Brian Larson Superior Consulting Services LLC Powering Up
Brian Larson Superior Consulting Services, LLC Powering Up with Power BI
THANK YOU, SPONSORS!
Brian Larson • 16 years experience with Microsoft Data Visualization Tools • Contributed to the initial codebase of SQL Server Reporting Service • Regional and National Speaker • Author on Business Intelligence and Data Visualization topics for Mc. Graw-Hill Professional
Course Outline Power BI Desktop Direct Connect with Power BI Desktop Tabular Data Model Building Basic Visualizations Measures and DAX Expressions Data Import and Transformations Advanced Interactivity Power BI Report Server On-premises Report Viewer Power BI Service Navigation Subscriptions Features Refreshing Data with Gateway Workspaces Administration Content Packs and Apps Power BI Mobile App Features
Power BI Architecture
Power BI Architecture Power BI is… a set of tools from Microsoft designed to provide business users with self-service business intelligence capabilities.
Power BI Architecture Power BI Desktop (pbix file) Source Data Query Editor Tabular Data Storage Model Structure Visualizations
Power BI Architecture Power BI Desktop (pbix file) Source Data Query Editor Tabular Data Storage Model Structure Visualizations
Power BI Architecture loy p De Power BI Service De plo y Power BI Report Server
Power BI Connection Types Direct Query SQL Server Relational DB, Azure SQL Database, Azure SQL Data Warehouse, SAP HANA Power BI Model Structure Data T-SQL Queries DAX/MDX Queries Visualizations Model Structure Visualizations Data Model Structure Import All supported Power BI data sources including those listed above. Data Source Direct Query Import SQL Server Analysis Services Components Stored in… Live Connection Works with… Visualizations
Power BI Desktop
Direct Connect with Power BI Desktop Demonstration
Basic Visualizations Demonstration
Data Import and Transformations Demonstration
Tabular Data Model Building Demonstration
Measures and DAX Expressions
1 7 Default Context in a Measure Default Context is the table
1 8 Measures Respond to Filters and Slicers Only Andy Only Guiding Lights Only 2015 Measures respond to the context created by filters and slicers
1 9 Measures Respond to Filters and Slicers Filter 2015 Orders Slice . . . 2015 Woodland Creatures Orders By Sales Person Only 2015 All Orders By Product Type Sales Tax: =SUM('Orders'[Store. Sales]) * 0. 07775 2015 Guiding Lights Orders Slice 2015 Guiding Sales Tax Lights Orders $50, 652. 48 for Andy. . . 2015 Woodland Creatures Sales Tax Orders $490, 457. 43 for Zeb Calculate Measure
2 0 Aggregate in a Calculate Column Does not react to User Filters Calculated Column becomes an Attribute
2 1 Filter Condition in a Measure > $150 Large Order Sales: =CALCULATE([Sales in Dollars], Orders[Store. Sales]>150) 2015 Guiding Large Order Lights Sales Orders {None} for Andy. . All. 2015. . . Orders. . . 2015 Woodland Creatures Orders for Zeb User Slice > $150 User Slice By Sales Person By Product Type Only 2015 User Filter 2015 Woodland Creatures Orders Large Order Sales $4, 766, 105. 40 Measure Filter Calculate Measure
2 2 Same Attribute in Slicer & Measure Filter Guiding Lights Sales: = CALCULATE([Sales in Dollars], 'Products'[Product Type]="Guiding Lights") 2015 Woodland Creatures Orders for Zeb User Slice X . . . Guiding Lights 2015 Woodland Creatures Orders Guiding Lights User Slice . . . 2015 Guiding Lights Orders for Andy. . . X By Sales Person User Filter 2015 Orders By Product Type Only 2015 All Orders 2015 Guiding Lights Sales for Andy $651, 478. 80. . . 2015 Guiding Lights Sales for Zeb $2, 120, 164. 20 Measure Filter Calculate Measure
2 3 Multiple Attributes Filtered in Measure Guiding Lights 2014 Sales: = CALCULATE([Sales in Dollars], 'Products'[Product Type]="Guiding Lights", X X 2015 Woodland Creatures Orders for Zeb User Slice . . . Guiding Lights User Slice 2015 Woodland Creatures Orders By Sales Person User Filter 2015 Orders By Product Type Only 2015 All Orders 2015 Guiding Lights Orders. . . 2015 Guiding Lights Orders for Andy. . . Guiding Lights X X 'Time'[Year]=2014) 2014 Guiding Lights Sales for Andy $703, 915. 80. . . 2014 Guiding Lights Sales for Zeb $2, 115, 498. 00 Measure Filter Calculate Measure
2 4 Overriding a User Filter/Slicer with ALL() Guiding Lights All Years Sales: = CALCULATE([Sales in Dollars], 'Products'[Product Type]="Guiding Lights", X X 2015 Woodland Creatures Orders for Zeb User Slice Guiding Lights Sales for Andy $2, 069, 970. 00 . . . Guiding Lights User Slice 2015 Woodland Creatures Orders By Sales Person User Filter 2015 Orders By Product Type Only 2015 All Orders 2015 Guiding Lights Orders. . . 2015 Guiding Lights Orders for Andy. . . Guiding Lights X X ALL('Time'[Year])) . . . Guiding Lights Sales for Zeb $6, 323, 343. 60 Measure Filter Calculate Measure
2 5 Overriding Filter/Slicer with ALLEXCEPT() All Sales Selected Year: = CALCULATE([Sales in Dollars], ALLEXCEPT('Orders', 'Time'[Year])) 2015 Guiding Lights Orders for Andy. . . X X 2015 Sales $161, 815, 808. 92 . . . Guiding Lights 2015 Woodland Creatures Orders for Zeb User Slice Guiding Lights User Slice 2015 Woodland Creatures Orders By Sales Person User Filter 2015 Orders By Product Type Only 2015 All Orders 2015 Guiding Lights Orders. . . 2015 Sales $161, 815, 808. 92 Measure Filter Calculate Measure
2 6 RELATED() Function =RELATED(Products[Retail Price]) Orders Product Unit Price Product Code Product Retail Price 2 $22. 05 2 Boston, MS Harbor Lighthouse $22. 05 5 $15. 75 3 Cape Hatteras, NC Lighthouse $18. 90 6 $14. 70 4 Portland, ME Lighthouse $25. 20 4 $25. 20 5 Split Rock, MN Lighthouse $15. 75 6 Manitowoc, WI Lighthouse $14. 70
LOOKUPVALUE() Function =LOOKUPVALUE(Products[Retail Price], [Product Code], [Product]) Orders Product Unit Price Product Code Product Retail Price 2 $22. 05 2 Boston, MS Harbor Lighthouse $22. 05 5 $15. 75 3 Cape Hatteras, NC Lighthouse $18. 90 6 $14. 70 4 Portland, ME Lighthouse $25. 20 4 $25. 20 5 Split Rock, MN Lighthouse $15. 75 6 Manitowoc, WI Lighthouse $14. 70 Relationship created on the fly 27 X Product
2 8 SUMX() Iterative Function Calcd Total Sales: =SUMX('Orders', 'Orders'[Unit Price] * 'Orders'[Unit. Sales]) 30. 80 * 4 = 123. 20
2 9 SUMX() Iterative Function Calcd Total Sales: =SUMX('Orders', 'Orders'[Unit Price] * 'Orders'[Unit. Sales]) 30. 80 * 4 = 123. 20 246. 40
3 0 SUMX() Iterative Function Calcd Total Sales: =SUMX('Orders', 'Orders'[Unit Price] * 'Orders'[Unit. Sales]) 30. 80 * 4 = 123. 20 369. 60
3 1 SUMX() Iterative Function Calcd Total Sales: =SUMX('Orders', 'Orders'[Unit Price] * 'Orders'[Unit. Sales]) 29. 40 * 4 = 117. 60 487. 20
3 2 Using the FILTER() Function Total Sales for Medium Priced Items: =SUMX(FILTER('Orders', Orders[Unit Price] >= 20. 00 && Orders[Unit Price] <= 30. 00), 'Orders'[Unit Price] * 'Orders'[Unit. Sales]) 2015 Guiding Lights Orders for Andy. . . Medium Priced Items 2015 Woodland Creatures Orders for Zeb User Slice Medium Priced Items User Slice 2015 Woodland Creatures Orders By Sales Person User Filter 2015 Orders By Product Type Only 2015 All Orders 2015 Guiding Lights Orders. . . Total Sales for Medium Priced Items $236, 048. 40. . . Total Sales for Medium Priced Items $657, 643. 20 Measure Filter Calculate Measure
3 3 Using the FILTER() Function Total Sales for Medium Priced Items: =SUMX(FILTER('Orders', Orders[Unit Price] >= 20. 00 && Orders[Unit Price] <= 30. 00), 'Orders'[Unit Price] * 'Orders'[Unit. Sales]) 2015 Guiding Lights Medium Priced Item Orders for Andy 2015 Guiding Lights Orders for Andy Filtered Rows FILTER(…) New Virtual Table SUMX(…) Iterates through the virtual table calculating the sum of the [Unit Price] * [Unit. Sales]
3 4 Using the DATESYTD() Function YTD Sales: =CALCULATE([Sales in Dollars], DATESYTD('Time'[Date])) 2015 Guiding Lights Orders for. January. . Set of Dates YTD . . . Guiding Lights January 2015 YTD Sales $1, 212, 750. 00. . . 2015 Woodland Creatures December 2015 Orders YTD Sales for Measure $41, 577, 790. 20 User Slice December Filter Calculate Measure Set of Dates YTD User Slice 2015 Woodland Creatures Orders By Month of Year User Filter 2015 Orders By Product Type Only 2015 All Orders 2015 Guiding Lights Orders. . .
3 5 Using the DATESYTD() Function YTD Sales: =CALCULATE([Sales in Dollars], DATESYTD('Time'[Date])) 2015 Guiding Lights Orders for January Filtered Rows DATESYTD(…) New Virtual Table SUM(…) Sum all Sales in the Virtual Table
3 6 Using the DATESYTD() Function YTD Sales: =CALCULATE([Sales in Dollars], DATESYTD('Time'[Date])) 2015 Guiding Lights Orders for January - February 2015 Guiding Lights Orders for February Filtered Rows DATESYTD(…) New Virtual Table SUM(…) Sum all Sales in the Virtual Table
3 7 Using the DATESYTD() Function YTD Sales: =CALCULATE([Sales in Dollars], DATESYTD('Time'[Date])) 2015 Guiding Lights Orders for January - March 2015 Guiding Lights Orders for March Filtered Rows DATESYTD(…) New Virtual Table SUM(…) Sum all Sales in the Virtual Table
3 8 Using the PARALLELPERIOD() Function Prev Qtr Sales: =CALCULATE([Sales in Dollars], PARALLELPERIOD('Time'[Date], -1, QUARTER)) 2015 Guiding Lights Orders for. Quarter 1. . Set of Dates Prev Quarter . . . Guiding Lights Quarter 1 2015 Prev Qtr Sales $3, 534, 379. 80. . . 2015 Woodland Creatures Quarter 4 2015 Orders Prev Qtr Sales for Measure $10, 572, 865. 72 User Slice Quarter 4 Filter Calculate Measure Set of Dates Prev Quarter User Slice 2015 Woodland Creatures Orders By Month of Year User Filter 2015 Orders By Product Type Only 2015 All Orders 2015 Guiding Lights Orders. . .
3 9 Using the PARALLELPERIOD() Function Prev Qtr Sales: =CALCULATE([Sales in Dollars], PARALLELPERIOD('Time'[Date], -1, QUARTER)) 2014 Guiding Lights Orders for Quarter 4 2015 Guiding Lights Orders for Quarter 1 Filtered Rows PARALLELPERIOD(…) New Virtual Table SUM(…) Sum all Sales in the Virtual Table
4 0 Using the PARALLELPERIOD() Function Prev Qtr Sales: =CALCULATE([Sales in Dollars], PARALLELPERIOD('Time'[Date], -1, QUARTER)) 2015 Guiding Lights Orders for Quarter 1 2015 Guiding Lights Orders for Quarter 2 Filtered Rows PARALLELPERIOD(…) New Virtual Table SUM(…) Sum all Sales in the Virtual Table
4 1 Using the DATESBETWEEN() Function Curr Qtr Sales: =CALCULATE([Sales in Dollars], DATESBETWEEN('Time'[Date], STARTOFQUARTER('Time'[Date]), ENDOFQUARTER('Time'[Date]))) 2015 Guiding Lights Orders for Quarter 1 2015 Guiding Lights Orders for January Filtered Rows DATESBETWEEN(…) New Virtual Table SUM(…) Sum all Sales in the Virtual Table
4 2 Using Variables in a Measure Qtr Over Qtr Sales Growth: = VAR Qtr. Over. Qtr. Diff = [Curr Qtr Sales] - [Prev Qtr Sales] return IF([Prev Qtr Sales] <> 0, DIVIDE(Qtr. Over. Qtr. Diff, [Prev Qtr Sales]))
Advanced Interactivity Demonstration
Power BI Report Server
Power BI Report Server A Suite of Products Power BI Reports Paginated Reports Mobile Reports KPI’s
Power BI Report Server Demonstration
Power BI Service
Power BI Service Features Demonstration
Workspaces Demonstration
Sharing and Receiving Reports and Dashboards Share Dashboard, Create and View Content Packs and Apps Email Subscriptions Demonstration
Data Gateway
• • • Personal, prototype use Impersonates you Available with Power BI Pro Computer must be on Computer must have internet connection • You have to be logged in or run services as an admin (run as an autonomous service running in the back ground) On-premises Data Gateway Personal Data Gateway • • For production enterprise use Installed on server, available 24/7 Always on and not asleep Direct internet connection, not wireless • Runs as local services
Data Gateway Power BI Model Power. BI. com Gateway Queue Gateway Cloud Service Azure Service Bus On-premises Application Gateway Data Source
Data Gateway Q/DS/C Power BI Model Power. BI. com Gateway Queue A model refresh or Direct Query queues up a request with Query/Data Source/Credentials (encrypted) Gateway Cloud Service Azure Service Bus On-premises Application Gateway Data Source
Data Gateway Q/DS/C Power BI Model Gateway Queue The request is analyzed by the Gateway Cloud Service Power. BI. com Gateway Cloud Service Azure Service Bus On-premises Application Gateway Data Source
Data Gateway Power BI Model Gateway Queue The Gateway Cloud Service sends the request to the Azure Service Bus Power. BI. com Q/DS/C Gateway Cloud Service Azure Service Bus On-premises Application Gateway Data Source
Data Gateway Power BI Model Gateway Queue Power. BI. com Gateway Cloud Service Q/DS/C Azure Service Bus On-premises The Application Gateway makes a secure, outbound connection to the Azure Service Bus Application Gateway Data Source
Data Gateway Power BI Model Gateway Queue Power. BI. com Gateway Cloud Service Q/DS/C Azure Service Bus On-premises The Application Gateway receives any pending requests Application Gateway Data Source
Data Gateway Power BI Model Gateway Queue Gateway Cloud Service Azure Service Bus Power. BI. com On-premises The Application Gateway uses the Data Source to initiate a connection Q/DS/C Application Gateway Data Source
Data Gateway Power BI Model Gateway Queue Gateway Cloud Service Azure Service Bus Power. BI. com On-premises The Application Gateway decrypts the credentials and uses them to complete the connection Q/DS/C Application Gateway Data Source
Data Gateway Power BI Model Gateway Queue Gateway Cloud Service Azure Service Bus Power. BI. com On-premises The query is sent to the data source and executed Q/DS/C Application Gateway Data Source
Data Gateway Power BI Model Gateway Queue Power. BI. com Gateway Cloud Service Azure Service Bus On-premises The result set is returned to the model Results Application Gateway Data Source
Data Gateway with Live Connect Power BI Model Power. BI. com Gateway Queue Gateway Cloud Service Azure Service Bus On-premises Application Gateway Data Source
Data Gateway with Live Connect Q/DS/C/UPN Power BI Model Power. BI. com Gateway Queue A Live Query queues up a request with Query/Data Source/Credentials (encrypted)/ User Principal Name Gateway Cloud Service Azure Service Bus On-premises Application Gateway Data Source
Data Gateway with Live Connect Q/DS/C/UPN Power BI Model Gateway Queue The request is analyzed by the Gateway Cloud Service Power. BI. com Gateway Cloud Service Azure Service Bus On-premises Application Gateway Data Source
Data Gateway with Live Connect Power BI Model Gateway Queue The Gateway Cloud Service sends the request to the Azure Service Bus Power. BI. com Q/DS/C/UPN Gateway Cloud Service Azure Service Bus On-premises Application Gateway Data Source
Data Gateway with Live Connect Power BI Model Gateway Queue Power. BI. com Gateway Cloud Service Q/DS/C/UPN Azure Service Bus On-premises The Application Gateway makes a secure, outbound connection to the Azure Service Bus Application Gateway Data Source
Data Gateway with Live Connect Power BI Model Gateway Queue Power. BI. com Gateway Cloud Service Q/DS/C/UPN Azure Service Bus On-premises The Application Gateway receives any pending requests Application Gateway Data Source
Data Gateway with Live Connect Power BI Model Gateway Queue Gateway Cloud Service Azure Service Bus Power. BI. com On-premises The Application Gateway uses the Data Source to initiate a connection Q/DS/C/UPN Application Gateway Data Source
Data Gateway with Live Connect Power BI Model Gateway Queue Gateway Cloud Service Azure Service Bus Power. BI. com On-premises The Application Gateway decrypts the credentials and uses them to complete the connection Q/DS/C/UPN Application Gateway Data Source
Data Gateway with Live Connect Power BI Model Gateway Queue Gateway Cloud Service Azure Service Bus Power. BI. com On-premises The User Principal Name is sent as the Effective. User. Name in the connection string Q/DS/C/UPN Application Gateway Data Source
Data Gateway with Live Connect Power BI Model Gateway Queue Gateway Cloud Service Azure Service Bus Power. BI. com On-premises The query is sent to the data source and executed in the security context of the user associated with the UPN Q/DS/C/UPN Application Gateway Data Source
Data Gateway with Live Connect Power BI Model Gateway Queue Power. BI. com Gateway Cloud Service Azure Service Bus On-premises The result set is returned to the model Results Application Gateway Data Source
Data Refresh with On-Premises Data Gateway Demonstration
Power BI Mobile
Supported Devices and Power BI App • i. OS (i. Pad, i. Phone, i. Pod Touch, or Apple Watch) • Android phone or tablet • Windows 10 device Supported Devices Power BI App • Download the Power BI App • Google Play Store • Apple Store • Windows Store • Sign into your Power BI account
Power BI Mobile Demonstration
Questions
Thank you Brian Larson blarson@teamscs. com
- Slides: 79