SQL, M or Dax? – part 2

This is a post about a post about a post.  Thanks to those of you who are entering comments in the original May 12 post titled SQL, M or DAX?  This is a popular topic. And thanks to Adam Saxton for mentioning this post in his Guy in A Cube Weekly Roundup.

This is a HUUUUGE topic and I can tell that I’ve struck a chord with many BI practitioners by bringing it up.  Please post your comments and share your ideas.  I’m particularly interested in hearing your challenging questions and your thoughts about the pros-and-cons of some less-obvious choices about whether to implement transformations & calculations in SQL, M or DAX.

This week, I have had engaging conversations on this topic while working on a Power BI consulting project for a large municipal court system.  As a consultant, I’ve had three weeks of experience with their data and business environment.  The internal staff have spent decades negotiating the intricacies and layers upon layers of business process so of course, I want to learn from their experience but I also want to cautiously pursue opportunities to think outside the box.  That’s why they hired me.

Tell me if this situation resonates with you…  Working with a SQL Server database developer who is really good with T-SQL but fairly new to Power BI & tabular modeling, we’re building a data model and reports sourced from a line-of-business application’s SQL Server database.  They’ve been writing reports using some pretty complicated SQL queries embedded in SSRS paginated reports.  Every time a user wants a new report, a request is sent to the IT group.  A developer picks up the request, writes some gnarly T-SQL query with pre-calculated columns and business rules.  Complex reports might take days or weeks of development time.  I needed to update a dimension table in the data model and needed a calculated column to differentiate case types.  Turns out that it wasn’t a simple addition and his response was “I’ll just send you the SQL for that…you can just paste it”.  The dilemma here is that all the complicated business rules had already been resolved using layers of T-SQL common table expressions (CTEs), nested subqueries and CASE statements.  It was very well-written SQL and it would take considerable effort to re-engineer the logic into a dimensional tabular model to support general-use reporting.  After beginning to nod-off while reading through the layers of SQL script, my initial reaction was to just paste the code and be done with it.  After all, someone had already solved this problem, right?

The trade-off by using the existing T-SQL code is that the calculations and business rules are applied at a fixed level of granularity and within a certain business context.  The query would need to be rewritten to answer different business questions.  If we take the “black box” approach and paste the working and tested SQL script into the Power Query table definition, chances are that we won’t be able to explain the query logic in a few months, after we’ve moved on and forgotten this business problem.  If you are trying to create a general-purpose data model to answer yet-to-be-defined questions, then you need to use design patterns that allow developers and users to navigate the model at different levels of grain across different dimension tables, and in different filtering contexts.  This isn’t always the right answer but in this case, I am recommending that we do as little data merging, joining and manipulation as possible in the underlying source queries.  But, the table mapping between source and data model are not one-to-one.  In some cases, two or three source tables are combined using SQL joins, into a flattened and simplified lookup table – containing only the necessary, friendly-named columns and keys, and no unnecessary clutter like CreatedDateTime, ModifiedDateTime and CreatedByUser columns.  Use custom columns in M/Power Query to transform the row-level calculated values and DAX measures to perform calculations in aggregate and within filter/slicing/grouping context.

I’d love to hear your thoughts and ideas on this topic.

 

 

 

 

 

 

 

 

SQL, M or DAX?

We live in a world of choices and we have many tools at our disposal.  In Microsoft Business Intelligence solutions using tools like Power BI and SQL Server Analysis Services, you have at least three different ways to perform data collection, transformations and calculations.  A question I get all the time is: “Which database or BI tool should be used to perform routine tasks?  Is it best to shape and transform data at the source, in Power Query using M script, or in the data model using DAX?”

In this series, I’ll demonstrate options for creating utility and dimension tables, columns and calculations using each option and discuss the advantages, disadvantages and recommended practice for each.

SNAGHTML176b0f5

I welcome your questions and ideas on these topics.  Please post comments to this post with your questions and challenges.  Let’s get started with one of the most common examples…

Creating a Date Dimension Table

A Date dimension table is an essential component in most any data warehouse or reporting database so techniques to generate these tables have been around for a long time.  The foundation of a Date dimension table is a table containing one row per contiguous date in a range that includes every possible transaction date or fact record.  To make reporting easier, it is common practice to have multiple date dimensions in the semantic model.  For example, if sales transaction facts have an Order Date and a Delivery Date, and both are used independently for reporting; there may be an Order Date dimension and a Delivery Date dimension in the model.

A common practice for building the dimension table is to just populate a single Date type column with the sequential date values.  After these rows are inserted, date part functions may be used to populate additional columns by referencing the Date value in an expression.  Most every language includes, for example, a MONTH() and YEAR() function to convert a date value into these date parts.

SQL

If you have a data warehouse or a relational database specifically suited to support your Power BI and reporting models, use that to define all of your tables using conventional techniques like T-SQL.  Examples for generating a date reference or dimension table are easy to find online, primarily because this is the oldest and most enduring technique, used for many years in conventional data warehouse design.  T-SQL is a flexible language but the SQL date part functions are pretty bare bones.  In the end, it really comes down to preference and language familiarity.

I think there is a good argument to be made for not only defining a date dimension using familiar SQL script but for persisting the table in the data warehouse along with other standardized dimension tables.  This approach is optimal when you are working with SQL Server or another relational database as your primary data source.

Reporting, BI and dashboard projects don’t always reply on a data warehouse.  Self-service BI solutions usually start with ad-hoc data mashups to support analytic reports rather than a holistic IT-driven solution.  If you aren’t using a relational database as the primary data source, you may be better off using a tool managed within Power BI or SSAS.

Example

There are several different techniques that include using a cursor or a WHILE loop to iterate through each date in a range, one row at a time.  One of the best techniques I’ve found is this example from Aaron Bertrand.  Adding special columns to keep track of holidays or special calendar periods (like Fiscal, 4-5-4, ISO, etc.) can require a lot of complex code.

— Date dimension script by Aaron Bertrand:

https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server


CREATE TABLE #dim
(
[date]       DATE PRIMARY KEY,
[day]        AS DATEPART(DAY,      [date]),
[month]      AS DATEPART(MONTH,    [date]),
FirstOfMonth AS CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, 0, [date]), 0)),
[MonthName]  AS DATENAME(MONTH,    [date]),
[week]       AS DATEPART(WEEK,     [date]),
[ISOweek]    AS DATEPART(ISO_WEEK, [date]),
[DayOfWeek]  AS DATEPART(WEEKDAY,  [date]),
[quarter]    AS DATEPART(QUARTER,  [date]),
[year]     AS DATEPART(YEAR,     [date]),
FirstOfYear  AS CONVERT(DATE, DATEADD(YEAR,  DATEDIFF(YEAR,  0, [date]), 0)),
Style112     AS CONVERT(CHAR(8),   [date], 112),
Style101     AS CONVERT(CHAR(10),  [date], 101)
);

— use the catalog views to generate as many rows as we need

INSERT #dim([date])

SELECT d
FROM
(
SELECT d = DATEADD(DAY, rn – 1, @StartDate)
FROM
(
SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate))
rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_objects AS s1
CROSS JOIN sys.all_objects AS s2
— on my system this would support > 5 million days
ORDER BY s1.[object_id]
) AS x
) AS y;

Power Query/M

In my opinion, Power Query is the best choice when other query transformations are also managed in Power Query.  For simplicity, you can keep all of your query and transformation logic in one place.  If you are just getting started with Power BI and aren’t inclined to use a different technique, use this one.

Example

Nearly every step in this process can be performed using menu selections and simple features in the Power Query user interface.  It just takes a little creativity to get started.  I’ve done this using a few different approaching until arriving at this one.  It’s easiest and most flexible.

  • Start by creating two parameters named “Dates From” and “Dates To”.  Assign them values to define the range of dates you need in the date dimension table; like January 1, 2010 and December 31, 2018.
  • Use the Get Data menu to create a Blank Query
  • The first two steps need to be entered manually.  Open the Advanced Editor and pastes these first two lines on a new line after the “let” command:

     DateCount = Duration.Days(Duration.From( #”Dates To” – #”Dates From” )),
Source = List.Dates(#”Dates From”, DateCount, #duration(1,0,0,0))

  • Switch back to the Transform ribbon tab and then click Convert > To Table
  • Change the name of the new date columna dn change the data type to Date
  • At this point, you can simply use the menus on the Add Columns ribbon to generate all of the date part columns you need in the date dimension table

The resulting M query can be viewed in the Advanced Editor:

let
DateCount = Duration.Days(Duration.From( #”Dates To” – #”Dates From” )),
Source = List.Dates(#”Dates From”, DateCount, #duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
#”Renamed Columns” = Table.RenameColumns(TableFromList,{{“Column1”, “Date”}}),
#”Changed Type” = Table.TransformColumnTypes(#”Renamed Columns”,{{“Date”, type date}}),
#”Inserted Year” = Table.AddColumn(#”Changed Type”, “Year”, each Date.Year([Date]), Int64.Type),
#”Inserted Month” = Table.AddColumn(#”Inserted Year”, “Month”, each Date.Month([Date]), Int64.Type),
#”Inserted Month Name” = Table.AddColumn(#”Inserted Month”, “Month Name”, each Date.MonthName([Date]), type text),
#”Inserted Quarter” = Table.AddColumn(#”Inserted Month Name”, “Quarter”, each Date.QuarterOfYear([Date]), Int64.Type),
#”Inserted Week of Year” = Table.AddColumn(#”Inserted Quarter”, “Week of Year”, each Date.WeekOfYear([Date]), Int64.Type),
#”Inserted Week of Month” = Table.AddColumn(#”Inserted Week of Year”, “Week of Month”, each Date.WeekOfMonth([Date]), Int64.Type),
#”Inserted Day” = Table.AddColumn(#”Inserted Week of Month”, “Day”, each Date.Day([Date]), Int64.Type),
#”Inserted Day of Week” = Table.AddColumn(#”Inserted Day”, “Day of Week”, each Date.DayOfWeek([Date]), Int64.Type),
#”Inserted Day of Year” = Table.AddColumn(#”Inserted Day of Week”, “Day of Year”, each Date.DayOfYear([Date]), Int64.Type),
#”Inserted Day Name” = Table.AddColumn(#”Inserted Day of Year”, “Day Name”, each Date.DayOfWeekName([Date]), type text),
#”Renamed Columns1″ = Table.RenameColumns(#”Inserted Day Name”,{{“Month”, “Month Number”}, {“Quarter”, “Quarter of Year Number”}}),
#”Added Custom” = Table.AddColumn(#”Renamed Columns1″, “Quarter Name”, each “Q” & Number.ToText([Quarter of Year Number])),
#”Renamed Columns2″ = Table.RenameColumns(#”Added Custom”,{{“Day”, “Day of Month”}}),
#”Changed Type1″ = Table.TransformColumnTypes(#”Renamed Columns2″,{{“Quarter Name”, type text}}),
#”Reordered Columns” = Table.ReorderColumns(#”Changed Type1″,{“Date”, “Year”, “Month Number”, “Month Name”, “Quarter of Year Number”, “Quarter Name”, “Week of Year”, “Week of Month”, “Day of Month”, “Day of Week”, “Day of Year”, “Day Name”})

in
#”Reordered Columns”

Beyond ordinary Gregorian calendar date parts, specialized columns like Fiscal periods, holiday flags and components of a 4-5-4 calendar are a little easier to do in M because

the language includes advanced functions to support complex formulas.  I’ll share some of these advanced techniques in a later post.

DAX

Calculated tables were recently added to the tabular model designer in both Power BI Desktop and the tabular model project editor in SQL Server Data Tools (SSDT) for Visual Studio.  This feature uses a handful of new table-based DAX functions, which include CALENDAR and CALENDARAUTO, for easily defining date dimension tables directly in the model.

Getting started is simple:

  • Click the New Table button on the Modeling ribbon
  • Enter the following script into the formula bar:

       My Calendar = CALENDAR(date(2018,1,1), date(2018, 12, 31))

  • Now you can add new calculated columns and apply the appropriate DAX functions to create date part columns using the Custom Column editor.

This post on the Power BI Tips site demonstrates a few variations of DAX-generated calendar tables: https://powerbi.tips/2017/11/creating-a-dax-calendar/

Each column in the table will be a separate expression or calculated column.  With regard to performance or model optimization, there is no additional overhead or good argument not to use DAX to generate a date dimension table.  However, using Power Query & M to transform data and create some tables and then DAX to generate other tables in the model can be more messy than keeping everything in one place.

So, why are there two different ways to create tables in Power BI?

This is an excellent question and it is really just an artifact of the evolution of the product and its constituent technologies.  The modeling tools behind Power BI (DAX, VertiPaq & the SSAS Tabular model) were created first and became the Power Pivot add-in for Excel.  As the DAX language evolved, that development team gave us the ability to generate tables using DAX Script.  Not long after that, a separate product team created Power Query and the M data mashup language.  Power Query was also made available as an add-in for Excel.  Eventually both tools found their way into the Power BI Desktop product.

Final Recommendation

If you have a SQL Server data warehouse, you can use SQL to create date dimension tables.  It’s usually best to unify all of the reporting data in the data warehouse or data mart to create a single version of the truth for reporting.  You can also use tools like SSIS or Azure Data Factory to build and manage these objects before the data is imported into Power BI or the Analysis Services data model.

If using Power BI only, use the Get Data tools to build all the tables, including the date dimension(s).  There is nothing wrong with using the DAX techniques but that is my second choice in the Power BI toolbox, for this particular need.

Facebook Live Pop-up Session Recording

A big THANK YOU to everyone who attended the Facebook Live Pop-up session today. This was a fun event and I enjoyed taking and answering your questions. A recording of the live session is available right here:

We’re not quite sure why the video jumped around a bit but it didn’t seem to be too much of a distractor. We tested everything and had no issues until the event (of course!). I recently upgraded my older LifeCam 1080p camera to the LifeCam Studio HD camera – so maybe blasting more bits through the service caused some unrest. With that exception, I’d love to have your feedback about the format and the whole live Q&A concept.

Another concept I’m kicking around is to provide a forum for you and others to request guided training content based on your questions.  It would be sort of a Q&A forum that would drive the way we build online training lessons.  What do you think?
Please post your comments below.

PASS Facebook Live Pop-up Expert Series

There are some great learning opportunities available from PASS and I am exciting to participate in two online events this month!

Please join me on April 24 for a live chat about all things BI, reporting and data analytics.  Ask me anything you want about these or related topics and I’ll answer your questions, talk about my experience or find out what the community has to say.  The session is on Tuesday, April 24th at 6PM UTC (that’s 11 AM here in Pacific Time).  Follow the image link to put it on your calendar.  You can use the comments on the Facebook post or send an email if you’d like to queue up your questions ahead of time.

Here are some topics to get you started:

  • Is self-service reporting and data modeling really sustainable?
  • New features are released (monthly).  How do we keep (IT/or users) up to speed?
  • Where can we find best practice guidance for our solutions?
  • What’s the best tool to use for a certain style of reporting solution?
  • Differences between Power BI in the service and on-premises
  • What is the future for SSRS and Power BI Report Server?
  • How do I license Power BI, Report Server and my users?
  • Can we expose reports externally?
  • What is the migration path from Power BI tabular data models to on-premises and Azure AS models?
  • What’s up with mobile reporting?
  • How do I get started with Power Query & M
  • What’s the best way to learn and get support with DAX and calculations?
  • How do Excel, SSRS, Power BI and SSAS work together (or do they?)
  • What’s unique about your scenario and business rules?  How do we best proceed and meet those requirements?
  • What’s up with reports in SharePoint, external-facing application, embedding reports and self-service reporting?

There have already been some great sessions from Kendra Little and Bob Ward – which I have thoroughly enjoyed watching.  I’ve always loved Kendra’s presentation style and positive energy when she speaks.  Bob is a tried-and-true SQL Server expert with many years of experience on the SQL Server product engineering team.

Join me live, learn some good stuff and we’ll have some fun!

24 Hours of PASS

Every year community speakers present the 24 Hours of PASS (24HOP) which will be on April 25th.

24HOP Call for Speakers: Cross-Platform SQL Server Management

Every hour, a different presenter will deliver a 60 minute session on a specialized topic from midnight to midnight UTC.  My talk will be the Nine Realms of Power BI and the many different ways Power BI may be used along with other technologies to deliver Business Intelligence, reporting and analytic solutions.

My session is at 4PM Pacific Time on Wednesday, April 25th.  That’s 11PM UTC for you night owls in western Europe.  The rest of you can do the TZ math for your time zone.

How to add KPI indicators to a Table in Power BI

Yesterday a friend asked for a little help getting started with Power BI.  He’s a DBA and system administrator and wanted to cut his teeth on Power BI with a really simple dashboard-style scorecard report.  Using a list of database servers with license expiration dates, he thought it would be a simple matter to calculate and show the expiration status for each server using a simple traffic light indicator.  The envisioned server list might look something like this:

image

Makes perfect sense, right?  This is a basic use case and a good application for simple KPIs; with the one minor caveat that POWER BI DOESN’T SUPPORT THIS!

This topic has become a bit of a soapbox topic for me because it’s a capability that, in my opinion, is a very obvious gap in the Power BI feature set.  After unleashing my rant, I’ll demonstrate a solution in this post.

<BEGIN RANT>

The most interesting thing about this missing feature is that for many years it has existed in the products that evolved into the current Power BI product .  Key Performance Indicators (KPIs) are defined as scriptable objects in SQL Server Analysis Services (SSAS) with tremendous flexibility.  KPIs are simple…  the STATE element of a KPI (often considered “Bad”, “OK”, or “Good” status) is translated into a visual indicator, usually an icon (commonly “Red”, “Yellow” or “Green”, respectively).  There are variations on this theme but it’s a very simple concept and a good solution has existed for many years.  In SSAS Tabular, the State logic was dummied-down to a slider control that eliminated some of the flexibility we have in the earlier multidimensional project designer but it still works.  The slider UX expects that the state applies when a value is equal to or greater then the threshold for yellow and green, and less-then the threshold value for red. Queries returned from SSAS include metadata that tells Excel, Power BI visuals or a variety of other client tools: “The KPI state is 1 (meaning ‘good’) so display a square green icon for this item”.  If you have the luxury of building your data model in Analysis Services using the SQL Server Data Tools (SSDT) designer for tabular models – or in Power Pivot for Excel, you would define a KPI using this dialog:

See the source image

The actual return value for a KPI designed this way is really just “–1”, “0” or “1” which typically represent “Bad”, “OK” and “Good” states, respectively.  As I said, you have other options like switching the red/green position or using 5 states rather than 3.  The multidimensional KPI designer even gives you more flexibility by allowing you to write a formula to return the VALUE, STATE and TREND element values for a KPI separately.  It would be wonderful to have the same capability in Power BI. It would be marvelous if we could the slider UI like this and then an Advanced button to override the default logic and define more complex rules in DAX!  The SSAS architecture already supports this capability so it just needs to be added to the UI.

If you design your data model using SSAS multidimensional or tabular, or using Power Pivot for Excel (which was the first iteration of Power BI) KPIs are just magically rendered in native Power BI visuals like a Table or Matrix.  But alas, Power BI Desktop does not have this well-established feature that could easily be ported from Power Pivot or the SSAS Tabular model designer.

</ END RANT>

…back to my friend’s simple scorecard report.

Using out-of the box features, the best we could do was this…
Create a calculated column in the table that returns -1 when the expiration date has passed, 0 if it is today and 1 if the expiration date is in the future.  Here’s the DAX script for the column definition:

Expiration Status Val =
IF([EndofLifeDate] < TODAY(), -1
, IF([EndofLifeDate] > TODAY(), 1
, 0
)
)

Next, add some fields and the new column to a table visual and use the Conditional Formatting setting in the table properties to set rules for the Back Color property of the calculated column, like this:

ConditionalFormatting

Here’s the table with the conditionally-formatted column:

image

Why Not Use the KPI Visuals?

The standard KPI visual in Power BI is designed to visualize only one value rather than one for each row in a table.  Like an Excel Pivot Table, if KPIs were defined in a Power Pivot or SSAS cube or model; a Power BI Table will simply visualize them but the Power BI model designer doesn’t yet offer the ability to create KPI objects.

Several community developers have tried to fill the feature gap with custom visuals but every one of them seems to address different and specific use cases, such as time-series trending or comparing multiple measure values.  I have yet to use one of the available KPI visuals that just simply allows you to visualize the KPI status for each row in a table, without having to customize or shape the data in unique and complicated ways.

How to Design Status KPIs With Indicators

Here’s the fun part:  Using the Expiration Status column values (-1, 0 or 1), we can dynamically switch-out the image information in another calculated column.  Power BI has no provision for embedding images into a report in a way that they can be used dynamically.  You can add an image, like a logo, to a report page and you can reference image files using a URL but you cannot embed them into a table or use conditional expressions.

Using this trick, you can conditionally associate images with each row of a table.  This is a technique I learned from Jason Thomas, whose blog link is below.  Using a Base64 encoder, I encoded three state KPI indicator images as text which I then copied and pasted into the following calculated column formula DAX script:

Expired = SWITCH([Expiration Status],
1,
“data:image/jpeg;base64,
iVBORw0KGgoAAAANSUhEUgAAADUAAAA1CAYAAADh5qNwAAAACXBIWXMAAB2HAAAdhwGP5fFlAAAAB3RJTUUH4gMcAQsJUd2JhgAAAAd0RVh0QXV0aG9yAKmuzEgAAAAMdEVYdERlc2NyaXB0aW9uABMJISMAAAAKdEVYdENvcHlyaWdodACsD8w6AAAADnRFWHRDcmVhdGlvbiB0aW1lADX3DwkAAAAJdEVYdFNvZnR3YXJlAF1w/zoAAAALdEVYdERpc2NsYWltZXIAt8C0jwAAAAh0RVh0V2FybmluZwDAG+aHAAAAB3RFWHRTb3VyY2UA9f+D6wAAAAh0RVh0Q29tbWVudAD2zJa/AAAABnRFWHRUaXRsZQCo7tInAAAHtklEQVRogd2aS2wUVxaGv1tV7ifQwXQHgrAHxYwmwY5Qdh4xGjIbM9tZDZYGthkkWJoVS1b2MkhMts5IZpc1Xg2LCHZRFBsJKR4hE2nkuHFii35W3XNmUVXd1Xa73X5gW/xSubq7Stb56j917rl1y6iq8p7JOeoA3oXeSyjvoP/h8sYK373+gcXyEj+u/sR6vcLC6lLHOWOlEQqZPJ+VLjFaHOHq0BWGT509sBjMQdxT6423zC3O8/jFfAvg3Mk8xWyKlONwLp/GqiIqWFVWKnUaVlhvWFYrNSAEvXF5gsnRCQrpE0cHtbyxwszzWeYW5xlwXUZOn+LTwZN8XMjjqMWK4IslsBY/+uxbG+7F0hTFGo//VRq8qQu/VBr41jI5OsHU+K09u7dnqOlns3z9/bcoTa4OnePzUoG0Y7BWWjBWhCACSML4YmlawRehYYWmWBpWqAtUZICff6tSDyz3xm9y74+33j3UwuoSd57M8HLtFVcvnGXi4nk8A4EIViyBDUECEQJrCTQECsTiW8GXgGb0uSnhFoPVrVATwXMHWKsK5WrAHwYv8vD6FGOlkXcDNbc4z/2nj0h7lptjlzifz4IqVjVyJQKRROq10i+GsjQl6AklUUSOM8Dr9SYDJsuDa7eZHJ3oK86+q9/c4jx352cYK53m75/8npTjICIoIKoIiqqihBGpKigQ7xSU9vFY8SXtdmlFfIYLHuWqz935GYC+wPqCioG++N1H/OVCCUPojiQARMLqphrDtSHi70mQiDf+pfV3M5wVy+kM5FK5vsF2hIqB/jx0jj+dO40VCQMwisEAIERQoqFr0abxPgZvfdcEvCIKAq2066a002T4dH9gPTuKhdUl7j99xCdnCoyfLYTuiGCtRAUhLApxxbMq4XEVRKUDUNq+oRoCxFsMvTU5t4IV82nuP320ZUDvG+rOkxk8x+f6cAmrUeBRlbNRAYirnY1LuErLMdsC2wQYQcag4W9g+yhZxRz4WuPOk5ndQ00/m+Xl2iv+evFDVG3oQFzhos0mYaLjsVPx7zbqIqS119Y++dnSvkd7yYplqJDi5dorpp/N9g+1vLHC199/y+iZk5xwaUNouMVpF3YJ0irhQeRUPE7FpT7eRAUr3cFs5FY/EvEp5jymn3/D8sZKf1Azz2cJtM6VYj7hjG11B62g4zRMHI/vNRvBxRciBEwCaMdvwS77msGcQ8ZzmXm+1a0tUHFzOlYqYG3QceV9mxxIE21P4nOrm0ikq5W2wyFAuFmlBSS77Naagc+FD3LMLc6z3njbGypuTj/KQKDJICOwpGt2K1B7L/jaTklfhEAVXxLuiOCr4mvvqredHBoMuC5zi/O9oR6/mOf8yQyodKRe3GW3e7hN7tgYONEetRwWglbqxfdd6JYfpeFepCoM5lI8ftEDanljhYXVJUoZNxGMtKcNW7bksSABuclZVQLR6Nz256aEzu1HWU9ZWF3qKBgdUN+9/gGAM1kPX8Kr2NwOysYgAc0u91nTSmt6kdyCqIltquJrP0W8t05lvI7YYVObtFheopjPUmvW8YyDqEEcgzUGF3CMwZiwOTKGVpsUdwmt5rY1mCZTLuFQdLH6GWx3Uq1Z50w+y2K53WF0QP24+hM5z+CLIkZwHYMrBpcQzDHahoLob6IZ7QIlcaVLFIWDAoqVdsPYu0Kt1ysYAw0RPGPwYqcwOMbgRO6ELsVQIVLcYcdTEBuVaRs5FGgI1VTtOs3YjxxjWK9XukMtrC7x6YcnqYvPgInSTg2u2QRFmH5JxfMlicBECUs32i7leyzdO+lE2mXhl23SD8IUCkSxRkO3TJh+jonuKcI9JH0KFTaqRE6FbgWETh3mY+Bt51OihBUKxTUGN4JyIHKq06p4Imgjp+L0O4pn2jtOEhWitgYM2qp6ZvO0PHFfHfXD+Y5xaqw0wtuG3fbksLrR0b8l+zg5IqC3DdvxtKkDqpDJ77qxPA4SVQqZfOt7B9RnpUv0MOrYqmHD2GN1QI0WR3hTqZFNZQ49sL0qm8rwplJjtLhN+l0dugLARj043Mj2oTjWOHbYBDV86ixjpRFqwaaR9RirFhjGSiMdiwlb5lM3Lk+wVm1izPFfjzPGYa3a5MblzmeAWyKfHJ3AtxYhfWjB7VVCurX0k9QWqEL6BJOjE/z8W5WUN3BoAe5WKS9c8um2SNc1x6bGb5F2s6xV5VAC3IvWqkI9sEyNb12/6go1fOosX37+N8rVAMc5fm45zgDlasC98ZtdVxt7rk998e9/8t/1VwwXPKwcj1HZdVyW1wM+LlzkP//4V9dzepa4h9enGDBZytV3Et+eVK7CgMny8PrUtuf0hBorjfDg2m3KlQYNSR14gLtVQ1KUKw0eXLvdc7l0x8FocnSCryamWP61eqRgDUmx/GuVryam9r/oBu0FrrvzMxTzaYo5Du0ecx2XchXKlf6AYI8L2b7WGCqkEPH3FfBO2utC9r5eOSjmPAZzDs3gYOFS3iG+cpDU9LNZpp9/Q8ZzufBBDocGqvsbrI1xENKH/3JIUptf4xnMpch6yqmMR61Z7+t/ZFMZNuoBtcCwVm0e7Ws8SXV74epMPkvaDZ9AnUi7Hee/bVhElYaFN8fthatuem9ejTtuOv4zwT3o/1Bjc+QsSaGpAAAAAElFTkSuQmCC”,
-1,
“data:image/jpeg;base64,
iVBORw0KGgoAAAANSUhEUgAAADIAAAAyCAYAAAAeP4ixAAAACXBIWXMAAB2HAAAdhwGP5fFlAAAAB3RJTUUH4gMcAQornab5IwAAAAd0RVh0QXV0aG9yAKmuzEgAAAAMdEVYdERlc2NyaXB0aW9uABMJISMAAAAKdEVYdENvcHlyaWdodACsD8w6AAAADnRFWHRDcmVhdGlvbiB0aW1lADX3DwkAAAAJdEVYdFNvZnR3YXJlAF1w/zoAAAALdEVYdERpc2NsYWltZXIAt8C0jwAAAAh0RVh0V2FybmluZwDAG+aHAAAAB3RFWHRTb3VyY2UA9f+D6wAAAAh0RVh0Q29tbWVudAD2zJa/AAAABnRFWHRUaXRsZQCo7tInAAAD9ElEQVRogd3aW28bRRTA8f/s2OTmxHYrNYQ0CXEFRCBRCShpKBWVSgvfmQ9QpeID9JkXJJ649KH1zpnDw8zOehPHsb27TtSRRrGjKDs/nzlztVFV5QMo2U03oKnSafsBv7/4Hnn7LwCnr9609pxWI1IgbDd8XudnJ609qzVIgdgYbPPFr2cMD+4B7WFagSTEcIfRs0fYjuX+wwcM9+8C7WAah5SIPqPnj8m6FtSj6tn/6oDBxwOgeUyjkIS402f04gdsx4IX8D7V/c93GdzrAc1iGoOUiAGjl0+xnQwViZCJqsL+8ZDB3Q2gOUwjkAril6dYa0AEvAMRNCLUC0iIzCf3txgMukAzmNqQS4jMgDhUHIiLmKIGXAHb2/2Ifi9rBFMLUsmJlz9iM0By1OUgeQWkIiUm/RT2hob+htbGLA2pjE4/PwkIVyAcOBdeF+8TKHY3EXA+YHpCf70eZilIZZ54/hibKerGZcPdGJVxiMo0kHPgIkY8AHvb9TALQyZn7NGzR2QmIMjH4EIN73PI8/A6djV1RaQEiup9+t91MGaRZXyBWO/3GP30TZgntJgjJHQbL+nTLyMUu1w+RvMcxjk6ziF3MOXxf/5n+eedAeZfaM4NKRFbjJ58XSLUoxGClzIPnKt2rXyM5i5EqUBMRKMuZi5IQuxscnx6gu1aUBKkiEiaMxKkjIo6FyIRMUVuzCqLYK6FJMT2OsffPphAaKyTEfHlJJiSOiJyFxFx6J2zzIuZCUmIrTWOHx6QdSwYAoQI8dOiUkREJhBF7lwfiWUwV0ISYrPLp1/uhpwwkCQ6Uf3FPJEwMomEHMnDz1k5URczFZIQG5ajz4YRYYKDIiBl18Jr6loqEeSkHGZFpo5OTWIuQRJiLePoaBPbycBkJIUxZaNUwSuq5TIdCTVFpEYUFsFUIAnRNRzudQIiM6HxmBJTIJSYI7F7SYyMxLxp6aBpGqYCOT87Yb2jHN5RMlsijCkwVYhW8kRL1ArKRcyl46BcDHnuWVMXfmEMmpJ8sujE6NVmky8Xr/A+Ns/2doApOXJ+doI1cDgQ1jq37xDSK/zxt+WdM9jeDt/99hqYsmg8ffUGiX/83l2Mws2WqxBwxer3NmJmIWDGMv42Ya5DwDX7kduAmQcBc2ysbhIzLwLm3CHeBGYRBCyw1V0lZlEELLhnXwVmGQQscfjQJmZZBCx5HNQGpg4CahzQNYmpi4CaR6ZNYJpAQAOH2HUwTSGgoWuFZTBNIqDBi55FME0joOGrt3kwbSCghcvQWZi2ENDS9fQ0TJsIWPA0ftFSbJv3+8Jfb7PWENAyBKr3HG0hYAXfDirOndpEwAoisqrywXxf639ULCnBYsymnQAAAABJRU5ErkJggg==”,
0,
“data:image/jpeg;base64,
iVBORw0KGgoAAAANSUhEUgAAADcAAAA3CAYAAACo29JGAAAACXBIWXMAAB2HAAAdhwGP5fFlAAAAB3RJTUUH4gMcAQoZVXGoowAAAAd0RVh0QXV0aG9yAKmuzEgAAAAMdEVYdERlc2NyaXB0aW9uABMJISMAAAAKdEVYdENvcHlyaWdodACsD8w6AAAADnRFWHRDcmVhdGlvbiB0aW1lADX3DwkAAAAJdEVYdFNvZnR3YXJlAF1w/zoAAAALdEVYdERpc2NsYWltZXIAt8C0jwAAAAh0RVh0V2FybmluZwDAG+aHAAAAB3RFWHRTb3VyY2UA9f+D6wAAAAh0RVh0Q29tbWVudAD2zJa/AAAABnRFWHRUaXRsZQCo7tInAAAGaklEQVRoge2aO28cRxKAv+qe2aVISzwu+MDZS3MtCTIfkmzgFFjKfMmlFxzg6O5P+BdQf4D+A0zs6MDEqQJnCwsgE1OwAQaGgAMoHE7nwEdCIndnuvuC7nktKZJL7qwhQwUUZrbn0fV1dXdVT6845xy/U1G/tQF1ytjgzPMnmOdPxlUdANG4Kkq76wDom38ZV5Xj8Vy6u4k73Mcd7pPubo6jSgCk7gnF9Q7of/MI/ccmAObfPRp//x5p3qizWmAMnjO7mzjzCt2eQbdncOYVZmej7mqBmuHc4T7pzgbRR/MQNyBuEH00n3fTuqVWuLS7jlxropfaoCdBT6KXFpFrzXyCqVNqg7MvnmKePyFaW8apyZJeI1pbxjx/gn3xtK7qgRrh0p2vUK0WMrsAaqKk15DZBVSrRbrzVV3VAzXBmb0t7Iun6NW7HkiXtQlqAr16z3t3b6sOE4CaQkHv60fItCb69AGIomhDAZxXZ0l3d3C/Gpr/+H7UJgA1eC7d3sAdv0Qv38epCZxM4FTTn6tmRfXHn+COX5Ju1xMaRgrnegeYZ5vom8sw1QLVAN0A1fTdUTf9eaZTM+ibK5hnm7jewShNAUYMZ3Y2cPYYdfueB8sgdCP8bgTAAK0bqNv3cPa4lsA+Mrgsb9T3P4Pme6DiAkhK3pIMOlxrvoe+/7CWwD4yuLS7jkxeRy2t5GBOxTjVqKoO5dIIoDGqs4pMXif57stRmQOMCC4L2PrB5x5MYpzEATKudstcY1y4F4nQD/6MffF0pIF9JHBp9zEy9z4y1waJcBKBikpdMz4VDolwKgKJkPlFZO590u7jUZgEjADO7G1hf/kJ/afPQTRONCjt4XLvnQKm4nBPeEa0994vP40ssF8JzvUOSLc3UEt3kKkbINoHbdEgkYeUCCdxGH+x92pFPRgoZGoatfSxj5UjCA1XgjO7mz5g3/ssZCISXplpFLySgWTjUQ80hMKFTEbff4g7fokZwYr90nB5wL61CnEzgAnIgKICRDQAlF2X8MZwjJvoW2sjCeyXhku766AN6ubKBZ+4eAqrbq2Atlde810Kzh3uY/a20HfuQhzhE2FXHHO1CBacBWfApeFoinuwAEiWUOMgilF37mL2tq4U2C8Fl3z3JXJjGrX4YQGFLR0tYEogCdgkwKVIXm6rGlYL4NCLS8iN6SsF9qHhskAbrawGQ8oGmuJog6dsgtgEcQli+4hLBmADKIOghmh17UqBfWi4tPsY1ZpBWn8AZ0peMMVvmwaw1IO4BGy/pEnpevaOtNo4ziIzM6jWzKW9NxRcHrDX7lSACvUwEjyWe8j0B+D6+XXJQYtGkVKX1mvL+RivDc71Dki76+jFedSErgBJ5gVbeCsHHIQySV4uZc+6YkwWXk2RiQj94QJpd33o0HBhuOzjatSZyz1UjJuSgbZsfN+PN9tHTPa7KM89aJO8YeTE5JMQdeb9x9whA/uF4NzhPubZJlG7Bdr5SSJr6WCcuLKhZY/1wPW92j643snumTWG64cGKnVrl4C2RO2WD+xDhIYLwaXbGyBH6M4sfroOxg8AiQsGnuiKvZIGYFtAii0/l4TGSIoyZ33dcjTU95Zz4bIsXXfmikJncqM4AdNDgmIDkO1VrhcTTA9xg9f7hRdtP4QJL7ozl09qF5FzP+31v/0CXu0Sf7p0ytMxqCZONfKkGBWdzCMp55DFp71q5lKejDLghMG0LfnhXzD1CY2//vNcuDM3H7MAeioYhO7pfIolFichMxGTL2M84ACcKwMWcMVk0j8VDLz3kh+8XeqDh2fCnem53tePkOb/iO+2z3yJ91JcrK4pr9MkLIcqrVLJRHwgH8hczpDkx31cb/rcj7lvHHNZ0hrdXjgbDMIY7IE5AnuEuCPE+nPscekYzo0/z+4T+7ooPwcMILq9cKFd2lM9l+2GqrnoYnDVV1YWofnCtTzksgQ775aG07rgWZL+/B/sf9Mzd2lP9VwRsGeHqtCLy2dTP/UfgX3tj9l57r1e8NTw2xVRZ/bcwH4CLt8N7cxCpIeudOBtVCeO0jLnEkAViTRRZ5Z0Z+ONgf0EXLq9gUzE6HbrapWPQXS7hUzEbwzslVCQ7Zfpdgv76+uxGHhVUbPXvc3LfzsRGioTSv/bL2rfyq1L1AcPTwT22v+H8lvKuz+2va3yDu5tlXdwb6u8g3tb5XcN938F12KOySmWigAAAABJRU5ErkJggg==”
)

The encoded binary strings correspond to these three images, in this order:

image

To reuse this, you should be able to simply copy and paste this code from here into a new calculated column.  You no longer need the image files because that binary content is now stored in the table column.  It really doesn’t matter what labels you use for the status key values as long as they correspond to the keys used in the preceding code.  I’m using the conventional -1, 0 and 1 because that’s the way SSAS KPIs work.

On the Modeling ribbon, set the Data Category for the new column to “Image URL”:

image

That’s it!  Just add any of these columns to a Table visual and WHAM, KPI indicators!

image

*Incidentally, since adopting Jason’s technique, Gerhard Brueckl came up with a method utilizing Power Query to manage and import image files that I will use in the future.  Prior to that, I used  this site Jason recommended in his post.  My thought is that if a separate table only stored three rows (one for each KPI status), the status key value would be used to relate the tables.  It would be interesting to see if using a related table reduces the PBIX file size or if VertiPaq can effectively compress the repeating values of image column.  May be a good topic for a later post.

http://sqljason.com/2018/01/embedding-images-in-power-bi-using-base64.html

https://blog.gbrueckl.at/2018/01/storing-images-powerbi-analysis-services-data-models/

 

CALL TO ACTION:

Please vote up this feature request so we can get the Power BI product team to add it back to the product:
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/9378456-when-will-the-kpi-red-yellow-green-indicators-be-a

Tour of the Power BI Solution Advisor

As a follow-up to my earlier post titled “Nine Realms of Power BI and the Power BI Solution Advisor“,  I’ve recorded this 7 minute tour of the solution advisor tour:

at last count, the tool has been accessed about 650 times.  Thanks for visiting!

I’ll also follow-up here with another tour to step-through the “making of” the tool and a peek inside the design.

Using Power Query “M” To Encode Text As Numbers

I worked through a brain-teaser on a consulting project today that I thought I’d share in case it was useful for someone else in the community.  We needed to convert application user names into an encoded format that would preserve case sensitive comparison.  Here’s the story… A client of mine is using Power BI Desktop to munge data from several different source systems to create analytic reports.

Two-Phase BI  Projects

I’m going to step out of the frame just a moment to make a soapbox speech:  I’m a believer in two-phase Business Intelligence project design.  What that means in a few words is that we rapidly work through a quick design, building a functional pilot or proof-of-concept to produce some reports that demonstrate the capability of the solution.  This gets stakeholders and folks funding the project on-board so we can get the support necessary to schedule and budget the more formal, production-scale long-term business solution.  Part of the negotiation is that we might use self-service BI tools to bend or even break the rules of proper design the first time through.  We agree to learn what we can from this experience, salvage what we can from the first phase project and then we adhere to proper design rules, using what we learned to build a production-ready solution in Phase Two.

Our project is in Phase One and we’re cutting corners all over the place to get reports done and ready to show our stakeholders.  Today I learned that the user login names stored in one of the source systems, which we will use to uniquely identify system users, allows different users to be setup using the same combinations of letters as long as the upper and lower case don’t match.  I had to ask the business user to repeat that and I had heard it right the first time.  If there were two users named “Bob Smith” that were setup with login user names of “BOBSMITH” and “BobSmith”, that was perfectly acceptable per the rules enforced in the application.  No right-minded application developer on this planet or any other should have let that happen but since their dink-wad software produces this data, we have to use it as it is.  In the Phase Two (production-ready) solution we will generate surrogate keys to define uniqueness but in this version, created with Power BI Desktop, I have to figure out how to make the same user name strings, with different upper and lower-case combinations, participate in relationships and serve as table key identifiers.

SNAGHTML1158f792

Wouldn’t it be nice if I could convert each UserName string to a numeric representation of each character (which would be different for each upper or lower case letter).  I knew that to convert each character one-at-a-time, I would need to bust off each string into a list of characters.  Let’s see…  that’s probably done with a List object but what method and where do I find the answer?

It’s Off To The Web, Batman!

Yes, I Googled it (I actually used Bing) and found several good resources.  Most official docs online weren’t very helpful.  I have a paper copy of Ken Puls book where he mentions List.Splitter, which seemed promising.  I have an e-copy of Chris Webb’s book – somewhere – and I know he eats and breathes this kinda stuff.  Running low on options, I came across Reza Rad’s December, 2017 blog post and found Mecca.  Reza has an extensive post about parsing and manipulating lists. He helped me understand the mechanics of the List.Accumulate function, which is really powerful.  Reza provides several good examples of List manipulation; pulling lists apart and putting them back together.  This post didn’t entirely address my scenario but did give me a foundation to figure the rest out on my own.  The post is here. It was educational and sent me in the right direction.  But, the sample code didn’t resolve my issue entirely.  It did, however get me thinking about the problem a certain way and I figured it out.  HOT DANG!

So Here’s The Deal

The first step was to tear each string down into a List object.  At that point, you have a collection of characters to have your way with.

I created a calculated column and entered something like this:

=Text.ToList( [UserName] )

image

If you were to add this column in the query design and then scroll on over to the new column, you’d see that it shows up as a List object placeholder, just all waiting for you to click the magic link that navigates to the list of all the characters in the column string.

image

We don’t want to do this.

Beep Beep Beep…. Backing up The Bus

Removing the navigation step and looking at the column of List object placeholders…  I want to modify the M code for this step to do the following:

  1. Parse the items in the list (each character in the UserName field)
  2. For each character, convert it to a number
  3. Iterate through the list and concatenate the numbers into a new string of numerals

To enumerate over the elements of a list and put the list members back into some kind of presentable package (like a single string or a number), we can use the Accumulate method.

The Accumulator is a little machine with a crank handle on the side.  Every turn of the handle spits out on of the element values, using the current variable.  You can do whatever you want with the object in the current variable, but if you want to put it back into the machine for next turn, you should combine it with the state variable, which represents the previous value (when the handle was cranked the last time).

Here’s my final desired result:

image

In a nutshell, List.Accumulate contains two internal variables that can be used to iterate over the elements of a list (sort of like an array) and assemble a new value.

The state variable holds the temporary value that you can build on each each iteration, and the current variable represents the value of the current element.  With an example, this will be clear.

The final code takes the output from “Text.ToList” and builds a List object from the characters in the UserName field on that row.

Next, List.Accumulate iterates over each character where my code uses “Character.ToNumber” over the current character to convert it to numeric form.

Adding this custom column…

image

…generates this M code in the query:

= Table.AddColumn(#”Reordered Columns”, “Encoded UserName 1”, each List.Accumulate(
Text.ToList([UserName])
, “”
, (state, current)=>
state
&
Number.ToText(
Character.ToNumber(current), “000”
)
)

Just like magic, now I have a unique numeric column representing the distinct upper and lower-case characters in these strings, that I can reliably be used as a key and join operator.

Bad Data Happens

As I said earlier, in a solution where we can manage the data governance rules, perhaps we could prevent these mixed-case user names from being created.  However, in this project, they did and we needed to use them.