PowerBI Set Up and Architecture (next to JedoxBI)

[Anonymous]

Hi everyone, 

My customer started to build corporate reporting through Jedox BI, but realized that  it was just not as intuitive and a bit too complex for those who should be working through KPIs and key operational and strategic reports. The option of using PowerBI (especially in view of a global D365 migration in 2020 onwards) is appealing, yet the team internally doesn't have any expertise in architecting a PowerBI set-up. 

One of the external PowerBI consultants I spoke with suggested to start with PowerBI Pro and pick up data directly from the SQL server which contains the raw data from about 21 ERP instances worldwide. The Jedox BI consultant suggested to  set up  another SQL server and install SQL Analytics Services in order to extract data for Power BI. He would then take about 1 month (?!) to convert data from Jedox BI cubes to PowerBi.... (I am confused about that...why would you need to do that?).

There will be approx. 30 - 50 report users / viewers world wide (approx. 5 - 8 admins who'd create reports). Ideally we should move to the cloud (Azure), but for now everything is hosted on prem. 

Does anyone have an opinion or experience in terms of set up, the license required (is PowerBI Pro sufficient to start with?) and best practices? 

Thank you, best regards

Alexandra 

[Seth_C_Bauer]

@Anonymous There is a ton that can be communicated here because there are so many options... I doubt I'll cover it all, but I'll try.

Depending on how large your dataset is, it could be possible that you would need an Analysis Services server, or you could boost up to Power BI Premium (5k a month) to support larger models. Why? The Power BI Pro account allows you to share content in the Service, the PBIX model size can only be 1GB max to publish to the service. If you have a model that is going to be much larger than that, it either needs to be supported in AS, or you need premium to deploy larger models. The upside with premium, is you can share reports with free users as they are covered under the license. All the developers would still need Pro to share.

Technically, if the dataset is small enough and the database in SQL is well formed in terms of facts/dimensions you could unleash your people on it and they could build a bunch of reports. There are methods to streamline and use the same dataset that I would sugget pursuing if you went this route, otherwise you would have a model for each report. Under the covers, Power BI has an Analysis Services model built into it. (Thus the only reason I would see to develop in AS is if you have size limitations) - then you could use a live connection from the reports to the AS model. The downside is all the developers would need to build in AS/Visual Studio... Power BI is a much easier interface. If you have a raw database and a very large model to build, a month of time to build that out in AS isn't outside the realm of possability. But again, we don't have details in that vein to specifically say one way or another.

[Anonymous]

Thank you very much, Seth. 

Very helpful. 

I guess because my client's data is a big data of mess and it can't be streamlined until they have gone through and completed the clean-up activities we've started to define, the database will remain huge and it's likely that models will be complex (due to the  different data sets and inconsistencies). 

If I interpret your response correctly, the Power BI  Premium would be the way to go, but we don't necessarily need AS given that Jedox BI has already put the data into cubes and therefore shouldn't require AS as an intermediary step unless we go down to the raw data and cut out the Jedox Cubes..., right? 

[Seth_C_Bauer]

@Anonymous I would suggest that you dig into understanding the sizing thresholds currently in place in Premium. AS will allow you to host larger models, so you might have to go that route. (I don't know based on your description).

If Jedox created cubes... I assume this means in their own proprietary software? If you can connect to the cubes I spose you can leave that, or you can migrate into AS based on the work they've done. There are to many unknowns for me to make recommendations specifically.