By Nick Edwards
In this blog post we’ll take a quick look at creating a self-generating calendar table using DAX.
Dates are important if we want to perform time intelligence reporting on our data i.e. yearly sales, monthly sales, weekly sales, year to date sales or previous year sales.
We’ll be using the calendar function to create our date table, but there are other methods to do this such as CALENDARAUTO or GENERATESERIES.
Here is the syntax we’ll be using to generate our date table.
The calendar function returns a single column called “Date” which generates a continuous series of dates from the specified start date to the specified end date. So if we specified the start date to be the 01/01/2020 and the end date to be 31/12/2020 the function would generate 366 rows of distinct date data.
We can then use the add columns function to expand our calendar table further to add specific columns we wish to slice our data by i.e. year, month, quarter, week number, day…
Here is the syntax we will be using to expand our calender table.
Let’s use an example to further explore how these functions work in practice using some sample adventure works sales data. Here we use the “Get Data” icon to directly query our adventure works database and bring over a sample of sales data, named “AW_Sales”.
Now, to create our calendar table we need to click “New Table” in the modeling tab and enter the following function.
Note: I like to use variables in my date table just to keep the DAX looking clean and fuss free, but this isn’t necessary.
For the start date parameter we have used the FIRSTDATE() and for the end Date parameter we have used the LASTDATE() function. This is so we can extract the first and last “OrderDate” from our AW_Sales table. We could have also used the MIN() and MAX() function to deliver the same results using the newly created “Date” column. As we can see this has generated a sequential date list from the 01/01/2012 to the 31/12/2013 with 731 distinct rows of data.
Now we want to expand our calendar table using the newly created “Date” column with new columns which can slice and dice our data. To do this I use the ADDCOLUMNS function. Here I have added Year, Quarter, Year Month, Month Number, Month Name, Day Of Year, Day Of Month, Day Of Week and Day Name as columns.
Once we’ve done this, we will mark our newly created table as a date table to allow Power BI to recognize date hierarchies and time intelligence functions.
We can now view our newly generated calender table in the data view. Instantly when any new data enters our model from “AW_Sales”, the calendar table will expand accordingly due to the last date function used above.
Now it’s just a case of creating a one to many relationship between our new calendar table and our “AW_Sales” table. We’ll create a one to many relationship between “Calendar[Date]” and “AW_Sales[OrderDate]” as shown below.
Congratulations we have now created a fully fledged calendar table that can slice and dice our “AW_Sales” tale by any of the columns we have created in our calendar table, as shown in the example below.
First question, why bother with layouts?
Using layouts in Power BI allows a user to make their visuals stand out better, the page looks professional and more appealing to its audience.
Second question, why PowerPoint?
The default page size in Power BI desktop is 16:9, (this trick doesn’t work for other Power BI page sizes), which is identical to a PowerPoint slide.
Therefore whatever is designed in PowerPoint will fit onto a Power BI page perfectly. Also PowerPoint is very easy to use; most people are familiar with it.
You’ll need a Power BI dashboard that you want to add a layout to. I’ll be using a dashboard displaying data on the Premier League top 50 goals scorers.
To start the creation of a layout, open PowerPoint with a blank side and add a rectangle.
Next, add glow to the rectangle, glow is found in the drawing ribbon under shape effects.
Repeat the process of adding rectangles with glow and lay them out so the visuals will fit.
If there is an important visual that needs to be a certain size you can find the size of the visual in Power BI in pixels and tailor the rectangle size to your visual.
Unfortunately PowerPoint displays sizes in cm so the numbers will need converting.
I use the following – https://www.unitconverters.net/typography/pixel-x-to-centimeter.htm
Once happy with the layout, save the slide as a PNG file. Upload this as the background in Power BI by going to format then page background, then selecting your created PNG file.
There you go!! A Power BI dashboard with its own custom layout.
Being able to hook Power BI directly into Azure Data Lake Storage (ADLS) is a very powerful tool (and it will be even more so when you can link to ADLS files that are in a different Azure account!! – not yet available as at January 2017). However there is a problem, Data Lake is designed to scale to petabytes of data whereas Power BI has a 10GB limit. Yes this is compressed, so we’d expect around 100GB of raw data, however to load this you need 100GB+ of RAM available on your PC, so it’s hard to actually reach the limit with a single dataset.
There’s obviously a disconnect in scalability here. In some datasets we can just use U-SQL to aggregate the data and pre-summarise by the list of fields that we actually want to analyse, and this is fine for additive transactional data. However if we need a many to many link or the granular details of individual rows of data then there’s an issue, how to we get this data into Power BI?
The answer is sampling, we don’t bring in 100% of the data, but maybe 10%, or 1%, or even 0.01%, it depends how much you need to reduce your dataset. It is however critical to know how to sample data correctly in order to maintain a level of accuracy of data in your reports.
Option 1: Take the top x rows of data
Don’t do it. Ever. Just no.
What if the source data you’ve been given is pre-sorted by product or region, you’d end up with only data from products starting with ‘a’, which would give you some wildly unpredictable results.
Option 2: Take a random % sample
Now we’re talking. This option will take, for example 1 in every 100 rows of data, so it’s picking up an even distribution of data throughout the dataset. This seems a much better option, so how do we do it?
— a) Use ROW_NUMBER() and Modulus
One option would be to include a ROW_NUMBER() windowing function in a U-SQL query that allocates each row a unique number.
ROW_NUMBER() OVER (ORDER BY id) AS rn
We then apply a modulus function to the result, and only take those rows that return a 0
WHERE rn % 100 == 0;
This filters to only 1 in every 100 rows.
This method works in T-SQL, and just as well in U-SQL.
— b) U-SQL SAMPLE
However, there is an easier way. U-SQL contains the ‘SAMPLE’ clause that automates this process. Thanks to Paul (T|B) for spotting this beauty.
SELECT xx FROM xx [SAMPLE [ANY (number of rows) | UNIFORM (percentage of rows)]]
There are two sampling options here, ANY and UNIFORM.
After not being able to find anything on the tinterwebs about them I ran some tests to see what they did and how well do these methods work compared to each other. The following code runs some U-SQL over a simple two column csv file containing an arbitrary id and a name. The 640MB file contains 400 names, each repeated a number of times to build 40m rows. Names were repeated using a normal frequency distribution pattern to make the data more representative of real world data.
To assess the output we can look at the distribution of the sampled data to see how closely it correlates to the distribution of the original dataset.
The U-SQL code looks like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71
DECLARE @INPUT_FILE string = @"/AWPF_Demo/Data/names.csv" ; //40601850 ROWS DECLARE @OUTPUT_FILE string = @"/AWPF_Demo/Data/names_results.csv" ; @rawdata = EXTRACT id INT, name string FROM @INPUT_FILE USING Extractors.Text(); //--Allocate each row a row number, and take 1 in every 100 @sequenceddata = SELECT * , ROW_NUMBER() OVER (ORDER BY id) AS rn FROM @rawdata; @sampleddata1 = SELECT id, name FROM @sequenceddata WHERE rn % 100 == 0; //--Use the SAMPLE ANY clause in U-SQL @sampleddata2 = SELECT id, name FROM @rawdata SAMPLE ANY(406018); //--manually calculated as 1% of the input row count //--Use the SAMPLE UNIFORM clause in U-SQL @sampleddata3 = SELECT id, name FROM @rawdata SAMPLE UNIFORM(0.01); //--1% //--Find the name distribution of the original data, and sampled datasets @outputbaseline = SELECT name, COUNT(*) AS Qty FROM @rawdata GROUP BY name; @outputdata1 = SELECT name, COUNT(*) AS Qty FROM @sampleddata1 GROUP BY name; @outputdata2 = SELECT name, COUNT(*) AS Qty FROM @sampleddata2 GROUP BY name; @outputdata3 = SELECT name, COUNT(*) AS Qty FROM @sampleddata3 GROUP BY name; //--Join all datasets together for analysis @Output = SELECT b.name , b.Qty AS QtyOrig , o1.Qty AS QtyMod , o2.Qty AS QtyANY , o3.Qty AS QtyUNIFORM FROM @outputbaseline AS b LEFT JOIN @outputdata1 AS o1 ON o1.name==b.name LEFT JOIN @outputdata2 AS o2 ON o2.name==b.name LEFT JOIN @outputdata3 AS o3 ON o3.name==b.name; //--Output the data OUTPUT @Output TO @OUTPUT_FILE ORDER BY QtyOrig DESC USING Outputters.Text(quoting:TRUE);
So what happens when we take the resulting data and plot the sampled distributions against each other?
- The Blue line is the number of times each name appears in the original dataset (on the right axis).
- The Orange line is the distribution from the ROW_NUMBER() and Modulus.
- The Yellow line is using U-SQL’s SAMPLE UNIFORM.
- The Grey line is using U-SQL’s SAMPLE ANY.
As you can see, the SAMPLE ANY is a terrible option to maintain data accuracy. In effect it looks like it just takes the top x rows from the file and discards the rest, which I explained earlier is a bad idea.
However the ROW_NUMBER/Mod and the SAMPLE UNIFORM approaches are both staggeringly accurate to the original, with variances +/-2% for each name. This isn’t any good for exact numerical calculations (total sales £ for example), but for looking at trends over very large datasets this sampling approach is a good option.
So, should you use ROW_NUMBER/Mod or SAMPLE UNIFORM? Obviously SAMPLE UNIORM is simpler code, but how do they perform compared with each other?
- The ROW_NUMBER/Mod approach, using the above dataset used a single vertex, with a total compute time of 29s, read 640MB and wrote 5KB.
- The SAMPLE ANY approach used two vertices, with a combined compute time of 2s, read 34MB and wrote 5KB.
- The SAMPLE UNIFORM approach used four vertices, with a combined compute time of 26s, read 766MB and wrote 5KB.
So the SAMPLE ANY, although poor for data consistency allows a much faster execution by only reading a small section of the data.
The ROW_NUMBER/Mod and SAMPLE UNIFORM approaches are very comparable in terms of performance, so it wouldn’t surprise me if they were doing something similar under the hood. However out of simplicity I’d recommend the SAMPLE UNIFORM method.