Web API Data Sources with Power Query and Scheduling Data Refresh in the Power BI Service

Using a Web API is a convenient way to expose and consume data over an Internet connection.  Exercising some essential design patterns, understanding and working with the Power Query Formula Firewall is essential if you need to consume Web API data with Power Query and schedule data refresh with the Power BI Service.

Having recently worked-through numerous issues with API data feeds and deployed report configurations, I’ve learned a few important best practices and caveats – at least for some common use cases.  In one example, we have a client who expose their software-as-a-service (SaaS) customer data through several web API endpoints.  Each SaaS customer has a unique security key which they can use with Power BI, Power Query or Excel and other tools to create reporting solutions.  If we need a list of available products, it is a simple matter to create a long URL string consisting of the web address for the  endpoint, security key and other parameters; an then just pass this to Power Query as a web data source.  However, it’s not quite that easy for non-trivial reporting scenarios. 

Thanks to Jamie Mikami from CSG Pro for helping me with the Azure function code for demonstrating this with demo data.  Thanks also to Chris Webb who has meticulously covered several facets of API data sources in great detail on his blog, making this process much easier.

Some web APIs have a database query or other logic hard-wired to each individual endpoint.  The endpoint I am demonstrating allows a stored procedure name and filter to be passed as parameters, which allows one endpoint to run any query in the database that is allowed by the developer or admin.  The following information was setup for this demo:

  • Here is the endpoint exposed through Azure Function App services:  https://intellibizwebapi.azurewebsites.net/api/ExecuteSQL
  • The security API key is required to authorize execution (*this key has since been retired):
    5FH6AcKwEDTChbOidYEpkveqlncgv/TOvqCAJ6jP2jX0fJUSmCVN0A==
  • The name of the stored procedure is passed in the “name” parameter like this: uspOnlineSalesByYear
  • An optional filter parameter may be passed to the stored procedure to filter the results.

The typical approach is to concatenating all the elements into a single string.  If we were to enter this address into a browser window:

https://intellibizwebapi.azurewebsites.net/api/ExecuteSQL?code=5FH6AcKwEDTChbOidYEpkveqlncgv/TOvqCAJ6jP2jX0fJUSmCVN0A==&name=uspOnlineSalesByYear&filter=2008

…a JSON document is returned beginning with the following data:

[{“SalesKey”:4,”DateKey”:”2008-01-13T00:00:00″,”channelKey”:2,”StoreKey”:306,”ProductKey”:1429,”PromotionKey”:10,”CurrencyKey”:1,”UnitCost”:132.9000,”UnitPrice”:289.0000,”SalesQuantity”:8,”ReturnQuantity”:0,”ReturnAmount”:0.0000,”DiscountQuantity”:1,”DiscountAmount”:57.8000,”TotalCost”:1063.2000,”SalesAmount”:2254.2000,”ETLLoadID”:1,”LoadDate”:”2010-01-01T00:00:00″,”UpdateDate”:”2010-01-01T00:00:00″},{“SalesKey”:5,

…and if we paste the same URL into the address dialog for a new web data source in Power Query, a complete table is returned.

image

Good so far, right?  But, here’s here’s the problem.  If we were to use a single API call in this manner, the Power BI service may, under certain conditions, allow the data source to be refreshed but you cannot modify the query string parameters in this way for the service to trust the web API connection so it can be refreshed.  Now for a more sophisticated and more realistic example.

To minimize the data volume per call and load data incrementally, web API data is often paged or filtered using a date range or category of some kind.  In this example, one call returns a list of years for which there are orders.  With that, orders can be loaded for each year.  An actual production scenario may be more sophisticated but this demonstrates the design pattern.

The first query – the outer query – returns one row per year.  Then we create another query that executes a stored procedure requiring a Year parameter, that returns all the Order records for that year. 

SNAGHTML1e42f4b4

That query, shown here, is converted into a function by adding the Year parameter.

image

In the typical design pattern, a custom column is added which invokes the custom function, passing the YEAR column value.

I deploy a copy of this report file to my Power BI tenant and then try to schedule data refresh.  Here’s the result:

image

The error reads: “You can’t schedule refresh for this dataset because the following data sources currently don’t support refresh…”

The Power Query Formula Firewall prevents the queries from running because they don’t meet requirements to be trusted according to the rules of the the formula firewall and the “fast combine” feature.  Each query must be granted permission to run.  The default permission set for web sources is “Anonymous” which simply means that no credentials are needed.  The developer simply needs to agree to let the query run.

Each query being combined must share a common address or connection string, with a compatible set of privacy level settings.

image

The formula firewall has a problem with us concatenating the endpoint, code and parameters into one humungous web address string.  The reasons and details are less important but separating the address and other elements and letting Power Query manage them as separate objects can resolve this issue.

Here’s the refactored M code for the outer query/function.  This is the patterned I’m using for all web API queries.  Note that the “BaseUri” is a scalar text type variable, and the other web query parameters are elements of a record stored in a variable named “Query”.  These two variables are passed as arguments to the Web.Contents method:

(Year as number) =>
let
     BaseUri     = “https://intellibizwebapi.azurewebsites.net/api/ExecuteSQL”,
     QueryRecord =
         [
             Query=
             [
                 code   = “abFbalkeuCiozdne7PeMG0bZWAZGj65uJ3zLsYoB8zLfisrJo6gv2/Fvw==”,
                 name   = “uspOnlineSalesByYear”,
                 filter = Number.ToText( Year )
             ]
         ],
     Source = Json.Document(
         Web.Contents( BaseUri, QueryRecord )
     ),

By letting the Web.Contents method work it’s magic and by conforming to the other requirements I mentioned, the Power BI service formula firewall will trust the source and allow this report to be scheduled for data refresh.

As I mentioned earlier, Chris Webb has covered a number of nuances related to this method on his blog.  To understand it deeply, I suggest reading about them here.

Mastering Power Query in a Day – Full-day training in Seattle

Following the Power BI World Tour, Seattle event on Oct 30, please join me for a full-day of deep learning.  That’s right… it’s on Oct 31st so put on your Wonder Woman or Captain America costume and get ready to exercise your super powers with Power Query and Power BI!  You will learn to master Power Query extensively from Beginner to Advanced.  The other session taught at the same time by Brian Grant is “Power BI: Enhance Your Data Model with DAX” but ya gotta pick one.  You can learn more about the Power BI World Tour and the Academy by following these events on Twitter and LinkedIn using the links at the bottom of this post, or search these hashtags:
#PowerBIUG | #PowerBI | @pbiusergroup | #PowerBIUGAcademy | #PBIWorldTour

Registration and course description:

Academy – Mastering Power Query in a Day (Seattle, WA)

The foundations of a Business Intelligence solution are data transformations, data wrangling, data cleansing and ETL. A well-crafted Power BI project rests on Power Query and the queries that define the data model, calculations and report visuals. This full-day session will teach you how to lay the foundation for a Power BI solution with simple and advanced Power Query techniques.

Learn from Paul Turley, ten-year Microsoft Data Platform MVP and veteran BI Solution Architect. You will learn best practice design patterns, tricks, shortcuts and proven techniques to improve your skills and add immediate value to your projects. Power Query is everywhere – and growing.

The skills and techniques taught in this workshop apply to Power BI Desktop, the “Get Data” feature in Excel 2016+, SQL Server Analysis Services 2017+ (SSAS), Azure Analysis Services (AAS) and Data Flows in the Power BI Common Data Service (CDS). You will learn through exercises and instructor-led hands-on demos. Bring your laptop with the latest version of Power BI Desktop installed. The rest will be provided. We will cover material from basics through advanced. Each exercise is separate so you can absorb only what you need to learn, based on your prior experience, needs and skill level.

Power Query Basics
Quick tour of the Power Query interface & essentials
Creating and managing queries
Adding and editing steps
Recovery and project management
Essential best practices
Managing data sources
Working with folder paths, web URIs & database connections
Referencing & Duplicating queries
Consolidating queries, building base queries & dependency chains
Loading queries into data model tables
Basic error handling & debugging

Data Sources & Structures
Flat CSV files
Irregular text files (headings & totals)
JSON (simple)
JSON (complex, with nested & ragged hierarchies)
SQL server
Excel (single sheet/table, multiple sheets/tables)
Folders & file collections
Web pages a page tables
Web APIs & web service endpoints

Essential Query Techniques
Managing data types
Applying correct naming conventions
Working with Date & Time values
Splitting & formatting columns
De-duplicating & grouping
Pivot, Unpivot & Transpose
Custom columns & expression basics
Extracting tables from a data sources to supporting essential modeling for Power BI report design:

  • Fact tables
  • Dimension tables
  • Bridge tables
  • Slicer & calculation-driver tables
  • Role-playing dimensions

Advanced Power Query Techniques
Working with M: The Data Mashup language
M function essentials
Prioritized learning (what’s most important)
Using & managing parameters
Using the #shared object for internal documentation, examples & code syntax
Understanding M objects (values, tables, lists & records)
Number, Date, Time & Text manipulation M functions
Create a Date lookup/dimension table using M & Power Query
Create a Time series lookup/dimension table using M & Power Query

  • Why do I need a Date dimension in Power BI?
  • Standard date parts & hierarchies
  • Columns to support time-intelligence calculations
  • Working with fiscal & special-purpose calendars (e.g. 4-4-5, ISO)
  • Working with query functions
  • Parameterized queries, API endpoints & user-defined functions

Putting it Together
Examples:

  • Queries to support data model construction
  • Queries used to support report visuals
  • Deploy a report, configure the on-premises gateway
  • Use query parameters to schedule refresh in a deployed Power BI solution

Follow the events at:

https://www.linkedin.com/groups/13580234

https://twitter.com/pbiusergroup?lang=en

How to Configure the Power BI Gateway to use Dataset Connection Parameters

A service provider or vendor might want to publish multiple copies of a report that should connect to different database servers or databases.  In a true multitenant service solution, we would have a singe database with row-level user mapping tables that filter data by the logged in user.  True multitenant solutions require quite a lot of planning and development work to implement.  In smaller-scale or interim solutions, copies of a report can be deployed to different workspaces and then queries can be parameterized to use different database connections.

In this post, I’ll demonstrate deploying and configuring such a solution where the server name and database name have been parameterized and setup to use the on-premises gateway to connect and refresh data.  I’ll also setup scheduled refresh.  The full video walk-through is below but I’ll do a quick review to set the stage.

This is the Power Query Editor in Power BI Desktop.  I have two parameters that are used to specify the ServerName and DatabaseName for each SQL Server query:

Power BI Gateway with Parameters (Time 0_00_57;24)

Once deployed to a workspace in the service, the gateway must be configured with a data source for every possible server name and database combination.  In my example, I can connect to my local server using the NetBIOS name, IP address or LocalHost.  These all are acceptable methods but a data source must be added to the gateway configuration for each so the that the connection strings match exactly.  Remember that the connection is from the on-prem gateway to the database server so names like LocalHost or an internal IP address will work just fine.  In my example, I’m using the IP address of my local loopback adaptor on the local machine to connect to a local instance of SQL Server over the TCP connection.

Power BI Gateway with Parameters (Time 0_04_31;15)

In the workspace, the dataset is bound to the gateway.  Click the ellipsis and choose Settings.

Power BI Gateway with Parameters (Time 0_02_44;22)

To bind the gateway to the dataset, click to select the radio button next to the gateway.  This flips the switch titled “Use a data gateway”.  Apply the setting and then you can refresh the data or schedule refresh.

Power BI Gateway with Parameters (Time 0_05_47;00)Power BI Gateway with Parameters (Time 0_06_09;27)Finally, the parameters can be updated right here in the dataset settings.

Power BI Gateway with Parameters (Time 0_06_46;21)

 

Video Demonstration

 

Power BI for Grownups

The message from the Microsoft Business Applications Summit this week was very clear: Power BI is growing up.

We have known for a while that Power BI is a great front-end tool for enterprise-scale tabular and multidimensional models.  Power BI Premium will now allow almost limitless data scale using extremely large datasets by leveraging composite models that employ both DirectQuery and VertiPaq cached mode tables.  This feature shatters the previous barriers where data models could only support either DirectQuery or cached mode (imported data).  Additionally, Microsoft publicly unveiled plans to add IT developer centric features to Power BI Desktop for managing large models, version control and application development lifecycle.

These announcements leave many open questions about how Microsoft will continue to support self-service and enterprise customers with one tool and about the long-term future of SSAS and Visual Studio-based SSDT. At this point, none of these tools are on the chopping block, but it is clear that James Phillips and his team have significant momentum and are not slowing down to wait for other products to catch-up.

In this interview with Christian Wade, he describes the benefits of this new capability.  Christian is Group Program Manager on the Power BI and Analysis Services product team with focus on data model development and scalability features.

Composite Models & Aggregations

In the opening keynote and again in his sessions, Christian demonstrated Power BI reports on the taxi driver activity database with over a trillion rows of raw data.  The larger dataset was in a Spark cluster, accessed using DirectQuery.  Aggregated tables were stored in the in-memory model using the new composite model feature.  As the data was explored in report visuals, the Power BI engine would seamlessly switch from tabular in-memory aggregate tables to DirectQuery source data in order to return low-level details.  Composite models will allow mashing-up imported database and file-based data with an DirectQuery.

There are limits and complexities with these new features.  You cannot mashup imported tables in a Power BI model based in a direct connection to SSAS, but enterprise-scale features in Power BI arguably may not steer a solution architect to select SSAS over Power BI for serious data modeling.  With incremental data refresh, large model support, row-level security and many other “big kid” features, Power BI might be a preferable choice.  I’m not ready to rule-out Analysis Services as the better option for most enterprise solutions – at least not in the near future, but Power BI is definitely heading in that direction.

Power BI Desktop Features for Developers

News Flash: Power BI Desktop will be the primary tool for serious, enterprise data model development.  A bunch of features are in the works and will arrive soon for preview.  The obvious question is what will happen to SSDT for Visual Studio and the answer is that it’s not going away but the product team is putting their energy into enhancing Power BI Desktop for now.  It will likely have some kind of “Beginner/Advanced” switch to simplify things for the novice user and isolate advanced or complicated, developer-centric features.

Here are a few screen captures of the forthcoming features:

Schema Compare & Model Integration

Like the BISM Normalizer tool created by Christian Wade, there will be a fully-supported compare and merge tool baked into Desktop.  The early build is branded with components from MAQ Software.

image

Relationship Diagram Designer, Properties Pane and Application Lifecycle Management Features

A new-and-improved diagram designer is imminent.  It will allow large models to be more effectively managed and displayed.  You will have the ability to save individual views of the diagram with selected, subject-area tables.

image

Like in Visual Studio, properties can be set and managed in a Properties sheet window.  Support for standard SSAS features like display folders is coming and can be managed here.

image

Business Applications Release Notes

With the velocity of features and updates, a comprehensive set of release notes are available here.  This document will be updated frequently with the revised roadmap, feature history and announcements.

APIS and Powershell CmdLets for Power BI Administration

At the MVP Summit in March Kay Unkroth promised API endpoints and Powershell CmdLets for managing all aspects of Power BI.  He even did a dance and sang a song to make the announcement memorable.  This week, the product team delivered what they promised, with the announcement official here.

The following features and capabilities are on the delivery short list coming in the next 2-3 months:

Power BI Service

The new, “modern workspace” eventually replacing current workspaces integrated with Office 365 groups.  The new app workspace will no longer be bound to an Office 365 Group. The feature will be optional at first, but will replace current workspaces soon.

Now, users can be: Member, Admin, Contributor

Can add groups to workspace

Premium capacity tenants can have nodes in different regions

Admins will have visibility and access to all objects and resources

Microsoft Business Applications Summit

Keynotes and Featured Sessions from the Microsoft Business Applications Summit are here.

SQL, M or Dax? – part 2

[ Related posts in this series: SQL, M or DAX: When Does it Matter? SQL, M or Dax? – part 2 SQL or M? – SSAS Partitions Using SQL Server Table-Valued Functions (UDFs) SQL or M? – SSAS Partitions in Power Query/M ]

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.

 

 

 

 

 

 

 

 

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

Power BI Global Hackathon Contest Results

The results of last month’s Power BI Global Hackathon are in! The Hackathon was facilitated by our our PUG here in Portland with the goal of welcoming global contenders in subsequent contest. Five teams entered the contest using publically-available data to visualize and tell data stories using our favorite data analysis platform.  Congratulations to Xinyu Zheng and Ron Barrett for winning the challenge with their entry, analyzing Yelp restaurant star ratings.  These were all great entries and you can view the contest results in the Power BI report below.

image  image  image

Here are the published projects that were entered in the Hackathon:

Xinyu and Ron analyzed ratings from nearly 1200 restaurant Pittsburgh, Phoenix and Las Vegas.  Results compare ratings and reviews by restaurant and food categories, sentiment and key phrases in the review comments

image

I loved the creativity of this solution from Jeremy Black and Kirill Perian who analyzed alcohol sales statistics using infographics and bookmarks to switch out visuals on the same page.  The presentation concludes on the last page of the report with an auto-advancing variation of “100 Bottles of Beer on The Wall”.  Nice touch.

image

I’m admittedly a bit biased because this was my design, with a lot of help from Brian, Ron and Greg.  We used a series of tables to prompt a user for Power BI solution business requirements and recommend fitting solution architectures and components.  We pushed some practical and technical limits in our project and I’ll write a separate post about it.

image

This entry from Ron Ellis Gaut is a nice, clean orchestration of county health data, measuring health and comparing personal well-being and program efficacy.

image

The entry from Daniel Claborne emphasizes machine learning predictions performed with R Script, commonly used in data science.  He actually includes the annotated code and explains the technique and the approach using training and prediction data sets.

image

The Portland Power BI User Group was one of the first and continues to be one of the most active in the international community.  We meet on the 4th Wednesday evening every month in Beaverton, Oregon. Today there are many active PUGs all over the world.