Some thoughts of your typical data shepherd / data plumber / data dance teacher sort of person.
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.
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.
l 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 let 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.
So first l created a variable in the query window see figure 1.
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.
In the previous post (see here) l set up the blank datasets as parameters storing the values of the database server and name. Whilst attempting to create a suitable dataset to a report for an internal customer, l was frustrated that l could not do it using Power Query. Yet on the other hand l know that l could write some TSQL that would give me the dataset l wanted very easily. So l wondered if this was possible to combine the two? So lets return to the SQL.Database function which has this very nice example Sql.Database("localhost", "Northwind", [Query= select * from Customers]) The last setting is "Query as text - Provide a query text that is run on the server to return values". It's almost like someone thought it would be a good idea to allow a user to run a SQL query. A few minutes later l had the query l wanted to run and it looked something like this -:
DECLARE @IssueType AS VARCHAR(10) = 'Problem'; DECLARE @ClientName AS NVARCHAR(100) = 'RainbowPainters'; SELECT DATEPART(MONTH, [Issue_Created]) AS Month_Number ,DATENAME(MONTH, [Issue_Created]) AS Month_Name ,DATENAME(YEAR, [Issue_Created]) AS Year_Name ,COUNT(DATEPART(MONTH, [Issue_Created])) AS Total FROM [dbo].[IssueData] AS ID WHERE [Issue_type_name] = @IssueType AND [ClientName] = @ClientName GROUP BY DATEPART(MONTH, [Issue_Created]) ,DATENAME(MONTH, [Issue_Created]) ,DATENAME(YEAR, [Issue_Created]); Next when back to the dataset opened the 'Advanced Editor' and added the query, you can it in Figure 1 below Figure 1 - Advanced Editor window with the new query in place
To make it a bit easier to read below is the actual text from the 'Advanced Editor' window above. All that has been added is the text in red,and the query text which is in italics
letSource = Sql.Database(DatabaseServer, DatabaseName ,[Query = "DECLARE @IssueType AS VARCHAR(10) = 'Problem'; DECLARE @ClientName AS NVARCHAR(100) = 'RainbowPainters'; SELECT DATEPART(MONTH, [Issue_Created]) AS Month_Number ,DATENAME(MONTH, [Issue_Created]) AS Month_Name ,DATENAME(YEAR, [Issue_Created]) AS Year_Name ,COUNT(DATEPART(MONTH, [Issue_Created])) AS Total FROM [dbo].[IssueData] AS ID WHERE [Issue_type_name] = @IssueType AND [ClientName] = @ClientName GROUP BY DATEPART(MONTH, [Issue_Created]) ,DATENAME(MONTH, [Issue_Created]) ,DATENAME(YEAR, [Issue_Created]);" ]) in Source
When l refreshed the dataset, the data appeared :-) Job done? No, as often happens one client gets something and then everybody wants why they have. So l was asked if we could create the same set of reports for another customer. There was two choices at this point, l could just change the hard code client name, or could l parameterise the client name. Having heard this before l knew about passing parameters from excel to filter datasets see the following posts. https://blog.oraylis.de/2013/05/using-dynamic-parameter-values-in-power-query-queries/ http://blog.crossjoin.co.uk/2015/11/24/using-parameter-tables-to-control-data-refresh-in-power-bi/ Having looked at the blog posts which declare and then used parameters this looked like a simple concept. Kudos to the Power BI team for that. First step was to add additional dataset as a parameter. So l now had three dataset as parameters, the latest one being 'ClientName', which stores the client name
Figure 2 - List of datasets containing parameters So having looked at the blog posts above l realised that l just need to declare a query which would hold the query text and pass in the Client Name parameter to the query text. Following some trial and error, l ended up with the query text below let pQuery = "DECLARE @IssueType AS VARCHAR(10) = 'Problem'; DECLARE @ClientName AS NVARCHAR(100) = '" & ClientName & "'; SELECT DATEPART(MONTH, [Issue_Created]) AS Month_Number ,DATENAME(MONTH, [Issue_Created]) AS Month_Name ,DATENAME(YEAR, [Issue_Created]) AS Year_Name ,COUNT(DATEPART(MONTH, [Issue_Created])) AS Total FROM [dbo].[IssueData] AS ID WHERE [Issue_type_name] = @IssueType AND [ClientName] = @ClientName GROUP BY DATEPART(MONTH, [Issue_Created]) ,DATENAME(MONTH, [Issue_Created]) ,DATENAME(YEAR, [Issue_Created]);", Source = Sql.Database(DatabaseServer, DatabaseName,[Query=pQuery]) in Source
Figure 3 - showing the query text in the Advanced Editor Window Much to my delight this syntax works as expected. So l can now have a way that it is possible to specific parameters which can be passed to SQL server. From reading about M (Power Query Formula Language) there's more to learn. For the moment l am happy with the new found knowledge, whilst l play with some of the other technologies offered by PowerBI. The only downside is this only works in PowerBI Desktop :-(
One of the attractions of being a programmer was that l could find ways to make routine tasks easier and quicker. As our company and clients have discovered the joy of Power BI also the reports l have created. There are more clients are asking for reports. Some of these are the same reports to ensure everyone has the same view of their data. Scenario There are two databases l populate with data, one of these is on my machine, the other is an Azure database. As you would expect l use the local copy to test out various changes to the database. Then using the Redgate tool SQL Compare l can script any changes and apply them to the Azure database. It is possible to change the data source for each dataset easily using the following method. From the main window, on the 'Home' tab click once on the 'Edit Queries' button see Figure 1 Figure 1 - showing the location of the 'Edit Queries' button
This will open the 'Edit Queries' window now to see where the data source for a selected dataset. First select a dataset in the example shown in Figure 2, it is 'DimDate'. Next click on the 'Advanced Editor' button see figure 2.
Figure 2 - Showing location of 'Advanced Editor' button When the Advanced Editor window opens it will look something like the one shown in Figure 3 below. Figure 3 - Advanced Editor window, with M (Power Query Formula Language) query showing the Database Server Name and Database Name The syntax of the statement is relatively simple. In this case we are looking at the Sql.Database data function, the syntax is simple and looks like this -: Source = Sql.Database( "Name of Database Server", "Name Of Database") This is the text shown in Figure 3 let Source = Sql.Database("localhost", "DBdata"), dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data] in dbo_DimDate
To change the data source to point at the Azure database the only changes that have to made are as follows
let Source = Sql.Database("AzureDatabase", "DBdata"), dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data] in dbo_DimDate Job done. This was nice until you have to do it several times, and personally I got bored then wondered if there was a better way? I am glad you asked if there is a better way let me show you. Having seen some examples where the data source was an excel spreadsheet l was aware that it was possible to supply parameters to change the data source. From one spreadsheet to another one. The first step is to set up the parameters. Open the Query Editor window, click on the 'New Sources' button from the list click on 'Blank Query' (see Figure 4)
Having set up the DatabaseServer dataset (parameter value), repeat the process and create a DatabaseName dataset (parameter value). Figure 6 shows what you query editor window would look like if you entered the same details shown in this example.
* DatabaseServer - containing the value 'AzureDatabase' or the name of the database server you are going to connect to. * DatabaseName - containing the value 'DBData' or the name of the database you are going to connect to.
If we look at the advanced properties of the dataset 'DimDate' the data source would look like the one shown below. let Source = Sql.Database("AzureDatabase", "DBdata"), dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data] in dbo_DimDate Next step is to replace the database server name and database name with the names of the Datasets that have just been set up. So the data source will look like the one below. let Source = Sql.Database(DatabaseServer, DatabaseName), dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data] in dbo_DimDate
The advanced query window should look like the one show in figure 7 below. When the data source is queried the values of 'DatabaseServer' and 'DatabaseName' are replaced by the values from the relevant dataset. In this PowerBI desktop report l just replaced all the hard coded instances of the database server name and database name with the relevant parameter. Which allows me to switch between two databases with the ease l wanted, only with PowerBI desktop :-( Figure 7 - Showing the final M (Power Query Formula Language) text (data source) If you click on this link you can download a copy of a .PBIX with the two dataset's shown above. There is more you can do with this you will need to wait till part two of this blog post for more details.
Figure 3 - Second data source settings window Now you can get the details eg Username and password used to connect to the data source are stored. If these are required. Figure 4 - Data source credentials window So we now have the follow credentials Username = [email protected] Password = JoeB123 N.B. We will be using these details later. Tip if using multiple accounts with PowerBI online Next step would be to publish your reports to the PowerBI service at PowerBI.com. If you have multiple accounts which you use to publish your reports to a PowerBI service. Then one tip is log out of the account and log back in as there is nothing in the PowerBI desktop application which indicates which account is logged in. Adding credentials in PowerBI Service (Aka PowerBi.com) One quick tip here is to open PowerBi.com using Internet Explorer. At the time of writing process shown next did not reliability render in other browsers. Log into PowerBI.com once successfully log in, go to the top right hand side of the window. Click with left hand mouse button the settings icon, from the menu click on the 'Settings' menu item.
Once the 'Settings' window opens click on the 'Datasets' tab Once in 'Settings->Datasets' click once with left hand mouse button on the triangle to the left of 'Data source credentials' Once the 'Data source credentials' have been expanded click on the 'Edit credential' link. Once the credentials configuration window opens the drop down box below the authentication method select the 'Basic' authentication method. When you select the 'Basic' Authentication method, text boxes will appear at the bottom of the form. Enter the username and password, then click on the 'Sign In' button. If the user name and password has been successful then you will see a message appear in the top right hand corner of the PowerBi window, indicating the data source has been successfully updated.
Whilst preparing a simple dataset to with a stacked bar chart in PowerBI l included both the month number and name. The dataset is shown below
The data set was created in PowerBI desktop, the next step was to plot the data on a clustered column chart. The month name was the value l wanted to appear on the axis, there was a little challenge with the way data was being displayed. The month names where being displayed in alphabetical order (see Figure 2)
Figure 2 - Power BI chart after placing data on the canvas What l wanted was the month names to be sorted according to month numbers so l knew l had missed something. First step was to go to the data tab to have a look at the data. So I licked on the the dataset and selected the 'Modeling' tab (see Figure 3). Figure 3 - Data view 'Modeling' tab selected the 'Month_Name' column selected Everything looked the way l expected then with the 'Month_Name' column selected l clicked on the 'Sort By Column'. This was sorted by 'Month_Name' so it would be sorted alphabetically, so l changed it to sort by the 'Month_Number' see Figure 4.
Figure 4 - The first figure shows the default setting for the 'Month_Name' column, the second figure shows the amended selection to sort the 'Month_Name'Column by the 'Month_Number' column
Then l returned to the report view to review the change see Figure 5
Figure 5 - The clustered bar chart report showing the Month Names sorted in the order of the 'Month_Number' column
Success the months where ordering as l wanted them, next I'm off to resolve my next challenge, of which there is never a shortage.