Getting Started in Power BI Chris Miller Tail

Getting Started in Power BI Chris Miller, Tail Wind Informatics

A Closer Look at the Matrix

Power BI Desktop Features/Limitations Comparability Matrix Feature MD (Import) MD (Live) REL (Import) REL (Live) TAB (Import) TAB (Live) Measure Creation Yes No Yes Limited DAX Compatibility Yes Large Datasets No Yes Small Datasets Yes Yes Yes Maximum Dataset Size 1 GB Server Resource Limits IT Governance Poor Good Poor Poor Good Data Silo Proliferation Yes No Yes Yes No Full MD Feature Support No No NA NA Full Tabular Feature Support NA NA No Yes Model Readability Poor Good Poor Good Model Ease of Use Poor Good Poor Good Model Maintenance Poor Good Poor Good Import Only No Import Only Import/ Live (Relational Databases Only) Import Only No (Multiple data sources can be used to create the server tabular model) Multiple Data Sources (data mashup)

POWER BI ARCHITECTURE

What Skills Do I Need?

Data Warehousing Build a Data Warehouses convert unwieldy data from multiple sources into an easily understandable format that is both user friendly and consumable with little or no modification. Data Warehouses simplify Tabular and Power BI Desktop model development by allowing developers to duplicate the design of the Data Warehouse in their models. This duplication promotes reuse and reduces the need to revalidate base data due to structural differences between source data and Tabular/Power BI Desktop models.

Data Warehousing Tips Use a star schema and not a snowflake design. Create conformed dimensions that can reused by all of your fact tables. Create at least one fact table for each subject area and choose the right granularity. Don’t take a one size fits all approach. Create a reporting schema with presentation tool ready views. Use date-based integer keys for your Date Dimension and add as many columns as needed to describe your dates in different ways. Create selectors for dates, months, quarters, and years to allow the creation of dynamic reports that change data when dates, months, quarters, and years change.

Date Dimension Day, Month, and Year Selectors

Learn Basic SQL You’ll be writing a lot of queries against your Data Warehouse to pull data into your Tabular and Power BI Desktop models. Most of the SQL queries you write won’t be too complex as the heavy lifting has already been done getting all of the source data into the Data Warehouse.

Master DAX is a complex yet robust development language. You’ll need to become fluent in DAX to get the most out of your Tabular and Power BI Desktop models. The good news is there are lot of great, free resources on the Web to get you started. DON’T GET FRUSTRATED! You can learn the basics of DAX fairly easily, but mastering DAX will take time and effort, and no one, not even Collie, Ferrari, and Russo (icons in Microsoft BI) have done it yet. Once you get experienced in DAX, you may grow to appreciate DAX’s flexibility in solving complex analytic and reporting challenges.

DAX: Base Measures When you load your data warehouse fact table measures into Power BI Desktop or SSAS Tabular for the first time, neither application recognizes these measures as aggregates. As a result, these data values can’t be used in most DAX expressions. So it’s necessary to convert your data warehouse measures into aggregable measures in your Power BI Desktop or SSAS Tabular data model. In most cases, you do this using the SUM, MIN, MAX, AVERAGE, FIRSTDATE, and LASTDATE DAX functions for numbers and dates, and the FIRSTNONBLANK and LASTNONBLANK DAX functions for text values. You’ll use base measures to create all other types of measures that have implicit or explicit filters built into them. For example, to get Total Sales Amt, you build off the Sales Amt measure, but apply the ALL function on the Online Sales table telling the calculation to use all values in the Online Sales table and to disregard any filters that are contrary to this explicit filter.

DAX: Time Intelligence Functions • Time intelligence functions in DAX are fairly straightforward, but they don’t work with complex fiscal calendars and you can’t use time intelligence functions with live connections to relational databases. • The four main functions you’ll work with are TOTALYTD, TOTALQTD, TOTALMTD, and SAMEPERIODLASTYEAR. • When time intelligence measures are created with no date context, the values of these measures will be blank. • Add date context as in the first four examples above…

DAX: Special Time Intelligence: PREV WK

DAX: Special Time Intelligence: ROLL 12

DAX: Special Time Intelligence: CAGR

DAX: Dynamic Reporting Measures First, create a Measure table that lists all of the measures that you want to use for dynamic measure selections. Note that these measures can be used as both stand-alone measures and as the base measures for the dynamic reporting measures that you’ll be creating. Next, create a new measure called Selected Measure which defaults to the lowest measure id in the Measure table if nothing is selected. By always setting this value to the lowest selected minimum id value, multiple selections are prevented. When a value is selected for Measure Name from the Measure table as a filter in your Power BI reports, you’ll get the correct values for any dynamic measures that you’ve created and connected to the Selected Measure measure (see next slide).

DAX: Dynamic Reporting Measures

DAX In Depth Function Review: SUMMARIZE() and SUMX() You’ve been asked by the business to provide custom sales totals for last week. You determine that a new measure called Prev Wk Custom Sales is needed. You decide to first use the SUMMARIZE() function to return an initial data set that totals custom sales for each day of the previous week and then to use the SUMX() function to aggregate the summarized data set into a single value. SUMMARIZE() For this request, custom sales data needs to be totaled for the previous week in the context of any selected date. We’ll use the SUMMARIZE() function to accomplish this. The SUMMARIZE() function usually takes a minimum of four arguments: SUMMARIZE( <TABLE OR TABLE EXPRESSION> , <GROUP BY COLUMN> , <RESULT COLUMN NAME> , <EXPRESSION> ) The first argument to the SUMMARIZE() function is a table or table expression. In this example, we only want to summarize the previous week of data, so we’ll use the DATESBETWEEN function to return a table of seven consecutive dates, starting with the first day of the previous week and ending with the last day of the previous week in the context of the currently selected date. The DATESBETWEEN function takes three arguments: A reference to a date/time table column to be evaluated, a starting date, and an ending date. In this example, we use GL Invoice Date[Date] as the reference date, GL Invoice Date[First Day of Prev Wk] as the start date, and GL Invoice Date[Last Day of Prev Wk] as the end date. Notice that we also wrap the start and end dates in the LASTDATE() function. We do this because each day in any given week has the same start and end days for the previous week. Therefore each GL Invoice Date[First Day of Prev Wk] and GL Invoice Date[Last Day of Prev Wk] is listed seven times in the GL Invoice Date table. The LASTDATE() function like the FIRSTDATE(), MIN(), and MAX() functions aggregate data to return a single value. In this example, using any of these functions returns a single value representing the first or last day of the previous week in the context of the currently selected date. SUMMARIZE( DATESBETWEEN('GL Invoice Date'[Date], LASTDATE('GL Invoice Date'[First Day of Prev Wk]), LASTDATE('GL Invoice Date'[Last Day of Prev Wk])) , <GROUP BY COLUMN> , <RESULT COLUMN NAME> , <EXPRESSION> ) The second argument to the SUMMARIZE() function is a group by column from the first argument to the SUMMARIZE() function against which the results of the expression argument of the SUMMARIZE() function will be aggregated. In this example, the first argument to the SUMMARIZE() function is a single column table returned by the DATESBETWEEN function, so we’ll use the GL Invoice Date[Date] column as our group by column. This group by column will serve as the unique row identifier for the data set returned by the SUMMARIZE() function. Please note that the SUMMARIZE() function allows multiple group by arguments. SUMMARIZE( DATESBETWEEN('GL Invoice Date'[Date], LASTDATE('GL Invoice Date'[First Day of Prev Wk]), LASTDATE('GL Invoice Date'[Last Day of Prev Wk])) , 'GL Invoice Date'[Date] , <RESULT COLUMN NAME> , <EXPRESSION> ) The third argument to the SUMMARIZE() function is the name to call the data set column that will store the values aggregated by the expression in the last argument to the SUMMARIZE() function. In this example, we name the data set column, #Prev Wk Custom Sales. SUMMARIZE( DATESBETWEEN('GL Invoice Date'[Date], LASTDATE('GL Invoice Date'[First Day of Prev Wk]), LASTDATE('GL Invoice Date'[Last Day of Prev Wk])) , 'GL Invoice Date'[Date] , "#Prev Wk Custom Sales“ , <EXPRESSION> )

DAX In Depth Function Review: SUMMARIZE() and SUMX() The final argument to the SUMMARIZE() function is the expression that will be aggregated against the group by column argument(s) to the SUMMARIZE() function. In this example, we calculate the custom sales for each of the seven days of the previous week. Notice that we use the CALCULATE() function because we need to add a filter to the calculation to ensure that the calculation is done only for the seven days of the previous week in the context of the currently selected date. To accomplish this in our example, we use the ALLEXCEPT() function to ensure that when evaluating the GL Invoice Date table all context filters are removed from the table except for any filters on the GL Invoice Date[Date] column. SUMMARIZE( DATESBETWEEN('GL Invoice Date'[Date], LASTDATE('GL Invoice Date'[First Day of Prev Wk]), LASTDATE('GL Invoice Date'[Last Day of Prev Wk])) , 'GL Invoice Date'[Date] , "#Prev Wk Custom Sales“ , CALCULATE([Custom Sales], ALLEXCEPT('GL Invoice Date', 'GL Invoice Date'[Date])) ) SUMX() Next we need to take the data set returned by the SUMMARIZE() function and aggregate it into a single value. We do this by using the SUMX() function. The SUMX() function takes two arguments: A table or table expression and an expression that contains one or more measures or columns present in the first argument to the SUMX() function. SUMX( <TABLE OR TABLE EXPRESSION> , <EXPRESSION> ) The first argument to the SUMX() function is a table or table expression. In this example, the first argument to the SUMX() function is our completed SUMMARIZE() function. SUMX( SUMMARIZE( DATESBETWEEN('GL Invoice Date'[Date], LASTDATE('GL Invoice Date'[First Day of Prev Wk]), LASTDATE('GL Invoice Date'[Last Day of Prev Wk])) , 'GL Invoice Date'[Date] , "#Prev Wk Custom Sales“ , CALCULATE([Custom Sales], ALLEXCEPT('GL Invoice Date', 'GL Invoice Date'[Date])) ) , <EXPRESSION> ) The second argument to the SUMX() function is an expression containing one or more measures or columns present in the first argument to the SUMX() function. This expression will be aggregated into a single value. In this example, we want to aggregate or sum the #Prev Wk Custom Sales measure. SUMX( ) SUMMARIZE( DATESBETWEEN('GL Invoice Date'[Date], LASTDATE('GL Invoice Date'[First Day of Prev Wk]), LASTDATE('GL Invoice Date'[Last Day of Prev Wk])) , 'GL Invoice Date'[Date] , "#Prev Wk Custom Sales“ , CALCULATE([Custom Sales], ALLEXCEPT('GL Invoice Date', 'GL Invoice Date'[Date])) ) , [#Prev Wk Custom Sales] Our final measure looks like this: Prev Wk Custom Sales: = SUMX( SUMMARIZE( DATESBETWEEN('GL Invoice Date'[Date], LASTDATE('GL Invoice Date'[First Day of Prev Wk]), LASTDATE('GL Invoice Date'[Last Day of Prev Wk])) , 'GL Invoice Date'[Date] , "#Prev Wk Custom Sales“ , CALCULATE([Custom Sales], ALLEXCEPT('GL Invoice Date', 'GL Invoice Date'[Date])) ) , [#Prev Wk Custom Sales] )

Self-Service BI with Power BI Desktop has a lot of great features to help you build meaningful dashboards and reports that you and your colleagues will come to rely on. Take some time to explore and learn these features by watching demos on You. Tube and then try the features out for yourself using the sample databases and reports that we’ve created for you. And remember, the key to success in Business Intelligence is to Empower your users, not to Overwhelm them.

Mapping in Power BI, a Cautionary Tale In Power BI, when you map just the city name as your location, Power BI may give us the following unexpected results even when we filter the city by country. This occurs because the same names are used to name different cities around the world. The classic example is Paris, Texas and Paris, France. The best practice to correct/workaround this behavior is to fully qualify the city name. For example, New Richmond, where I live, becomes NEW RICHMOND, WISCONSIN, USA. But applying the best practice may yield unexpected results. As you can see the map is blank. The issue is occurring because in our model we categorize City as “City”, which may sound a little strange. And until very recently, Bing was able to figure out that NEW RICHMOND, WISCONSIN, USA, for example, was a city (you know, because we categorized it as a City). But a fully qualified city name is no longer recognized as a city even if it’s categorized as a City in the model. Bing now recognizes fully qualified cities as Places and not Cities. So when we change the category of our City column in the model from City to Place…

Mapping in Power BI, a Cautionary Tale

Power BI Demo

Demo Instructions 1. Restore the Contoso. DW database backup (Contoso. DW. BAK) to your SQL Server 2016 server. Note that we have significantly altered the Microsoft Contoso dataset to meet our needs, so don’t use the version of this database that is downloadable from Microsoft. 2. Open the Contoso SSDT project in either Visual Studio 2015, 2017, or SSDT 2016. Note that you have to install SSDT as an add-on to both VS 2015 and VS 2017 if you are using these tools. 3. Choose existing connections and change the Contoso DW connection to point to the SQL Server 2016 server to which you restored the Contoso. DW database. Note that we use the SSAS 2016 Tabular Server Service Account to run our SQL Server database queries, so we granted this account read access to the Contoso. DW database. 4. Change the Impersonation setting in the Contoso DW connection to use a specific user who has read access to the Contoso. DW database. 5. Open project properties and point the Deployment Server to your SSAS 2016 Tabular Server. 6. Save, process, and deploy the project model to your SSAS 2016 Tabular Server. 7. Open the Contoso Power BI Report in the latest version of Power BI Desktop and change your connection settings to point to your SSAS 2016 Tabular server. 8. Refresh the report and enjoy!

SIMPLE HOW-TO’S

Tabular Model How To: Marking Date Tables j Right-click on the Date. Key column in the your Date table and select Hide from Client Tools. Key or ID columns can be used to create relationships between tables in tabular models and in most cases can be hidden for better readability. k Date tables must be marked as Date tables for DAX Time Intelligence functions to work properly. Select Date from the Table menu and then select Mark as Date Table. l Select the Date column as the unique identifier for the table and then select OK

Tabular Model How To: Creating Hierarchies j From diagram view, select the columns you want in the hierarchy in the order they should appear, rightclick on the selection, and then select Create Hierarchy k Rename the hierarchy and if needed rearrange the members of the hierarchy by dragging a member up or down in the hierarchy

Tabular Model How To: KPIs

Tabular Model How To: Row-Level Security (1) First, add a user name column to the dimension table that you’ll be using to restrict access to the connected fact table records. In this example, I’ll be restricting access to the State of Texas, so I’ve tagged all Texas records with my user id.

Tabular Model How To: Row-Level Security (2) Next, create a role, add members to the role, and restrict access to dimension records. In this example, I’ve restricted access to the Geography dimension by user name. Note that the USERNAME() function is only executed when a user logins to the model.

Tabular Model How To: Row-Level Security (3) Now test the role by analyzing the changes in Excel and connecting to the role that you want to test. When excel opens, you’ll only be able to access the records that you are authorized to access using this role. Notice that you can set what role you connect to in the connection string, so a user can connect to the same Tabular Model with different access levels by explicitly connecting to different roles.

The More You Know: Domestic and International Geographic Equivalency Feature US Postal Code CA Dissemination Area US Zip 3 CA FSA (Zip 3) Standard Map Feature Yes No No Yes Geographic Polygon Yes No Yes Yes Equivalent Feature in US/CA CA Dissemination Area X US Postal Code CA FSA (Zip 3) US Zip 3 1) CA Dissemination Areas contain multiple postal codes, but Canadian postal codes are more akin to a street address and cannot be mapped into a geographic polygon like US zip codes. Postal Code 1 Postal Code 2 Postal Code 3 Postal Code 4 3) Once we create this equivalence, we leverage it to ensure that international mapping and demographic comparisons are always “apples to apples” comparisons between equivalent map features and geographic polygons. Zip 3 Combined Polygons for Both US and CA CA Dissemination Area 2) Since CA Dissemination Area polygons are equivalent to US zip code polygons, we can choose any single postal code in a CA Dissemination Area to be a reference postal code for the Dissemination Area. But we only create this equivalence in our geography dimension in which postal codes and postal code polygons are our lowest level of granularity, and a specific address is never required. Postal Code 1 Postal Code 3 Feature to Postal Code Relationship 1: 1 1 to Many Postal Code 2 CA Dissemination Area US Zip Code CA Dissemination Area Polygon US Zip Code Polygon Postal Code 4 Population by Postal Code for Both US and CA Cities

Useful Resources Blogs Power Pivot Pro Marco Russo Alberto Ferrari SQLBI DAX Patterns How To Tabular Model Row-Level Security Power BI Desktop Row-Level Security Pareto Charts Mapping Resources Geo Names Map Shaper Report and Dashboard Ideas Power BI Partner Showcase Power BI R Script Showcase Software Downloads Power BI Desktop QGIS R Installation Package