Welcome Power BI User Group PUG Spokane Power
Welcome! Power BI User Group (PUG) Spokane Power BI User Group
Agenda • Welcome & Introductions • Power. BIUG News & Updates • Member Showcase • User Group Discussion • Next Meeting & Upcoming Events
Welcome & Introductions q Name q Company q Role in Company q How do you use Power BI? q What do you want to get out of these meetings?
Power BI User Group News & Updates
2019 Power Platform Summit North America • 3+ days of learning, sharing and network-building to get full value from the Power BI, Power. Apps and Flow technologies you’re using right now. • 60+ courses, for deep insights, time-saving tips and tricks, and practical ideas you can implement right away. • Outstanding networking with peers – plus access to ISVs, partners, Microsoft experts and others, all eager to offer instruction and share proven insights. • On-demand Session Recordings. While registering, upgrade your Summit experience to include Session Recordings and get on-demand post-Summit access to 200+ sessions! • Pre-Conference Academy training to build practical skills you can apply year-round. Check out a dozen-plus Power Platform sessions. (Register today for Pre-Conference Academy and you could win a Microsoft Surface Laptop 2!) • All-new offerings for 2019, like expert advice at the expanded User Group Medics Station, the excitement of 4 simultaneous sessions in 1 room at the Power Zone, Active Shooter Preparedness and Personal Cyber Privacy & Security training from Personal Academy, and much more!
Power Platform World Tour 2019
Vancouver, British Columbia • Dates • 26 -27 September 2019 • Location • Fairmont Hotel Vancouver • About • 24+ breakout sessions designed for the Citizen Developer, Technical, Business, and New User • Add an additional day of training with an Academy Workshop* *Additional registration required Learn More & Register at: powerplatformworldtour. com
Become A User Group Leader or Co -Lead • Chairperson/Co-Chair • VP of Membership • VP of Programming • VP of Communications
Member Showcase Curtis Heyamoto and Dustin Dauphin Pyrotek
Member Showcase q SQL BI Data Modeling On-Site Training q 2019 Microsoft Business Applications Summit (MBAS)
SQL BI Data Modeling On-Site Training
SQL BI Data Modeling Training • Chicago, IL ; March 25 -27, 2019 • 20 -30 Attendees, Various Industries • Marco Russo • www. sqlbi. com/training/data-modeling
SQL BI Data Modeling Training • Optimizing Data Models • Faster queries • Simpler DAX Code • Easier maintenance • Exercises and Demo Files • Analyzing Data with Microsoft Power IB and Power Pivot for Excel Book
2019 Microsoft Business Applications Summit
MBAS Overview • Microsoft’s Premier Event for the Power Platform & Dynamics • Power Platform • Power BI • Power. Apps • Flow • Dynamics 365 • Atlanta Convention Center • 4 -5 k attendees
Sessions Attended • Opening Keynote • Microsoft Power BI: The future of modern BI - roadmap and vision • Microsoft Power. Apps: Introduction to Power. Apps and integration with Power BI • Microsoft Power BI: Enterprise Reporting • Microsoft Power BI: Building and implementing a governance strategy - from self-service to enterprise • Microsoft Power BI: AI Powered Analytics • Microsoft Power BI: Designing for high performance in Power BI • Microsoft Power BI: Using the Power platform to radically change your business • Microsoft Power BI: Debugging your premium capacity • Microsoft Power BI: BI Power Hour
Power BI in the Industry • Power BI is now the clear leader in both vision and ability to execute in the Gartner BI matrix. • Power BI is used by 95% of Fortune 500 companies.
Power BI Keynote - New Stuff • Power BI Desktop and the Power BI service will be getting a new UI. The UI is modernized, light in color, and looks more like other Office apps. • New corporate branding capabilities are coming, along with new metadata on reports, including the owner and last refresh time. • In the new service, report viewers will be able to toggle visuals in the report. For example, change a bar chart to a pie chart. (Not sure if this is good for most users!) • Ability to add comments to reports and reply to those comments.
Power BI Keynote - New Stuff • Siri integration to mobile app. For example, “Siri, open the sales report. ” • Paginated reports are now GA. These are like SSRS reports, and you can subscribe to receive them on a regular basis. • Shared and Certified datasets are now in preview. Datasets can be tagged as promoted or certified, and other users can be granted a "Build" permission and allowed to create reports from those datasets. They can even create these reports in a different workspace than the one the dataset resides in. This is REVOLUTIONARY.
Data Modeling Best Practices • Performance issues are typically due to sub-optimal model design • As a rule, you want to make your model as small as possible. Only bring in data you need for your reporting. Do not bring in data that you think you might use later. • Tables should be as narrow as possible, remove extra columns that aren’t needed. Move them into dimension tables where applicable. Filter the rows in your tables to only included the necessary data. Unnecessary data creates longer load times and takes up more space in memory.
Star Schema • Power BI is optimized to work with Star Schemas. A star schema consists of a FACT table and associated DIMENSION tables. The FACT table is where your transaction data is, the numbers. DIMENSION tables are the tables that contain the information you want to categorize the numbers by.
More ways to optimize • Remove columns with high cardinality or primary key columns if not needed. They do not compress well into memory. • Use integers instead of strings wherever possible. • BI-directional relationships - be careful with these, as they can cause a significant performance hit. They can also cause unexpected and incorrect filtering. Consider using an explicit DAX CROSSFILTER when filtering the other direction is needed.
Data Storage Modes • Data Storage mode - Import, Direct Query, or Dual. • Import is the first choice. The dataset is loaded into memory, so it provides the fastest performance. It also gives you access to all DAX functions. • Direct Query might be a better option if you have extremely large datasets or if you need data closer to real-time. • Dual storage mode does both an import and also provides for direct query where necessary.
More ways to optimize • Auto Date/Time - disable it! It creates a date table for every date in your model and bloats your PBIX file. Better to use a common calendar/date table for time intelligence. • Use a measure instead of a calculated column whenever possible. You should know why you are using a calculated column instead of a measure. • Use variables in DAX to greatly speed up your measures. Assigning a value to a variable allows you to reuse that variable without taking a performance hit every time it is referenced.
Data Shaping • In general, data shaping should be done as close to the source data as possible. • Creating a view in SQL to join data will offload the processing to the SQL server and use less Power BI resources. • Likewise, concatenating two columns using power query rather than bringing both columns into your model and creating a calculated column will allow you to load less data into your model and take up less space in memory.
Affecting Performance
Report Optimization
High-Level Performance Takeaways
User Group Discussion
Next Meeting • Date: October 2, 2019 • Time: TBD • Place: TBD • Topics/Speakers: TBD • Member Showcase: TBD
THANK YOU! QUESTIONS? CURTIS HEYAMOTO CURHEY@PYROTEK. COM
- Slides: 31