Welcome to the Iamdata Solutions Asset Management Monthly Newsletter - July 2022
Data Models
Well, I guess the first question is, what is a Data Model?
A data model can be described as tables that share connections or relationships. We connect the tables based on their common fields or common key.
It is critical that the data model is set up correctly to ensure that the reports show the correct values. When relationships are set up correctly in Power BI, this also help increase the data model performance and keep the size of the data model relatively small, making it more efficient.
Data Model Best Practices
Star schema
The data should be structured using a Star Schema. If you know anything about data warehouses, the Star Schema concept will be familiar to you. A Star Schema is what you would typically use when designing a relational data warehouse. The Star Schema model classifies the tables as either fact tables or dimension tables.
Dimension Table
The Dimension Tables are typically used for grouping or filtering data when you are building your analytics.
Examples of what a Dimension Table could be includes:
· Calendar,
· Location,
· Asset Class,
· Condition,
· Status, etc.
Within the dimension tables, there are various attributes that provides us with specific relevant information.
There is also what we call the Surrogate Key. The Surrogate Key is a unique identifier for an object in the database, it is usually the primary key that is defined within the data warehouse.
There is also another called the Alternate Key. The Alternate Key is the primary Key from the source system.
Fact Table
The Fact Table is used to track transactions, events, sales, conditions audits, revaluation, asset inspections, etc. The Fact Table is the table that contains the information you want analyse – e.g., average condition over the footpath network, or the current replacement cost for road surfaces at 30/06/2022, etc. The Fact Table is related to each one of the dimension tables using the Surrogate Key.
The Fact Table usually sits in the middle with the Dimension Tables surrounding the Fact Table which resembles are star shape – hence the name Star Schema!
One-way Filters
The relationship between the Fact table and the Look up table should use one-way filters, not bidirectional filters. Using bidirectional filters can introduce uncertainty in the data model.
In my Star Schema example, note all the joins are one-way filters. The Dimension Tables are filtering the Fact Table.
Fact Tables and Dimension Tables
Each table within the data model should serve a specific purpose. The models should typically contain Fact (data) tables and Dimension (lookup) tables.
You do not want everything pulled into your Power BI project in one huge table. There are good reasons why you should aim for the Star Schema model:
Organising the data is more manageable. It is easier to find the fields you need for your reports and visualisations and useability is increased.
Simpler DAX: the DAX will definitely be easier to write if you have designed a good data model following the Star Schema.
Performance: Power BI is more efficient in processing the visuals and reports with the Star Schema. It also allows you to scale the model without incident. The Star Schema model will be able to manage the processing of large data volumes without incident.
The Star Schema also means a faster refresh.
Housekeeping
Try and reduce the size of the data model by only including the data that is necessary for your analysis. Removing redundant or unnecessary records, fields, and tables will help improve the data model.
(Just to let you know, I’ll be looking more deeply into this in the next Power BI Newsletter).
Date time elements
If the data model has a date column that contains date and time elements, you will have to split them out into two separate columns. Splitting dates into separate columns will help reduce the size of the data model and it also allows you to create proper relationships between a date field and a calendar table, and within a data table.
Power BI Dashboard Design Best Practices
We have quickly looked at some best practices to apply when building your Power BI data model, now we’ll look at how we can ensure we’ve applied best practices for creating Power BI Dashboards and Reports.
Tips to consider before you start creating your dashboards
First of all, here are some simple tips to consider when you are creating your dashboards:
Keep it simple
It is important to use the correct visual for the data you want to visualise. When designing dashboards, especially those aimed at people who are not used to working with data, it is crucial to show the information in the most understandable way possible. The data behind the graphs, charts, and visuals is usually complex, but the visuals that represent the data must be simple to ensure anyone can understand it.
Less is more - Don't over do it
You just want to present the data in a way that makes the story the data is telling us easily understood. It is sometimes easy to get carried away filling the dashboard with as many charts as possible to show off all the data. A cluttered dashboard looks messy and disorganised, and the end user can be left feeling overwhelmed. Too much information can make the information difficult to read.
Consider your audience - who is going to use the Power BI report?
If you are building the Power BI dashboard for someone else, then you will have had many discussions with that person to fully understand their requirements. You should understand what information they want displayed, where that data is stored, and the purpose of the dashboard, e.g., for presentation purposes, for internal analysis, etc.
Be consistent
It adds to the readability and understanding of the Power BI dashboard if you maintain consistency. For example, use the same metrics, colours, visuals, charts, titles, and the information's layout to make it easier for the end user to digest the information and make sense out of it.
Best Charts, Graphs, Visuals suit which data types?
Area Charts
An area chart is based on the line chart with the area between the axis and line filled in. Area charts are good for showing changes over time. In this chart, we can see the differences between the total capital budget spent across 4 years.
Bar and Column Charts
Bar charts are great for looking at specific value across different categories.
Cards
Combo Charts
A combo chart combines a column chart and a line chart. Combining the two charts into one allows you to compare the data you are visualising in the chart. Combo charts can have one or two Y axes. Combo charts are good for when you have a line chart and a column chart with the same X axis. A combo chart is pleasing to the eye as well as saving space – where one chart takes the place of two charts. Combo charts are also a good choice to:
Compare multiple measures with different value ranges
Show the correlation between two measures in the one visual
Check whether one measure meets the target which is defined by another measure.
Funnel Charts
Funnels Charts help visualise a process that has stages, and items flow sequentially from one stage to the next. This example is for the detection and removal of Asbestos in Buildings.
The shape of the funnel shows us whether the process is working. Each funnel stage represents a percentage of the total. In most cases, a funnel chart is shaped like a funnel. The first stage is usually the largest and each stage smaller than its predecessor.
Ribbon Chart
Conditional Formatting
Conditional Formatting is really useful when you want to draw attention to specific points in the data. In these examples, we are highlighting where condition scores are good, medium, or bad. In these examples, I’ve used Data Bars, Background Colour, and icons.
Maps
Power BI integrates with Bing Maps to provide default map coordinates so you can create maps. Together they use algorithms to identify the correct location, (but sometimes it's a best guess).
The Power BI service and Power BI Desktop send Bing the geo data it needs to create the map visualisation. For maps (bubble, scatter, and dot plot maps), if latitude and longitude are provided, then no data is sent to Bing. Otherwise, any data in the Location bucket is sent to Bing. Filled maps require a field in the Location bucket; even if latitude and longitude are provided. Whatever data is in the Location, Latitude, or Longitude bucket is sent to Bing.
Artificial Intelligence (AI) Visuals
Key Influencers Visual
The Key Influencer visual in Power BI helps you understand the factors that drive a specific metric. A Key Influencer visual displays the major contributors to a selected result or value.
It is also an artificial intelligence (AI) visualization.
The drop-down box shows us the value that we are investigating. We have a choice, we can choose if we want to understand how this value increases, or how this value decreases.
The Left pane shows a list of the top key influencers that impacts the value under investigation.
The Right pane contains a column chart displaying all the values for the key influencer theme selected in the Left pane. There is also an Average line showing the percentage of the other themes that increase or decrease the metric under investigation.
In this visual, it’s the Capex Actual Costs under investigation.
Question: What influences Actual Cost to increase?
Top: The biggest influencer for this is when the Work Type Description is for Capex Pavement Reconstruction (Pavement Capital Works – Purchase).
Second: The biggest influencer for Asset Class is the Road Pavement. This makes sense because the Replacement Cost for a Road Pavement Asset is greater than the Replacement Cost for other Asset Classes.
Third: When Work Type is Capex Pavement Renewal (Pavement Capital Works – Minor Works)
Fourth: When Work Type is Capex Kerb & Gutter Reconstruction (Kerb & Gutter Capital Works – Purchase)
The Decomposition Tree
The decomposition tree visual is great when you need to interact with the data. Actively exploring the data in this visual by clicking on the different components is the best way to explore the data.
The decomposition tree enables you to visualise data across multiple dimensions. It automatically aggregates data and enables drilling down into your dimensions in any order.
It is also an artificial intelligence (AI) visualization. You can ask it to find the next dimension to drill down into based on certain criteria. This makes it a valuable tool for ad hoc exploration and conducting root cause analysis. Very cool!
I hope you have found this Power BI best practices newsletter useful. Look out for future newsletters where I'll be sharing more Power BI tips and tricks.
I have worked on many different projects with my Local Government clients, from designing and developing Power BI Reports, to building SQL Server databases for spatial data, to managing and maintaining the GIS and the Asset Management systems. If you'd like to discuss how we might work together, then please email me at ➡️ jill.singleton@iamdata.solutions
If you would like to receive the latest Newsletter Blog straight to your inbox, please subscribe here: ➡️ https://www.iamdata.solutions/subscribe
You can read all our Newsletters and Blogs here:➡️ https://www.iamdata.solutions/blog
You may also be interested in our Projects Page:➡️ https://www.iamdata.solutions/past-projects
Check out what our clients say about us here:➡️ https://www.iamdata.solutions/reviews
If you would like to see a particular topic covered in these newsletters, then please let me know about it. The chances are other people will be interested and would like to hear about it too! Please email me at: ➡️ jill.singleton@iamdata.solutions with your suggestions.
Comentários