Using Microsoft Power BI – Business Data Analytics for Better Reporting

December 20, 2021

We have a lot of discussions with clients about a desire to do “better reporting”. They have been using Power BI but feel they now need a data warehouse to create reports that will give them all the info they need. Fundamentally, organizations often have an analytics problem, one for which a data warehouse forms part of the solution; however, the complexities lie in the data. Often organizations have already applied significant expertise to the problem, and while they understand a lot about the problems, they have reached some difficult data challenges requiring external expertise.

Sometimes our clients have challenges coming up with an analytical data model that addresses this subject area. We try to identify common patterns for data modeling and see where and why there are differences. A good source of data modeling patterns can be found on the website https://DaxPatterns.com – this is specific to tabular datasets such as Power BI or Analysis Services, but it gives good examples of handling data patterns in the vast experience of those experts. When there are differences, it could be that the source data needs to be shaped in a different way to make it work with an existing pattern and that re-shaping can address the challenges in the data model.

Our clients often state that specific challenges and problems include:

Lack of standard terms across datasets and silos.
Non-standard course and program design.
Historical data that is sometimes overwritten by changes.
Process changes that result in the breaking of data linkages and consistency.
Inconsistent business practices result in data quality issues.

These are typical problems that many organizations face. Usually, BI projects expose these previously unforeseen problems in the organization, and there was no need to standardize or improve data quality until such an effort discovered these issues. Organizations that are willing and able to make process changes to address these issues at the source have the most successful BI efforts.

Lack of standard terms across datasets and silos is a common problem – this is the result of independent efforts of unconnected business users across the enterprise. There are several ways to approach this, but often we recommend that organizations try to foster an environment that brings groups together and fosters collaboration and cooperation to enable these groups to provide value across the organization rather than just for their own group. There are tools and features in Power BI to help with this, provide featured datasets, and provide data flows that can be produced by one group and consumed by other groups. Establishing a data catalog can help discover datasets within the organization, with documentation provided by the subject experts and other ways to build data stewardship and governance.

When processes cannot be changed, more specific transformation work can happen to adapt to things like the non-standard course and program design mentioned above. These build a set of business rules to apply to specific instances where patterns cannot be applied consistently.

Historical data changes can usually be handled by adding change tracking mechanisms like SQL change tracking or more brute-force mechanisms for change detection.

Process changes that break data linkages and inconsistent business practices are examples of things that cause data quality issues. Either there needs to be organizational change (sometimes it’s cultural change) to address these ongoing issues, or more stringent data quality validation needs to take place, with processes that detect invalid data and report validation errors for someone to address. The BI development team can do this work to provide users with mechanisms to address data quality issues. Still, cultural change to adopt practices that encourage high data quality will improve the organization over time and reduce the need for the BI team to provide these mechanisms.

During our engagements, Imaginet introduced this concept of bringing groups together and fostering collaboration which ultimately drives the maximum value for the organization.

Thank you for reading this post! If you enjoyed it, I encourage you to check out some of our other content on this blog. We have a range of articles on various topics that I think you’ll find interesting. Don’t forget to subscribe to our newsletter to stay updated with all of the latest information on Imaginet’s recent successful projects

Kyle Thorburn

Kyle Thorburn has been helping organizations implement Microsoft cloud solutions for the past 10 years. As Business Development Manager at Imaginet, he helps clients further adopt Office 365 and Azure across the enterprise.

discover more

Let’s build something amazing together

From concept to handoff, we’d love to learn more about what you are working on.
Send us a message below or call us at 1-800-989-6022.

Using Microsoft Power BI – Business Data Analytics for Better Reporting

SQL Saturday Part 2: Learning About Microsoft Fabric

My Trip to SQL Saturday Atlanta (BI Edition): Part 1

Enabling BitLocker Encryption with Microsoft Intune

Let’s build something amazing together