Using Microsoft Power BI in data engineering
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 is sometimes overwritten by changes.
- Process changes that result in breaking of data linkages and consistency.
- Inconsistent business practices that 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. Power BI tools and features 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 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 introduces this concept of bringing groups together and fostering collaboration which ultimately drives the maximum value for the organization.
Contact us today to discuss your organization’s needs around analytics.