Hi, I’m looking for some high level advice on how best to undertake two migrations:
• Migrate hundreds of legacy reports from Cognos to PowerBI. The current cognos reports are built upon Framework Manager as the semantic layer, which connects to our DWH (‘kimblallised’ facts and dimensions)
• Migrate an on-prem SQLServer data warehouse to Azure Synapse.
We have been advised by a 3rd party that undertaking the report migration first should be prioritised and can be done in isolation from the DWH migration, which would be complete afterwards. No ‘to-be’ architecture has been proposed and no transitional road-map has been created.
What are the implications/considerations of undertaking a migration to PowerBI, where all data will originate from the on-premise data warehouse, and once completed migrating the on-premise data warehouse to Azure Synapse?
-
Costs. Costs. COSTS. Your cloud costs are going to exceed all your forecasts and whatever pricing you get from research. A safe, and probably even conservative, suggestion is to double whatever monthly pricing you get. You should budget for that number and monitor carefully, I recommend tracking expenses at least weekly, if not daily, for your first month or three. You should also forecast increase in monthly costs as time goes on. This is especially true if you have a PowerBI Premium subscription, as certain features require it.
-
I've never worked with Cognos, but I'm pretty sure that PowerBI features will differ enough that some re-engineering will be needed (not just redesign...you'll need to rethink how a report works if it's anything more than a static page) PowerBI is better suited for dashboards and highly interactive controls, it's poorly suited for "reports". Reporting Services might be a better place for those. As far as I know there aren't any tools to migrate automatically, and PowerBI doesn't lend itself to programmatic generation like SSRS does. You should budget your time to do every report manually, and add 25-50% additional time.
-
Regarding Azure Synapse, again, look at costs. Unless you're migrating more than 10 terabytes of warehouse data, you will probably not get the advantages of Synapse, and spend a lot more money than necessary. Unless your data is already in some columnar-optimized format, like Parquet or SQL Server columnstore indexes, and already exceeds 10 TB, you're better off looking at a SQL Server option or hosting Parquet files. I know Synapse does this under the hood, but it also adds a lot of monthly fees.
-
I don't disagree with your 3rd party's assessment on doing the report migration first, but if your DW migration will involve architectural changes, you'll want to stage them in a way so that you don't end up re-working most of the reports because the DW schemas changed. Basically, any report that is really simple, selects from only one table, those should migrate first. Anything more complicated should wait until some of the DW migration has been tested. A really well done Kimball architecture should be a clean migration...I'll leave it at that.
Not to sound critical but many are not as well-done as their architects would like to believe. -
PowerBI data models always host their data in the cloud, unless you use the PowerBI ReportServer. If you need data to be refreshed frequently, you'll need to schedule that and possibly have to look at ExpressRoute or whatever MS calls it these days. Recommend you check the GuyInACube channel on Youtube, it's probably the best resource for PowerBI information. In all likelihood, your data refresh needs will significantly impact your DW data model, and you'll want to minimize how much data you have to copy for each refresh stage. There are a number of videos on GuyInACube about that, I think there's one that saved over 90% space and time during refresh.
-
COSTS. You will monitor closely. You will optimize and make everything as efficient as possible. You will STILL be surprised by unforeseen costs, and unforeseen growth in costs. I can't really express this properly, unless you've already done things in the cloud, the costs will sneak up on you. I think it's impossible to lift-and-shift existing architecture to the cloud, with any kind of parity, and not pay a lot more. Any cloud savings require significant re-architecture to achieve.
I think another way of saying "cloud costs more" is "on-prem is probably not as expensive as you think". The cloud-native services like Synapse are engineered to maximize monthly recurring revenue. I don't want to sound conspiratorial or sinister. My own experience with cloud was not entirely positive, and the company spent way, way more on Azure than even a substantially better on-prem environment would have cost.
Edit: something I forgot to mention earlier, you will need to rethink how the reports/dashboards are managed and administered, comparing Cognos to PowerBI. If you have a lot of report subscriptions, e.g. emails to folks, etc., that's going to be a serious undertaking in PowerBI, as it must all be done manually. That alone is a separate project, because you'll need to look hard at existing schedules, multiple subs for the same reports (same recipient/diff. parameters, or vice versa) and look at consolidating wherever possible. If you have 1000+ subscriptions I can guarantee you have some dead and redundant ones running, and they're not worth migrating.
Who is recommending the migration from Cognos to Power Bi? So the reports are migrated first on prem or in cloud?
If in cloud will they be pointing back to the on prem DW? That would propably require some powerbi gateway setup etc. Have they detailed this in the recommended migration path?
I see red flags here as well.