The organization is a Cognos shop and in the process to migrate reports to Paginated Report (SSRS) or Power BI reports.
I have to migrate a Cognos ‘Performance Report’ into Power BI Report Server (on-prem) Paginated Report (also referred to as SSRS) and would use the Microsoft Report Builder tool.
The Cognos report used a star-schema Framework Manager semantic layer consisted of 1 fact (summarized), 2 Dims and couple common Dims (Date, Region). Report contains Crosstab, Bar/Line chart and List table for each performance indicator; there are about 10 indicators. For each indictor, there are 3 query subjects; each for Crosstab, Bar/Line chart and List table.
If you are familiar with Cognos, we can generate the SQL from each query subject to use in SSMS to view the same data results but it is very unfriendly SQL. There are at least 30 query subjects for the entire report.
Azure subscription is in the works (for sure with Power BI Premium) – hopefully, we would have the environment in 4-6 months.
Migration thoughts …
Use the Cognos generated SQL to create dataset for each of the key performance indicator > Matrix, Bar/Line and Tablix in the Paginated Report. This would be bad choice as it would be a nightmare to maintain the very unfriendly SQL in the migrated report (updated at least 2-3 times per year due to new requirements) and there would at least 30 datasets.
Create Tabular Model consisting of 1 fact (summarized), 2 Dims and couple common Dims (Date, Region) for the report. What would happen when it is time to move the Paginated Report on-prem to Azure? Does the Tabular Model become a DataSet?
I would very appreciate your sharing and recommendation what do most work through to migrate Cognos reports to Paginated Report (on-prem) and subsequent to Azure.
Thank you in advance.
