I was having a candid conversation with Phil Seamark from DAX.tips about Aggregation Tables. During that conversation, I was asking about patterns in using Aggregation tables. Within that 10 minute conversation I was blow away by all the possible patterns. Because of this, we pleaded for Phil to present these patterns to the Milwaukee Brew City User Group.
While these patterns are described in detail here are the various patterns that can be used for Aggregation tables. Also, Phil includes a great introduction, found here. For each of these articles Phil describes proper usage for the pattern.
The Filtered Aggs would contain multiple Aggregation tables of the same data. But, each Agg table could contain different gains of data. For Example, data aggregated by Week, Month or Year.
Finally, the Incremental Aggs. This type of aggregation would be used when aggregating transactions per day by store, and or product.
Thanks Phil
A special thanks to Phil for presenting. Since, we know you are a busy guy doing tons of great work. Thank you for taking time out of your day to present this wonderful topic. We hope you enjoy this exploration into Agg Tables.
If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.
Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
Well Microsoft has done it again. They have added a great feature in the Power BI desktop release for December 2020. Direct Query to Power BI data sources is a thing. As a result, this means we unlock new Architectural patterns.
Enterprise Modeling
In the Microsoft Release notes we get a glimpse of our new normal. Previously, Power BI datasets could only direct query certain data sources. Here is a full list of data sources for Power BI Desktop. The Enterprise Sematic model is simply larger view of a data model.
Quick background on Composite Models. A Composite model is a data model. More specifically, a Power BI data model. Typically Power BI models have multiple data sources. Such as, Excel, or SQL server. For certain data sources we load data in one of two ways, Importing, or Direct Query. The Import method loads data into the Power BI file. While, Direct Query leaves the data inside the data source, but sends queries to retrieve data on demand. Learn more about Import and Direct Query in these articles.
When we think of an organization there are likely hundreds of data models. These data models support by many different teams. Each model is solves some sort of problem. As an example, we can think about models developed for Human Resources. The Human Resource model informs the HR team about acquiring new talent, or track an interview process. Other teams such as Engineering track spend or project details.
Using this method, imagine a user who needs to see data from both human resources and engineering. Thus, a user would need to visit two different reports. Obviously value can given by combining multiple data models. This would enable the creation of a single report using data from both sources.
Direct Query for Power BI datasets
Now, lets consider the Enterprise Data model. In the Microsoft documentation this is called the Enterprise Semantic Model. We can think of the Enterprise Semantic Model as storing metadata linking tables of data and storing relationships between tables. Direct Query to PowerBI.com now lets us make models of models.
From our previous example now consider this architecture.
Report builders can now create a single model that queries other data models. This provides data from multiple subject matter areas. Thus, enabling a single report to combine data from multiple locations.
Centrally Managed Models
Often in larger organizations there will be different teams creating models. This means, you might not have access to modify an Enterprise build model. Again, Direct Query to models to the rescue. As a report author, I want to reuse an existing model. However, I would like to add more data to the model that would enrich my reporting. This may come in the form of a connected excel document. For this architecture consider the following diagram.
This new architecture is the ultimate success for self service business intelligence. Enterprise governed models can be enhanced by business users. Therefore, providing flexibility while controlling model governance and standards.
More Architecture thoughts
Power BI is evolving at a rapid pace. Because of this, Power BI is rapidly becoming one of the key tools. Therefore, more thought to Enterprise Architecture must be considered. Learn more about key architectural decision points in our previous articles, Data Architecture, and The Greater Data Solution.
Composite Models Conclusion
These are just some of my initial thoughts on this amazing new world we have. There will likely be many more designs and implementations from the community of Power BI developers. I’m extremely excited to see other patterns emerge from using Direct Query against Power BI datasets.
Read more from the official blog release and Microsoft documentation:
If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.
Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
Check out the new Merch!
Hasta La Vista Data
Go Ahead Make My Data
PBIX Hat
Power BI Swag Now Available
Now you can get your favorite Power BI gear. Visit our PowerBI.tips clothing store to purchase your favorite items.
This article will focus on Power BI architecture within a data solution. In this context, Power BI architecture describes how Power BI can slot in as a piece of this strategy. For instance, this includes not only the reports, but the data retrieval, storage and machine learning involved. Next, it discusses different roles and responsibilities involved. This can expand on Power BI skills, looking at the entire solution.
In addition, we hope it to provide ideas for current developers looking to expand Power BI skills or change directions in their career. It can provide a look at areas of need in organizations and give thought of learning opportunities available.
Power BI is Greater than a Report
In my previous article, I discussed how Power BI should not be thought of as a separate product to ETL, AI/ML or overall data strategy. Rather, organizations need to include Power BI architecture as part of a data culture with all of the products working in union.
As a recap from the last article, a modern data platform typically has 4 steps:
Load and Ingest – extract the data out of the source system and transform it.
Store – Land this data somewhere so we can run analysis on it.
Process (or transform) – Run analytics on your data and draw out KPIs, AI and predictions.
Serve – present this data in an easily way for stakeholders to consume it.
Medium Size – Power BI Services
It is possible to implement a reporting strategy entirely in Power BI. First, we can load data using dataflows. Next, these can be stored as a dataflow in the service or backed by Data Lake Gen 2 storage. It is good practice to separate our Power Query models and reports.
Large Size – Azure Services
Sometimes, we want to use more services than just Power BI. This may be due to huge datasets, use of data in other applications, or writing custom machine learning. The above diagram shows an enterprise scale reporting solution. Azure data factory can move and transform the data. Afterwards, we can store in a variety of storage options, depending on the nature of the data. Many options are available to run machine learning on the data. This ranges from custom code to autoML. Lastly, we create data models and we can produce reports off them. This integrates the Power BI architecture into a whole reporting solution. There is also a path for streaming data – through Event hub and Stream analytics.
Azure has a host of services available. If you are new to these, it can seem a lot to learn. Luckily, Microsoft are rolling out Synapse! This includes a portal that houses many of these services, enabling you to use them all in a single place. If interested, Nicola Ilic I has a great series on Synpase and Power BI.
Different Roles in a Data Solution
If we want to design a data culture, we often need more roles and skills than just designing reports. The below list looks to identify some different roles and responsibilities in a data solution. This is not intended to be a fully comprehensive list. Rather, we explore some different and common roles that could be involved within a project.
It is unlikely a single project will require all roles. Usually, one person may take on two or more of these roles. Instead, the aim is to distinguish different areas of the data strategy. This can help us to view Power BI in the bigger picture, seeing where it fits in.
We will look at the following architecture – a company that uses Synapse as well as dataflows. For simplicity, we are not looking at any streaming reports. In this diagram the dataflows storage is implied to make the diagram easier to understand.
Each role will look at some common responsibilities and skills. It will also highlight the area in the above architecture diagram that they are responsible for.
Power BI Developer
The Power BI Developer is responsible for building and owning data models for KPI and user reports. Therefore they will spend large parts of their time modeling and transforming data in Power Query or dataflows. In addition, a BI developer has good understanding of tabular models and how to write custom business logic in DAX. They may also be required to set up the Power BI architecture.
Skills
Expert in Power Query and DAX. Familiarity in tabular editor and DAX Studio. Plus knowledge on database designs and tabular modeling such as implementing good STAR schema. Great Power BI skills and ability on Power BI desktop.
Roles of a Power BI developer
UI/UX Engineer
In larger projects, report design can benefit from a UI/UX specialist. Power BI developers will often complete this, but specialists can be brought in to help design layouts, flow and brand consistency within the project. Usually, the Power BI developer will look after the models and logic. However, the UI/UX Engineer helps make sure the final reports are professional looking. In addition, they can be involved in storyboarding the design of reports. A UI/UX specialist is needed for projects with many reports, external facing reports or embedded solutions.
Skills
Design skills as in products such as Adobe Illustrator. Dashboard design (such as gestalt principles).
Roles of a UI/UX developer
Data Engineer
A Data Engineer is responsible to get the data from the source and load it into Power BI. Smaller reporting projects may use only dataflows or power query, but larger ones might require more steps. The Data Engineer will move the data using tools such as data factory into a SQL database or Synapse storage. This allows larger enterprise solutions with massive volumes of data, or for complex machine learning to be performed on the data. In addition, Data Engineers will transform and clean the data making it suitable for reporting analysis or machine learning. They will then integrate this back into the solution.
Skills
Tools such as Azure Data Factory, Stream Analytics, Data Lakes or other data storage. In some projects a Data Engineer might work exclusively with dataflows or Power Query. Databricks, Spark Analytics and SQL are important for prepping and transforming big data. Engineers also can benefit from automating in languages such as PowerShell. Strong Power BI skills.
Roles of a Data Engineer
Data Architect
Data Architects are responsible for designing, managing and maintaining the reporting solution. Architects will suggest the best selection of tools and methods used. They would be the ones to recommend the team involved, the technologies used and the correct approach. Overall, they will set up the Power BI Architecture and machine learning solution.
Skills
Architects need to have vast knowledge and experience across all the technologies that can be used. This includes deep knowledge on Azure or Synapse, Power BI and data governance methodologies. They have solid understanding how machine learning can be integrated into a solution.
Roles of an Data Architect
AI Engineer
You may incorporate an AI Engineer in large projects that have a machine learning focus. Data Scientists spend large amounts of time writing custom algorithms. In contrast, AI Engineers use tools such as Azure cognitive services or Azure machine learning studio. On large projects they may work in tandem with Data Scientists helping to merge their code into the reporting system. They often spend time cleaning, transforming and prepping data. In many cases, this job is regularly undertaken by Data Engineers. However, projects with a heavy AI focus may benefit on having an AI Engineer or both a Data and AI Engineer.
Skills
While AI Engineers may not write custom algorithms from scratch, they need a solid understanding of machine learning principles. They also need to know how to prepare and clean data ready for machine learning solutions.
Roles of an AI Engineer
Data Scientist
The data science field has exploded in popularity over the recent years. Typically, a Data Scientist will spend their time cleaning, prepping and analyzing large volumes of data. Next, they write custom algorithms that can detect deeper insights. A Data Scientist often has years of experience and training coming from various backgrounds. Data Scientists write custom code in Synapse, Databricks or Apache Spark notebooks.
Skills
Expert in an analytical programming language, typically R or Python. They will have a unique blend of programming skills and statistics. Deep knowledge of designing and implementing different machine learning algorithms. In addition, they will be proficient at cleaning and preparing data.
Roles of a Data Scientist
Conclusion
As we can see, there can be many different roles involved in a data solution. Many times, one must wear many hats. Other times, organizations can benefit from having several specialists in different areas.
Microsoft is looking to unify the Power BI architecture and overall data solution through Synapse. This portal will make these roles easier to be completed by fewer people within the same portal. Still, there will always be a need for specialists. So if you are looking to expand your power bi skills, or finding new areas to expand into, make sure you familiarize yourself with these.
If you like the content from PowerBI.Tips please follow us on all the social outlets to stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.
Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
We just completed an amazing webinar from Parker Stevens over at https://bielite.com/ . In this webinar, Parker walks us through how to connect to the Power BI Admin APIs. The Admin API contains a wealth of knowledge for all activity within your Power BI tenant.
While there is a good amount of reporting built into the Power BI admin page, you may need more. This is why you would use the Admin APIs to gather all the activity data from within your Power BI. For more information, learn about the Power BI Admin Portal.
Parker takes us through a deep dive connecting to the Power BI admin apis and shows us how to build a robust dataset ready for Admins to produce the reporting they need.
Watch the Full Webinar
If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.
Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
The Power BI Field Finder created by Stephanie Bruno was just updated. In this recent update the HTML viewer has been updated since the old custom visual has been deprecated.
What is Field Finder?
We are glad you asked. The Power BI Field finder is a tool that can be used to review all the relevant connections between tables, measures and visuals within a report. This solution is easy to use and very informative. Simply point the Field Finder at a PBIX file. Then the Field Finder tool will de-construct all the visual elements of the report and digest them in to one complete data set.
Why do I need it?
If you have done any Power BI development over the years, you will notice a pattern.
Start building a report
Create some measures for a visual
Measures don’t work or report needs changes
Make more measures
Delete and replace visuals
Add visual styles and elements requiring additional measures
Finally publish report
After completing this process, which could be over the span of days to months. You will likely have a Power BI report with extra measures or un-needed calculated columns.
Then you are introduced with a problem, How do I go through and clean up the entire report? Do I need to click on every single visual to see which measures are used?
Doing this for a page or two you quickly are overwhelmed with tracking all that information. This is where Power BI Field Finder really shines. It handles all of that for you with a couple button clicks.
Download Power BI Field Finder
Be sure to download field finder for yourself.
If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.
Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
I was recently contacted by Power BI Community member Gomathy Viswanathan and Ashwini Nayak. Thus, it is my pleasure share with you their incredible report. The Harry Potter Escape room. If you are as curious as I was you will enjoy this fun and light hearted puzzle.
Play the Game
Take a little break today and give the Harry Potter Escape room a try.
The Creators
Escape Room, Harry Potter Edition was originally posted on the Power BI Community. Be sure to follow Gomathy and Ashwini on LinkedIn. For a quick tutorial on how to use the game the creators watch the video below.
Download the Game
Feel free to download a copy of the Escape Room to see how it was built. While these games are fun to play, there are many techniques you can learn from. Learning the ins and outs of bookmarking definitely will help your report building skills for example.
If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.
Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
The Milwaukee Crew is back at it again with the October 2020 Power BI User Group (PUG). This month we have the amazing Gil Raviv talk to us about Power Query and the Enterprise. For those of you who don’t know Gil he is a superb Power Query Expert.
Gil talks to about Power Query in different enterprise domains. Learn which tools to use and which ones are not quite ready for prime time. You will learn a ton in this great session.
If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.
Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
Daniel Marsh-Patrick is a custom visual developer who recently released the HTML Content, a custom visual. This month we go over this visual in detail with it’s creator himself.
While this visual is currently at V1, Daniel shows us a preview of V2 and trust me you will absolutely love it. I’m so looking forward to using this visual in my daily workflow. As well as V2 is going to be amazing.
If you like Daniel’s work please consider sponsoring him. This way we can continue to get amazing visuals: https://github.com/sponsors/dm-p
If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.
Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
Power BI is a powerful reporting tool that has been dominating the market and rapidly evolving. Yet, in many organizations people seem unaware of its true potential or core purpose. As a result, too often it is deployed to simply extract or visualize data points in an ad hoc reporting manner.
Power BI is greater than a report
Power BI should not be thought of as a separate product to ETL, AI/ML or overall data strategy. Rather, organizations need to include it as part of a data culture with all of the products working in union.
To deploy Power BI successfully, do not use it to simply design reports. Instead, design a culture and architecture. This is one that allows business users to understand, interpret and react to rich and powerful data driven insights.
The many additional products, services and capabilities that come packaged in Power BI are too frequently overlooked. As a result, people see only the top level – visuals in reports and dashboards. But there is a whole host of rich and exciting features below the surface.
With that, here are some common mistakes I have frequently seen new users make when rolling out Power BI.
Mistakes made to under utilize Power BI
Using it for Data extraction Large tables with a selection of filters that you may or may not look to export. Instead, Power BI is designed for trends, insights and cross slice and dice. Large tables and data dumps do not give insight.
Using it for a data visualization to tell a single point Design a visual that can convey information quickly, rather than an infographic type solution. If you are looking for that pixel perfect data visualization for a news story that tells a specific point, there may be other options. Paginated reports or predesigned Excel documents are viable options. Design data pipelines that are regularly updated. Create visuals that are designed to be interactive. This will help users drill down and find insights.
Ad hoc only reporting While this can be a great tool for ad hoc reports, you may be underutilizing and doing extra work. Instead, build reusable data models that are designed for multiple reports. Write DAX business logic and KPI that can serve as a single source of truth. Be sure to document your measures inside the data models. By clearly documenting measures data consumers will understand how to use the data model to build new reports.
Current reporting tool / Excel replacement A common request is to “lift and shift” all excel reporting into Power BI. These products are different and have different uses. If you are moving to Power BI, don’t try and recreate old solutions. Instead, a better approach is to design new reports that play to Power BI’s strengths. Utilize the rich features and powerful engines that make Power BI beneficial. This is a story of it’s better together. Using just Power BI or just Excel has it’s advantages and dis-advantages. Conversely, using both Power BI and Excel can play to each tool’s strength.
Not building a data culture Matthew Roche has an amazing blog series on building a data culture with why and how to do this. Building a good data culture is vital for adoption within the organization. The data culture will start with an Executive sponsor who can push for adoption. So, first and foremost, be sure to have a leader who believes in your vision.
Mistakes made when deploying Power BI solutions
Focusing on raw numbers, not business insights Instead of simply displaying numbers, great reports often have the following KPI, trends, drill down, interactivity and slicing capabilities. This allows business users to gain meaning information about the direction for the business.
Ignoring the deployment approaches Many business users are familiar with a typical process for reports; a user submits a ticket to IT. IT writes a bunch of SQL queries to get the data for this request. They then surface the data in tables and simple graphs. In contrast, Power BI does a great job at breaking down this long turnaround and getting the data in users hands quick. An organization should deploy a top-down, blended or bottom-up approach. As a result of utilizing this approach, they can merge the business and IT side of operations and remove silos.
Failing to Think like the Business and Act Like I.T. The I.T. organization has many strengths related to how to make data available quick and reliably. Power BI is mainly designed for business users. Thus, Power BI has features that borrow from best practices from I.T. One such best practice is the use of Deployment Pipelines.
Not utilizing Data Models or ignoring self-service reporting Data models, as described in this blog by Matt Allington, contain all the metadata needed for reporting. This includes the business logic and data transformations. However, creating and maintaining these can be time consuming. Instead, it is possible to reuse data models and keep one source of the truth for many reports. The modeling experts can own and maintain the models. Furthermore, business users can connect and build their own Power BI reports utilizing the models. This is done without even needing to write a single line of code.
Treating Power BI as a stand alone product, not part of the greater data or AI solution You should not treat Power BI should as just a visualization tool (read this blog by Gil Raviv). Instead, Power BI is a business insights tool, a way to serve and communicate the information within the organization. In addition ML and predictive analytics are baked into it, as are ETL processes, data storage and security. As a result a unified approach to a data culture should be built. Users from all business areas need to be aware of the strategy.
Using Power BI the right way
Power BI should be unified and part of the entire data stage – not a visualization layer on top of it. A modern data platform typically has 4 steps:
Load and Ingest – extract the data out of the source system and transform it.
Store – Land this data somewhere so we can run analysis on it.
Process (or transform) – Run analytics on your data and draw out KPIs, AI and predictions.
Serve – present this data in an easily way for stakeholders to consume it.
Power BI can be all of these steps. From a single report using power query (Load and Ingest) to import data (Store). Next, you can build a model and DAX measures (Process). Lastly, you can surface the data in visuals on the report pages (Serve).
This can be a more enterprise level solution and scale well too. Firstly, Dataflows are set to extract and transform data from many sources (Load and Ingest). You can back-up and store in a data lake gen 2 storage (Store). Secondly, the data can take advantage of automated ML (AutoML) and cognitive services. Build DAX expression over them, combining a powerful DAX language with the power of AI (Process). Last, you can package these as reports, dashboards, apps or embedded into other applications (Serve).
Alternatively, Power BI doesn’t have to be all these steps. A traditional data platform architecture is described by Microsoft in the picture below. You can utilize other tools such as Data Factory to Load and Ingest data. Next, you can use Databricks to Process/Transform the data. Power BI and Analysis services models will serve the data to the end user. This is a great example of Power BI fitting into a greater data solution. However, you should implement the deployment with the entire solution in mind. Power BI is not as a tool for simply creating visuals. A good deployment is deeply rooted in the culture. Each step must consider the others in the pipeline, not sit in silos.
Microsoft is expanding this ecosystem with Azure Synapse. As they roll it out, they are designing data engineering as a single platform. This combines this entire pipeline and tools into a unified experience. Power BI being a part of this platform.
When we think about user level security, Azure Active Directory (AAD) is the gold standard for access and security for organizations. Synapse leverages this technology to remove friction between different azure components. You can leverage AAD across the multiple services for data factory, Data Lakes, SQL and Spark compute as well as Power BI. The experience of governing data on a user by user basis improves with the Synapse experience.
A Low Code Data Engineering Solution
There are many Azure components you can use to produce a well engineered data pipeline. Azure Synapse brings all these tools under the same portal experience. For example, using Azure Data Factory, then writing data into a data lake. Picking up the data and querying flat files with compute engines such as SQL or Spark. Azure Data Factory also has built in features that can simplify data lake creation and management using mapping dataflows.
More Computing Options
No longer do We have to choose just SQL or Spark, rather We have options. We can use Provisioned SQL which was previously Azure Data Warehouse. Synapse now offers on-demand SQL, and Spark compute engines. This is where we are really seeing the technology move to where we have separated the storage layer from the compute layer. This means Azure Data Lake Gen2 serves as storage, and SQL and Spark serve as compute.
One Place for all information
Whether it is Azure Data Factory, Spark, SQL or Power BI. Synapse has now become the single portal for integrating all these services. This in general simplifies the experience and management of all your data pipelines.
If you like the content from PowerBI.Tips please follow us on all the social outlets to stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.
Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
Horary! The Power BI desktop for October finally arrived and it is packed with tons of updates. I’m super excited about this month’s release. We rallied the troops and have a ton of MVPs talking about the latest release of Power BI desktop
If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.
Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
