Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Tom A]

Hi,

I’ve just started in a new role BI Manager role and I’m struggling to understand the how and why of the database / Power BI setup. Basically the company I’m working for has employed a consultant to build the BI architecture. From what I can tell there is a ‘data warehouse’ in an on-premise SQL server that has around 180 tables that are derived from SSIS packages. The tables aren’t facts or dimensions  with some being very wide (300+ columns) and no relationships exist. There are a further 200 or so SQL Views which contain all the business  logic which are added to as and  when new there are  requirements.

These are then used as Power BI data sources and linked together with relationships to build the PBI model. The relationships are very complex and difficult to follow and not in, for example, a star or snowflake schema. There are no transformations made in Power Query and the consultant is adamant that there shouldn’t be. The PBI reports are then built with relatively basic  measures where required and published.

None of this is documented and as I said I’m really struggling with it all. Has anyone seen or setup anything like this. Any advantages or disadvantages to what I’d consider a more standard Power BI approach using a Power Query?

Thanks for any help

Tom

[Beau A]

We had a similar issue.. where our company had a large number of wide transactional tables. Our process was to create aggregate tables off them in Analysis Services (think daily counts of important metrics by product or site.. or anything) and used these tables as our new fact tables. This allow for faster visual loading and you will be able to direct import the tables into Power BI for your star schema.. and PowerQuery becomes an option again. And the DAX measure become much much easier to create. This would mean though that you have a few Power Bi files (maybe by department). When you finish creating your report you can click publish to a power bi workspace where ppl can view the dashboards.. or on the workspace it give you a link where you can post to SharePoint.

——————————
Beau A
Analyst
MI
——————————
——————————————-

[Tom A]

Hi Beau,

Thanks for the reply. The  wide tables are being generated by the SSIS packages the consultant has put in place and there is no SSAS. The table are being saved in an SQL database and are pulled into Power BI. The theory is that all modelling and business logic is done in SQL / SSIS to give a consistent model. I’ve  been told that no transformations are to take place in Power Query. 

Really confused!!

Regards

Tom

——————————
Tom A
CT215DX
——————————
——————————————-

[Sam Duval]

SQL Server is exponentially stronger in terms of building and joining logic. We do everything in SQL and use power bi as the output. The advantages of having all the hard work being written in SQL is it’s easily searchable compared to power BI so if a certain field is being removed from the database, I can quickly find and update all code bases that reference said field. 

Another advantage is you can more simply control security without having to define roles, we leverage workspaces like department buckets, I’m at a credit union, so the credit card department has it’s workspace with the reports they need, and only they have access to it. So I can publish reports for them that have full credit card numbers, then the branch staff has their own workspace and I can publish all their reports without the credit card number. 

End user experience is a little better as well, depending on the report, a user has 2 or 3 pages of information, where as the model that replicates the whole warehouse into PBIX and reports are built you can have many more pages, and if someone doesn’t have the link to their specific report page, they will have to tab through the pages until they find the one they need. 

We end up with more PBIX files published to PBI Service, but each one is independent of the other and each one can be refreshed on its own schedule and the failure of one report doesn’t prevent the others from being updated. Also by not using a power bi to build out all reports of a single import, if you have multiple people potentially editing files, and making changes to the power query model, you risk a change breaking an existing report. 

We had a consultant build my company a database “data warehouse” and a power bi model that imported those objects into it then did all the report logic in power bi. It was the most useless thing i ever used. It was very slow and very cumbersome to work with. We just finished replacing that with the SQL first model and everything runs much smoother. 

All that being said, I don’t think outlawing any and all power query or other manipulations within power bi is a good idea. There can be benefits to doing some transformations in power BI or creating custom columns and measures in power bi can really help you get the data you need. Especially when you are dealing with a lot of potential filtering by the end user. Power BI formulas as much more responsive in that regard.

——————————
Sam Duval
Data Quality Analyst
Element Financial
Indianapolis IN
——————————
——————————————-

[Ethan Everett]

Hi Tom,

How are the Power BI files being propagated to end users? Is your company using PBIRS, PowerBI.com or are they shared via some other means? Is there any consideration for implementing SSAS or using PowerBI.com? This would open up a lot more options since you would be able to deploy tabular models to SSAS or PBI.com. If you are unfamiliar with tabular models, they are essentially larger and more powerful versions of a PBIX file. Tabular models also have much better tools available like Tabular Editor, DAX Studio and ALM Toolkit. Lastly in regards to tabular models you can implement good version control processes using things like Visual Studio, Git Hub/Lab and Azure Dev Ops. Tabular models take PBI to the level of an enterprise tool rather than an tool for individuals to do some type of analysis. I would suggest doing some digging to make sure your company is well informed on the options available, Tabular modeling overview – Analysis Services | Microsoft Docs

SQL Server is certainly preferable for doing complex logic, however, the issue we have run into is that reporting using SQL Server is too static. As an example, you have a view and any new dimension requires significant changes to ?the view in order to allow that dimension/field to appear in the report. With SSAS tabular models, this is not nearly as problematic. As long as the relationships exist in the model, the addition of new columns/dimensions to a table could flow in upon refresh and then users could drag in the new field if so desired. I do agree that giving all users the ability to do their own reports in Power BI Desktop directly can result in chaos and that’s where implementing SSAS bring things into the enterprise tool realm. 

?

——————————
Ethan Everett
Finance Data Engineer
——————————
——————————————-

[Mike Hammons]

Hi Tom,

Yes, I have seen this approach in many organizations. Unfortunately, it is seen a lot more than we should at this point in Power BI’s maturity. When I see these approaches, Power BI is used simply as a visualization tool and nothing more. That’s such a waste. While Power BI is a good/great visualization tool, the true power lies in Power Query, DAX to build the data models. To get the most value out of self-service BI and BI for the masses, Power BI is simply the best tool for that, when used correctly. 

In my experience, often people with very strong SQL skills tend to struggle the most with Power BI, Power Query and DAX. Creating Tabular models with Star schemas is quite different than traditional relational models. Power BI is a user friendly front end to creating Analysis Services Tabular models. Tabular models are much stronger for doing analysis and analytics. When a Power BI data model is published, behind the scenes it is deployed into Analysis Services ( a bit more complicated than that, but primarily that’s what’s happening).

There are endless debates on where transformations should happen. I like how Matthew Roche, Principal Program Manager at Microsoft, came up with a maxim to help make the decision. Roche’s Maxim of Data Transformation states: “Data should be transformed as far upstream as possible, and as far downstream as necessary.” Where “upstream” is closer to where the data is originally produced, and “downstream” is closer to where the data is consumed.

The SQL Views really should not have been developed on top of the “transactional” or data store. The SSIS packages created what I usually refer to as a landing zone or more technically staging tables. It is best to move that data into more reporting and analytics friendly structures, do some necessary transformations and cleansing. Recently we do this most often in Azure Data Factory and/or Dataflows. Next build views if needed to “materialize” the cleaner data. From this point is where you should now start to do your creation of Power BI data models. Doing as much as possible in Power Query to massage the data, then DAX for the context part of the analysis (slicers, dimensions, facts, etc.).

Your thoughts are correct!

Best Regards,

Mike

——————————
Mike Hammons
Director, Business Intelligence
HSO Enterprise Solutions
——————————
——————————————-

[Sam Duval]

It really depends on how data is consumed. Just because power bi excels at ingesting and transformations og data, doesn’t mean it is a replacement for doing aggregation and query logic in SQL server. They are two separate use cases. If the purpose of the power bi is to display information developed by a few people, hosting the work on SQL makes more sense. 

If the idea is to publish a trusted copy of the data stored in sql to be able to research and glean their own insights from the data then absolutely the data needs to be imported into the power bi model with the snowflake schema in mind.

Get Outlook for Android

——————————————-