Tag: Power BI

Azure Analysis Services, BISM Tabular, Business Intelligence - BI, Data Warehousing - DW, DAX, Power BI, Power BI Desktop, Power BI Desktop RS, Power Pivot, Power Query, Quick Tips, Self-Service BI, SQL Server, SQL Server Analysis Services - SSAS, T-SQL

Quick Tips: Time Dimension with Time Bands at Seconds Granularity in Power BI and SSAS Tabular

Time Dimension with Time Bands at Seconds Granularity in Power BI and SSAS Tabular

I wrote some other posts on this topic in the past, you can find them here and here. In the first post I explain how to create “Time” dimension with time bands at minutes granularity. Then one of my customers required the “Time” dimension at seconds granularity which encouraged me to write the second blogpost. In the second blogpost though I didn’t do time bands, so here I am, writing the third post which is a variation of the second post supporting time bands of 5 min, 15 min, 30 min, 45 min and 60 min while the grain of the “Time” dimension is down to second. in this quick post I jump directly to the point and show you how to generate the “Time” dimension in three different ways, using T-SQL in SQL Server, using Power Query (M) and DAX. Here it is then:

Time Dimension at Second Grain with Power Query (M) Supporting Time Bands:

Copy/paste the code below in Query Editor’s Advanced Editor to generate Time dimension in Power Query:

let
Source = Table.FromList({1..86400}, Splitter.SplitByNothing()),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "ID"}}),
#"Time Column Added" = Table.AddColumn(#"Renamed Columns", "Time", each Time.From(#datetime(1970,1,1,0,0,0) + #duration(0,0,0,[ID])), Time.Type),
    #"Hour Added" = Table.AddColumn(#"Time Column Added", "Hour", each Time.Hour([Time]), Int64.Type),
    #"Minute Added" = Table.AddColumn(#"Hour Added", "Minute", each Time.Minute([Time]), Int64.Type),
    #"5 Min Band Added" = Table.AddColumn(#"Minute Added", "5 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/5) * 5) + 5, 0)), Time.Type),
    #"15 Min Band Added" = Table.AddColumn(#"5 Min Band Added", "15 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/15) * 15) + 15, 0)), Time.Type),
#"30 Min Band Added" = Table.AddColumn(#"15 Min Band Added", "30 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/30) * 30) + 30, 0)), Time.Type),
#"45 Min Band Added" = Table.AddColumn(#"30 Min Band Added", "45 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/45) * 45) + 45, 0)), Time.Type),
#"60 Min Band Added" = Table.AddColumn(#"45 Min Band Added", "60 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/60) * 60) + 60, 0)), Time.Type),
    #"Removed Other Columns" = Table.SelectColumns(#"60 Min Band Added",{"Time", "Hour", "Minute", "5 Min Band", "15 Min Band", "30 Min Band", "45 Min Band", "60 Min Band"})
in
    #"Removed Other Columns"
Continue reading “Quick Tips: Time Dimension with Time Bands at Seconds Granularity in Power BI and SSAS Tabular” →
Power BI, Power BI Governance, Self-Service BI

Introducing Power BI Governance and Adoption Good Practices Series

Introducing Power BI Governance Series

In my experience working in IT industry from early 2000 one aspect of dealing with data that is always taken very seriously is data governance.

For many years the industry learnt how important it is to have a data governance process in place to keep your data safe and healthy so you get the most out of your data. Power BI is no different to any other data platforms, so data governance is an important part of it. However, the concept of self-service data management which offers all the beauties and power an agile approach caused some organisations not to think about their governance approach from the outset.

On the other hand, some people find governance as a burden which slows down the adoption. Some people think even worse, they think governance is a stopper which puts a lot of unnecessary restrictions in place which decreases efficiency.

But in reality if you start data governance planning sooner than later you can protect your organisation from a lot of risks down the road which can get really nasty and costly like:

  • Risk of law suits due to data leakage and privacy issues
  • Untrusted data analysis and reporting due to poor quality of data
  • Poor performing solution due to lack of auditing
  • Inefficient development outcomes due to undefined environments
Risks of lack of governance
Continue reading “Introducing Power BI Governance and Adoption Good Practices Series” →
Business Intelligence - BI, Good Practices, Power BI, Power BI Governance

Power BI Governance, Good Practices, Part 1: Multiple Environments, Data Source Certification and Documentation

Power BI Governance, Good Practices, Part 1: Multiple Environments, Data Source Certification and Documentation

Power BI is taking off, and it’s fast becoming the most popular business intelligence platform on the market. It’s easy to engage with and get professional results quickly, making it the perfect tool for organisations looking to beef up their BI prowess and make data driven decisions through-out the organisation.

Gartner 2020 Magic Quadrant for Analytics and Business Intelligence Platforms
Did you know that Gartner named Microsoft as the 2020 leader in their Magic Quadrant for Analytics and Business Intelligence Platforms?

In this post we’re going to look at three good practices for implementation and give you the tips you need to make sure you avoid common pitfalls so you are on the fast track to success with Power BI on your organisation.

1. Setup multiple environments

When working on a Power BI implementation project, it’s wise to have multiple environments to manage the lifecycle of your BI assets. Below we’ve listed several environments that should be considered depending on the complexity of the project and your organisation’s needs.

Development (aka Dev)

Being able to keep on top of the many reports you’re testing, and having the ability to track changes that occur, is essential as you get setup. Without a specific Dev environment, your production environment will quickly become overwhelmed with assets, making it hard to maintain and manage.  

When working in the dev environment, make sure that you have data sources specifically for development. We’ve seen production data used in dev on many occasions which can lead to serious privacy and data sovereignty issues. Your dev data sources shouldn’t contain sensitive data. 

These development environments can be on your local network or in cloud storage (like OneDrive for Business or GitHub). It is recommended to have separate Workspaces in Power BI Service for each environment.

Tip: The data sources of all published reports to Power BI Service must be sufficient for development use only and should avoid including confidential data.

User Acceptance Testing (aka UAT) 

The people who will be using the reports daily are the ones who should be testing them – they know the business best, and will be able to identify opportunities and gaps that the development team may not be able to identify themselves. By making sure the user is brought into the process early on, it maximises the value added to the business.

User acceptance testing is the last phase of testing. The UAT environment should only be created once the solution has been fully tested in Dev and approved by senior Power BI developers.

Continue reading “Power BI Governance, Good Practices, Part 1: Multiple Environments, Data Source Certification and Documentation” →
Business Intelligence - BI, Data Visualisation, Power BI, Power BI Desktop, Power BI Service, Self-Service BI

Highlighting Below Avg Sales per Hierarchy Level with SWITCH() and ISINSCOPE() DAX Functions in Power BI

Highlighting Below Avg Sales per Hierarchy Level with SWITCH() and ISINSCOPE() DAX Functions in Power BI

I was working on a project a wee bit ago that the customer had conditional formatting requirement on a Column Chart.
They wanted to format the columns in the chart conditionally based on the average value based on the level of hierarchy you are at.
Here is the scenario, I have a Calendar hierarchy as below:

  • Calendar Hierarchy:
    • Year
    • Semester
    • Quarter
    • Month
    • Day

I use “Adventure Works DW2017, Internet Sales” Excel as my source in Power BI Desktop. If I want to visualise “Total Sales” over the above “Calendar Hierarchy” I get something like this:

Line Chart in Power BI, Total Sales by Year

Now I activate “Average Line” from “Analytics” tab of the Line chart.

Adding Average Line to Line Chart in Power BI

When I drill down in the line chart the Average line shows the average of that particular hierarchy level that I am in. This is quite cool that I get the average base on the level that I’m in code free.

Power BI, Drilling Donw in Line Chart

Easy, right?

Now, the requirement is to show the above behaviour in a “Column Chart” (yes! visualising time series with column chart, that’s what the customer wants) and highlight the columns with values below average amount in Orange and leave the rest in default theme colour.

So, I need to create Measures to conditionally format the column chart. I also need to add a bit of intelligent in the measures to:

  • Detect which hierarchy level I am in
  • Calculate the average of sales for that particular hierarchy level
  • Change the colour of the columns that are below the average amount

Let’s get it done!

Detecting Hierarchy Level with ISINSCOPE() DAX Function

Microsoft introduced ISINSCOPE() DAX function in the November 2018 release of Power BI Desktop. Soon after the announcement “Kasper de Jonge” wrote a concise blogpost about it.

So I try to keep it as simple as possible. Here is how is works, the ISINSCOPE() function returns “True” when a specified column is in a level of a hierarchy. As stated earlier, we have a “Calendar Hierarchy” including the following 5 levels:

  • Year
  • Semester
  • Quarter
  • Month
  • Day

So, to determine if we are in each of the above hierarchy levels we just need to create DAX measures like below:

ISINSCOPE Year		=	ISINSCOPE('Date'[Year])
ISINSCOPE Semester	=	ISINSCOPE('Date'[Semester])
ISINSCOPE Quarter	=	ISINSCOPE('Date'[Quarter])
ISINSCOPE Month		=	ISINSCOPE('Date'[Month])
ISINSCOPE Day		=	ISINSCOPE('Date'[Day])

Now let’s do an easy experiment.

  • Put a Matrix on the canvas
  • Put the “Calendar Hierarchy” to “Rows”
  • Put the above measures in “Values”
Detecting Year, Semester, Quarter, Month and Day hierarchy levels with ISINSCOPE in Power BI Desktop

As you see the “ISINSCOPE Year” shows “True” for the “Year” level. Let’s expand to the to the next level and see how the other measures work:

Continue reading “Highlighting Below Avg Sales per Hierarchy Level with SWITCH() and ISINSCOPE() DAX Functions in Power BI” →