A bit later this year, but even after my move to the US I decided to keep up the tradition of updating my Power BI analysis of the Top 2000 analysis for this year.
Read all about it in the original post from 2016.
Enjoy and happy holidays! See you next year.
This is my first blog post since I moved to Redmond. A lot of time has passed, sorry for that! I kept busy filling out forms, forms and some more forms. After that came more forms. I dream about forms, I breath forms. I am a form. Wait, let’s stop there.
Recently I had to figure out how to pass in settings to an SQL Server Integration Services (SSIS) package when calling it using dtexec from bash on Linux.
The dtexec utility is utility to execute a SSIS packages. The name goes back to the time that SSIS was called Data Transformation Services (DTS) – that is also the reason why SSIS packages carry the .dtsx extension. The dtexec utility is available for both Windows and Linux.
One of the interesting things you can do with dtexec is passing in values to variables (using the /Par(ameter) option or even change a connection string at runtime (using the /Conn[ection]). I had to do the latter. Here is where the fun started. The /Par and /Conn options (and maybe others) expect something like this: [name];[value]. For example:
1 | dtexec /F myfile.dtsx /Conn "MasterSQL";"Data Source=myserver;User ID=myuser;Initial Catalog=master;Password=mypassword" |
Notice the ‘;’ between the name of the source connection and the connection string. This is all great, unless you are trying to call dtexec from the bash command line in Linux, because ; actually means something in bash: when bash sees ; it thinks the current command has ended and the next instruction follows. That is not what we want here.
I had to escape a number of items to get this to work correct in bash:
1 | /CONN "MasterSQL"\;"\"Data Source=myserver;User ID=myuser;Initial Catalog=mydb;Password=mypassword\"" |
See what I did there? I had to escape the first ; (between ‘MasterSQL’ and ‘Data Source’) as well as escape the quotes that surround the data source definition itself and have it double quoted.
If you need to pass in a value to a string parameter you need to follow the same logic:
1 | /PAR "$Project::myparam"\;"\"myvalue\"" |
For a string variable you can pass in the value like this:
1 | /SET "\\package.variables[myvariable].Value"\;"\"myvalue\"" |
Hope this helps!
This is starting to become a tradition, sort of. My Power BI analysis of the Top 2000 has been updated to include 2018’s edition!
Read all about it in the original post from 2016.
Enjoy and happy holidays! See you next year.
| Datum | Tijd | Plaats | Organisatie | Soort evenement | Inschrijven |
|---|---|---|---|---|---|
| 17 October | 10:00 | Bussum | CraniumConnect | Voor leden | Website |
| 17 October | 17:00 | Den Haag | TopBI | Intern | n.v.t. |
| 18 October | 17:00 | Amsterdam | QNH | Intern | n.v.t. |
| 23 October | 16:00 | Amsterdam | Datafied | Publiek | Inschrijven |
| 25 October | 18:00 | Rotterdam | Motion10 | Publiek | Inschrijven |
| 29 October | 16:30 | Eindhoven | Valid | Publiek | Inschrijven |
| 30 October | 13:30 | Veenendaal | HSO | Op uitnodiging | Website |
| 30 October | 16:00 | Huizen | Sigma Data Consulting | Intern | n.v.t. |
| 31 October | 17:00 | Vianen | Rubicon | Publiek | Inschrijven |
| 2 November | 09:00 | Amsterdam | Breinwave | Op uitnodiging | n.v.t. |
| 6 November | 13:30 | Veenendaal | Hillstar | Publiek | Inschrijven |
| 7 November | 19:00 | Veenendaal | Infosupport | Publiek (beperkt aantal plaatsen) | Inschrijven |
| 8 November | 14:30 | Hoofddorp | Macaw | Publiek | Inschrijven |
| 12 November | 13:30 | Utrecht | Axians | Intern | n.v.t. |
| 13 November | 17:30 | Utrecht | Caesar Experts | Publiek | Inschrijven |
| 14 November | 10:00 | Bussum | CraniumConnect | Voor leden | Website |
| 14 November | 18:00 | Vianen | Sogeti | Intern | n.v.t. |
| 15 November | 15:00 | Amsterdam | Ireckonu | Op uitnodiging | n.v.t. |
| 29 November | 17:00 | Den Bosch | Power BI Gebruikersgroep | Publiek | Inschrijven |
I have updated the instructions and the code for the Custom Connector for CBS Open Data / Statline. Please see the original post:
Power BI provides great time intelligence features to calculate Year-to-date (YTD), Month-to-date (MTD) and Quarter-to-date (QTD) totals. There is no such thing as Week-to-date (WTD) or Period-to-date (PTD) where period could be any arbitrary period definition (I used two-month periods in my example below). If you want those, you will have to create the calculations yourself. I was inspired by this excellent blog post and created an ultimate time-intelligence calculations Power BI file. I used Matt Massons excellent date dimension generation script to generate the date table for my example.
Download the full Power BI file here or get just the DAX formulas. Enjoy!
Basic Measures
1 | TotalAmount = SUM(Sales[Amount]) |
1 | TotalQuantity = SUM(Sales[Quantity]) |
Day Measures
1 | Amount_LastDay = CALCULATE([TotalAmount],PREVIOUSDAY('Date'[Date])) |
1 | Amount_SameDayLastYear = CALCULATE([TotalAmount],SAMEPERIODLASTYEAR('Date'[Date])) |
1 | Amount_DOD_Variance = [TotalAmount]-[Amount_LastDay] |
1 | Amount_DOD_Variance% = DIVIDE([Amount_DOD_Variance],[Amount_LastDay]) |
1 | Amount_YOY_Variance = [TotalAmount]-[Amount_SameDayLastYear] |
1 | Amount_YOY_Variance% = DIVIDE([Amount_YOY_Variance],[Amount_LastDay]) |
1 | Quantity_LastDay = CALCULATE([TotalQuantity],PREVIOUSDAY('Date'[Date])) |
1 | Quantity_SameDayLastYear = CALCULATE([TotalQuantity],SAMEPERIODLASTYEAR('Date'[Date])) |
1 | Quantity_DOD_Variance = [TotalQuantity]-[Quantity_LastDay] |
1 | Quantity_DOD_Variance% = DIVIDE([Quantity_DOD_Variance],[Quantity_LastDay]) |
1 | Quantity_YOY_Variance = [TotalQuantity]-[Quantity_SameDayLastYear] |
1 | Quantity_YOY_Variance% = DIVIDE([Quantity_YOY_Variance],[Quantity_LastDay]) |
Week Measures
1 2 3 4 5 6 7 8 9 10 11 | Amount_WTD = IF ( HASONEVALUE ( 'Date'[Year] ) && HASONEVALUE ('Date'[WeekNumber] ), CALCULATE( [TotalAmount], FILTER ( ALL ( 'Date' ), 'Date'[Year] = VALUES ( 'Date'[Year] ) && 'Date'[WeekNumber] = VALUES ( 'Date'[WeekNumber] ) && 'Date'[Date] <= MAX ( 'Date'[Date] ) ) )) |
1 2 3 4 5 6 7 | Amount_LastWeek = SUMX( FILTER(ALL('Date'), IF(SELECTEDVALUE('Date'[WeekNumber])=1, 'Date'[WeekNumber]=CALCULATE(MAX('Date'[WeekNumber]), ALL('Date')) && 'Date'[Year]=FORMAT(VALUE(SELECTEDVALUE('Date'[Year]))-1,""), 'Date'[WeekNumber]=SELECTEDVALUE('Date'[WeekNumber])-1 && 'Date'[Year]=FORMAT(VALUE(SELECTEDVALUE('Date'[Year])),"")) ), [TotalAmount]) |
1 2 3 4 5 6 7 8 9 10 11 | Amount_SameWeekLastYear = IF ( HASONEVALUE ( 'Date'[Year] ) && HASONEVALUE ('Date'[WeekNumber] ), CALCULATE( SUM ( Sales[Amount] ), FILTER ( ALL ( 'Date' ), 'Date'[Year] = FORMAT(VALUES ( 'Date'[Year] )-1,"") && 'Date'[WeekNumber] = VALUES ( 'Date'[WeekNumber] ) && 'Date'[Date] <= MAX ( 'Date'[Date] ) ) )) |
1 | Amount_WTD_WOW_Variance = [Amount_WTD]-[Amount_LastWeek] |
1 | Amount_WTD_WOW_Variance% = DIVIDE([Amount_WTD_WOW_Variance],[Amount_LastWeek]) |
1 | Amount_WTD_YOY_Variance = [Amount_WTD]-[Amount_SameWeekLastYear] |
1 | Amount_WTD_YOY_Variance% = DIVIDE([Amount_WTD_YOY_Variance],[Amount_SameWeekLastYear]) |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | Quantity_WTD = IF ( HASONEVALUE ( 'Date'[Year] ) && HASONEVALUE ('Date'[WeekNumber] ), CALCULATE ( [TotalQuantity], FILTER ( ALL ( 'Date' ), 'Date'[Year] = VALUES ( 'Date'[Year] ) && 'Date'[WeekNumber] = VALUES ( 'Date'[WeekNumber] ) && 'Date'[Date] <= MAX ( 'Date'[Date] ) ) ), BLANK () ) |
1 2 3 4 5 6 7 8 9 10 11 | Quantity_SameWeekLastYear = IF ( HASONEVALUE ( 'Date'[Year] ) && HASONEVALUE ('Date'[WeekNumber] ), CALCULATE( SUM ( Sales[Quantity] ), FILTER ( ALL ( 'Date' ), 'Date'[Year] = FORMAT(VALUES ( 'Date'[Year] )-1,"") && 'Date'[WeekNumber] = VALUES ( 'Date'[WeekNumber] ) && 'Date'[Date] <= MAX ( 'Date'[Date] ) ) )) |
1 2 3 4 5 6 7 | Quantity_LastWeek = SUMX( FILTER(ALL('Date'), IF(SELECTEDVALUE('Date'[WeekNumber])=1, 'Date'[WeekNumber]=CALCULATE(MAX('Date'[WeekNumber]), ALL('Date')) && 'Date'[Year]=FORMAT(VALUE(SELECTEDVALUE('Date'[Year]))-1,""), 'Date'[WeekNumber]=SELECTEDVALUE('Date'[WeekNumber])-1 && 'Date'[Year]=FORMAT(VALUE(SELECTEDVALUE('Date'[Year])),"")) ), [TotalQuantity]) |
1 | Quantity_WTD_WOW_Variance = [Quantity_WTD]-[Quantity_LastWeek] |
1 | Quantity_WTD_WOW_Variance% = DIVIDE([Quantity_WTD_WOW_Variance],[Quantity_LastWeek]) |
1 | Quantity_WTD_YOY_Variance = [Quantity_WTD]-[Quantity_SameWeekLastYear] |
1 | Quantity_WTD_YOY_Variance% = DIVIDE([Quantity_WTD_YOY_Variance],[Quantity_SameWeekLastYear]) |
Month Measures
1 | Amount_MTD = TOTALMTD([TotalAmount],'Date'[Date]) |
1 | Amount_SameMonthLastYear = CALCULATE([Amount_MTD],SAMEPERIODLASTYEAR('Date'[Date])) |
1 | Amount_LastMonth = CALCULATE([TotalAmount],PREVIOUSMONTH('Date'[Date])) |
1 | Amount_MTD_MOM_Variance = [Amount_MTD]-[Amount_LastMonth] |
1 | Amount_MTD_MOM_Variance% = DIVIDE([Amount_MTD_MOM_Variance],[Amount_LastMonth]) |
1 | Amount_MTD_YOY_Variance = [Amount_MTD]-[Amount_SameMonthLastYear] |
1 | Amount_MTD_YOY_Variance% = DIVIDE([Amount_MTD_YOY_Variance],[Amount_SameMonthLastYear]) |
1 | Quantity_MTD = TOTALMTD([TotalQuantity],'Date'[Date]) |
1 | Quantity_SameMonthLastYear = CALCULATE([Quantity_MTD],SAMEPERIODLASTYEAR('Date'[Date])) |
1 | Quantity_LastMonth = CALCULATE([TotalQuantity],PREVIOUSMONTH('Date'[Date])) |
1 | Quantity_MTD_MOM_Variance = [Quantity_MTD]-[Quantity_LastMonth] |
1 | Quantity_MTD_MOM_Variance% = DIVIDE([Quantity_MTD_MOM_Variance],[Quantity_LastMonth]) |
1 | Quantity_MTD_YOY_Variance = [Quantity_MTD]-[Quantity_SameMonthLastYear] |
1 | Quantity_MTD_YOY_Variance% = DIVIDE([Quantity_MTD_YOY_Variance],[Quantity_SameMonthLastYear]) |
Period Measures
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | Amount_PTD = IF ( HASONEVALUE ( 'Date'[Year] ) && HASONEVALUE ('Date'[TwoMonthPeriod] ), CALCULATE ( [TotalAmount], FILTER ( ALL ( 'Date' ), 'Date'[Year] = VALUES ( 'Date'[Year] ) && 'Date'[TwoMonthPeriod] = VALUES ( 'Date'[TwoMonthPeriod] ) && 'Date'[Date] <= MAX ( 'Date'[Date] ) ) ), BLANK () ) |
1 | Amount_SamePeriodLastYear = CALCULATE([Amount_PTD],SAMEPERIODLASTYEAR('Date'[Date])) |
1 | Amount_LastPeriod = CALCULATE([TotalAmount],DATEADD('Date'[Date],-2,MONTH)) |
1 | Amount_PTD_POP_Variance = [Amount_PTD]-[Amount_LastPeriod] |
1 | Amount_PTD_POP_Variance% = DIVIDE([Amount_PTD_POP_Variance],[Amount_LastPeriod]) |
1 | Amount_PTD_YOY_Variance = [Amount_PTD]-[Amount_SamePeriodLastYear] |
1 | Amount_PTD_YOY_Variance% = DIVIDE([Amount_PTD_YOY_Variance],[Amount_SamePeriodLastYear]) |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | Quantity_PTD = IF ( HASONEVALUE ( 'Date'[Year] ) && HASONEVALUE ('Date'[TwoMonthPeriod] ), CALCULATE ( [TotalQuantity], FILTER ( ALL ( 'Date' ), 'Date'[Year] = VALUES ( 'Date'[Year] ) && 'Date'[TwoMonthPeriod] = VALUES ( 'Date'[TwoMonthPeriod] ) && 'Date'[Date] <= MAX ( 'Date'[Date] ) ) ), BLANK () ) |
1 | Quantity_SamePeriodLastYear = CALCULATE([Quantity_PTD],SAMEPERIODLASTYEAR('Date'[Date])) |
1 | Quantity_LastPeriod = CALCULATE([TotalQuantity],DATEADD('Date'[Date],-2,MONTH)) |
1 | Quantity_PTD_POP_Variance = [Quantity_PTD]-[Quantity_LastPeriod] |
1 | Quantity_PTD_POP_Variance% = DIVIDE([Quantity_PTD_POP_Variance],[Quantity_LastPeriod]) |
1 | Quantity_PTD_YOY_Variance = [Quantity_PTD]-[Quantity_SamePeriodLastYear] |
1 | Quantity_PTD_YOY_Variance% = DIVIDE([Quantity_PTD_YOY_Variance],[Quantity_SamePeriodLastYear]) |
Quarter Measures
1 | Amount_QTD = TOTALQTD([TotalAmount],'Date'[Date]) |
1 | Amount_SameQuarterLastYear = CALCULATE([Amount_QTD],SAMEPERIODLASTYEAR('Date'[Date])) |
1 | Amount_LastQuarter = CALCULATE([TotalAmount],PREVIOUSQUARTER('Date'[Date])) |
1 | Amount_QTD_QOQ_Variance = [Amount_QTD]-[Amount_LastQuarter] |
1 | Amount_QTD_QOQ_Variance% = DIVIDE([Amount_QTD_QOQ_Variance],[Amount_LastQuarter]) |
1 | Amount_QTD_YOY_Variance = [Amount_QTD]-[Amount_SameQuarterLastYear] |
1 | Amount_QTD_YOY_Variance% = DIVIDE([Amount_QTD_YOY_Variance],[Amount_SameQuarterLastYear]) |
1 | Quantity_QTD = TOTALQTD([TotalQuantity],'Date'[Date]) |
1 | Quantity_SameQuarterLastYear = CALCULATE([Quantity_QTD],SAMEPERIODLASTYEAR('Date'[Date])) |
1 | Quantity_LastQuarter = CALCULATE([TotalQuantity],PREVIOUSQUARTER('Date'[Date])) |
1 | Quantity_QTD_QOQ_Variance = [Quantity_QTD]-[Quantity_LastQuarter] |
1 | Quantity_QTD_QOQ_Variance% = DIVIDE([Quantity_QTD_QOQ_Variance],[Quantity_LastQuarter]) |
1 | Quantity_QTD_YOY_Variance = [Quantity_QTD]-[Quantity_SameQuarterLastYear] |
1 | Quantity_QTD_YOY_Variance% = DIVIDE([Quantity_QTD_YOY_Variance],[Quantity_SameQuarterLastYear]) |
Year Measures
1 | Amount_YTD = TOTALYTD([TotalAmount],'Date'[Date]) |
1 | Amount_LastYear = CALCULATE([Amount_YTD],PREVIOUSYEAR('Date'[Date])) |
1 | Amount_YTD_YOY_Variance = [Amount_YTD]-[Amount_LastYear] |
1 | Amount_YTD_YOY_Variance% = DIVIDE([Amount_YTD_YOY_Variance],[Amount_LastYear]) |
1 | Quantity_YTD = TOTALYTD([TotalQuantity],'Date'[Date]) |
1 | Quantity_LastYear = CALCULATE([TotalQuantity],PREVIOUSYEAR('Date'[Date])) |
1 | Quantity_YTD_YOY_Variance = -[Quantity_YTD]-[Quantity_LastYear] |
1 | Quantity_YTD_YOY_Variance% = DIVIDE([Quantity_YTD_YOY_Variance],[Quantity_LastYear]) |
I have written before on the subject of using Power BIs mapping capabilities (see the post on ArcGIS) and using the maps with Dutch geographical data (see using Dutch postal codes in Power Map). However, I have never did a more thorough exploration of the mapping capabilities of Power BI using Dutch data. Until now! (Niels, thanks for requesting me to do this).
Please see the Power BI report below – the first tab shows the dataset I used. The other tabs show the way(s) to work with the various maps to plot Dutch data. Unfortunately the ArcGIS visual is not supported in the mode I use here – please download the original Power BI desktop file below. Enjoy!
Oops, I did it again. It’s that time of the year. A day later than last year (sorry about that ;))….
Back by popular demand: the Top 2000 visualized in Power BI. Read all about it in last year’s post.
Enjoy and happy holidays! See you next year.
Anxiously you have waited for this day. The day you could finally get your hands on the latest version of the best database platform available. Today is that day. SQL Server 2017 is available and it runs on Windows, Linux and Docker. Find more info here: https://www.microsoft.com/en-us/sql-server/sql-server-2017. What changed? So many things it is hard to mention them all. Have a look at the Ignite sessions to get a feel for what happened.
Updated 7/10/2018: path updated below and new file on GitHub.
Since this post primarily concerns a Dutch data source, the rest of this post will be in Dutch. English summary below.
De open data die het Centraal Bureau voor de Statistiek (CBS) via Statline (http://statline.cbs.nl) aanlevert is van onschatbare waarde voor analisten. Over diverse thema’s wordt informatie gepubliceerd, zoals woning, demografie, inkomen en arbeid. Erg handig om je verkopen per provincie te vergelijken met de samenstelling van de bevolking bijvoorbeeld. De juiste informatie uit Statline halen was erg lastig, maar dankzij de Power BI Connector voor CBS Open Data / Statline is dat niet meer het geval.
Wanneer je de connector hebt geïnstalleerd (zie beneden) vind je de connector in de lijst met data bronnen in Power BI Desktop:
Klik op CBSOpenData en klik Connect. Vul één of meerdere zoekwoorden in (bijvoorbeeld arbeid) en klik OK:
De connector communiceert met de CBS Statline website en levert de thema’s en eventueel bijbehorende tabellen. Dit kan een tijdje duren, afhankelijk van het aantal thema’s en tabellen die horen bij de woorden die je ingevoerd hebt. Kies één of meerdere tabellen en je kunt ze laten of bewerken! Eenvoudiger wordt het niet!
Omdat de custom connectors nog erg nieuw zijn is er op dit moment een omweg nodig om ze te kunnen gebruiken in Power BI Desktop. Volg deze stappen:
The CBS Open Data / Statline website (http://statline.cbs.nl) is a very popular source of all types of statistical information about the Netherlands, provided by the government. In order to use this connector in Power BI, download the .mez file from Github and save the file in your custom connectors directory. Please see the documentation to find which directory to use. Enable the Custom Connectors preview feature in Power BI Desktop, restart Power BI and the connector should show up. You can of course also look at the source code on Github. Enjoy!