(scottish) sql bob blog


Some thoughts of your typical data shepard / data groomer / data dance teacher sort of person.

Power BI no more

I've been working with my current company for over two years now.  During that time, on my own initiative, I decided to review the BI market to see what tool(s) that the company should be looking at adopting.  There are quite a few restrictions, data privacy, our clients are very cautious about their data.  So must be an on-premise server and yes I have asked lots of questions about this.  Also, our clients are mostly non-profit or charities, the budget is a massive consideration.  

PowerBI has been my tool of choice for reporting.  It is used for a POC (proof of concept) project reporting service desk incidents to our clients.  It is fantastic we had a Pro account, we shared the reports with our clients.  We loved it, the clients loved it. During the next two years, I invested time, energy, effort, working on other POC projects.  At the same time showing the relevant directors why we should look at Power BI for future development.  The deal breaker was an on-prem server, no negotiation on that point.  The start of 2017 exciting news, on-premise server was coming  Which version would get it, how much would it cost, could we use it.  Answers from Microsoft, zero, zilch, nada, nothing, brick wall impression.

So we waited and waited and waited.  Then, Power BI premium.  By the time I had digested the news, it felt like someone had kicked me black and blue.  There is no point in even approaching our Managing Director with a minimum of £3k per month for this project.  Our budget is not even in the same country, let alone same ballpark.  Next, our sharing reports with other free accounts using Power BI Pro, at least for some of our client has gone. Now have it, now you don't.

Your company might be a large enterprise, then these costs are reasonable, we are not a large enterprise.  So in essence over two years investment of my time down the drain, time to start again.  Now I am in the process of contacting clients for the POC project to show them how to access the reports, as the can no longer use their own Power BI accounts.  Disappointed would be a mild word to use to describe my feelings.

Very recently Tableau announced a price change.  Long story short, my line manager saw the new pricing structure, complete with on-premise server, per user cost of $35 per month, PowerBI cannot compete with that deal.  What will happen now is my company most likely to become a Tableau customer, Microsoft's loss.  The tools released at the data summit (June 2017) now places PowerBI toe to toe with Tableau.  Sadly I believe that Power BI will likely loose in the long run due to the pricing currently in place.  Whilst I understand the business logic and reasoning pursuing this model.  Microsoft has also demonstrated very clearly they do not understand the market in the way that Tableau seems to, which is reflected in their pricing structure.  Great for me, another toolset to add to my CV.  As I see it, Tableau leaves PowerBI dead in the water for customers like my company.  There is NO competition, Tableau has this market to themselves.  Which is bad news for me as a customer. 

Microsoft has got it right before, yes I will stand up, shout, cheerlead, and applaud when they do get it right.  As my tweet to James Phillps / Power BI team expressed.  When Microsoft get it wrong I need to be just as vocal, and I believe they have got it wrong, with the pricing in a big way, at least from where I am standing.  Yes I will continue to let people know about PowerBI, it not be with the same enthusiasm, that makes me sad :-(

Last but not least a more personal public apology to Chris Web (@Technitrain) was on the end of my rant via Twitter regarding pricing, sorry Chris, my bad.

Dynamic date variables using M (Power Query Formula Language)

Whilst working on developing some reports for our Service desk l was asked if we could just return a dataset containing the last 6 and 12 months of data.  This is the type of request l would normally use a TSQL script to resolve.  In this case one of the purposes of this project was to allow us to dog food both PowerBI and also self service BI more generally.

The challenge was quite simple find a way to filter data in a dataset based on specific datetime. to this with a specific date-time using the UI is very simple.  In Figure 1 below l will filter the column titled Issue_Created, which contains date-time values.

Figure 1 - The field 'Issue_created' selected showing the filter icon

From the list l can use this to filter by certain dates as shown below in Figure 2.  This lead me to wonder if it was possible to filter by date ranges.

Figure 2 - Filter by specific date

Below is a video showing how to filter records that either equal or occur after specific date, see Figure 3.

Figure 3 - Creating a filter to show record on or after a specific date.

Once l had set up the filter l then opened the 'Advanced Editor' to look at the M Code.   The code that does the filtering looks like this 

#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Created] >= #datetime(2015, 7, 7, 5, 16, 52))

The syntax looked relatively simple and straightforward, one of the main challenges is that in the snippet above the datetime is static not dynamic.  At this point there was a tweak to the specification, my internal clients l have every faith in me :-).  What they required was based on today's date, get the first day of the month and then return first day of the month 6 months and 12 month before.  This is a pattern which l have done many times in TSQL, this was going to be a bit more of an adventure.

Long story short, after some experimenting l arrived at the following formulas

= Date.AddMonths(Date.StartOfMonth(DateTime.LocalNow()),-6)
First day of the month 6 months ago based on current date

= Date.AddMonths(Date.StartOfMonth(DateTime.LocalNow()),-12)
First day of the month 12 months ago based on current date.

Whats the next step?  Create a variable which will contain the DAX formulas that have created above.  The first step to open PowerBI desktop and go to 'Edit Queries', this will open the 'Query Editor' window.  In the 'Query Editor' window click on the 'New Source' button and select 'Blank Query' from the pull down list see Figure 4.

Figure 4 - Creating a new Blank Query to act as a parameter

Now the blank query has been created the next step is to name the parameter, the name that l use in the example is Previous_FDM_6Months.  Next l type in the DAX formula -> = Date.AddMonths(Date.StartOfMonth(DateTime.LocalNow()),-6).  Once thats done, then click the green tick to update and evaluate the parameter.

Figure 5 - Setting the parameter name and inserting the DAX formula

So now l have parameter which returns a datetime value based on current date and time.  The last step was to amend the part of the M code l had grabbed earlier to filter the dataset.  Below is the line from the M code l use in production

    Source = Sql.Database("A_Cloud_Database","Some_Data"),
    dbo_Some_Data = Source{[Schema="dbo",Item="SomeRandomData"]}[Data],
    #"Filtered_Rows_Last12_Months" = Table.SelectRows(dbo_Some_Data, each [Issue_Created] >= Previous_FDM_12Months),

The data is extracted from the database and filter only return data where the Issue_Created is equal to or greater than the datetime value returned by the parameter Previous_FDM_12Months.

A complete reference of all the datetime functions in power query lanague aka M can be found here https://msdn.microsoft.com/en-us/library/mt296608.aspx.  If you want to download a PowerBI desktop file with the functions you can download it here.

Create dynamic text with M (Power Query Formula Language)
This post is wholly inspired by this post (https://blog.crossjoin.co.uk/2016/04/25/dynamic-chart-titles-in-power-bi/) by Chris Webb about creating chart titles.  We have a set of reports which are the same for each client, so when we are asked to create set of reports for a new client.  The process is relatively straightforward and simple.  One frustration is that we had to change the client name in a text box at the top of each page.  l am lazy so as programmer l like the computer to do the work for me.

So first l created a variable in the query window see figure 1.

Figure 1 – Select 'Blank Query' from the new source options

When the  blank query window opens, replace the text 'Query' with the name of the variable, then enter the value that will be returned from the variable.    See Figure 2 for details  

Figure 2 – Showing location of the variable name and where value should be placed.

In figure 3 what l have done is to enter the client name that will be used in the example 'Rainbows' and changed the variable name to 'ClientName'.  Once completed those details then click on the 'Close & Apply' button.

Figure 3 – Showing the client name and variable name.

Once returned to the main PowerBI desktop window, find the newly created dataset in this example it is called 'ClientName' with a field called ' ClientName'.  Now that the variable has been surfaced its time to use.  So let create a new measure, expand out the dataset click once with mouse on the ellipsis and select 'New measure' from the list see figure 4.

Figure 4 – Creating a new measure

When the new measure dialog box appears in this example l created a measure called 'TicketsTextBox' as  l want to the measure to appear in a box containing text.  Which is the reason for my naming convention.  The measure l used is as follows

TicketsTextBox = CONCATENATEX(ClientName,[ClientName]) & "- Tickets"  
In figure 5 you can see the newly created measure.

Figure 5 – showing the measure 'TicketsTextBox'

The next step is to get this measure on the report canvas, so insert a card visualization on the report canvas.  Then drag the measure to the values section on the visualization configuration section.  Click on the paint brush icon and expand out the 'Data label' section adjust the size of the label to the desired size.  Then turn off the 'Category Label.  These steps are shown in the video below.

Now you have a card with dynamic title, if you want to change the client name all you have to do is change the text in the variable. A sample PowerBI report file can be downloaded here which contains the report shown above.