Blog – Page 12 – Prologika

Atlanta MS BI and Power BI Group Meeting on February 7th

January 26, 2022 / No Comments »

Please join us online for the next Atlanta MS BI and Power BI Group meeting on Monday, February 7th, at 6:30 PM ET. Your humble correspondent will show you different techniques to implement scorecards with Power BI. For more details and sign up, visit our group page. Download the presentation assets here. Presentation: Implementing Scorecards Date: February 7th Time: 6:30 – 8:30 PM ET Place: Click here to join the meeting Overview: Join this session to learn how to create scorecards measuring strategic objectives with Power BI. We'll start by introducing you to balanced scorecards and KPIs. Then, I'll compare different ways to assemble scorecards, including: · Conditional formatting · Analysis Services KPI · Power BI Goals Demos will make it all clear. As a bonus, you'll learn some black-belt modeling techniques, such as using Tabular Editor where Power BI Desktop falls short. Speaker: Teo Lachev is a consultant, author,...
DirectQuery Performance Progression

January 12, 2022 / No Comments »

So much data, so little budget! Caching data in Power BI gives you the best report performance, but budget constraints usually put downward pressure to stay within lower Power BI Premium premium plans. So, what to do? Split large models. Remember that Power BI Premium Gen2 grants each dataset a 25 GB memory quota. I'm actually a big proponent for consolidated organizational semantic models, but this is where best practices meet reality. Switch large tables to DirectQuery. When report performance sucks, follow this performance optimization progression: Add a columnstore index to the fact table – As VertiPaq, a columnstore index organizes data in columns so aggregate queries should see an immediate performance boost. Detail-level queries, e.g. sales by customer, not so much as they probably won't hit the index. Try hybrid tables when you can get away with a compromise where the latest data can be cached, but archive data...
Azure Learning Path

January 10, 2022 / No Comments »

I'm preparing for the new Azure Data Engineer exam, which is one of options to maintain a Gold Certification for Data Analytics for MS partners that has to be renewed annually. I'm really impressed by the Azure Learning Path self-paced environment that Microsoft put together. The training material is top-notch, and I actually learn some practical and useful stuff from the covered topics on ADLS, ADF, Synapse, etc. And you can even practice in an Azure sandbox environment that Microsoft sets up and tears down for you! It's clear that tremendous effort has gone into setting this online and free learning option. No excuse to not certify anymore … Check it out the online learning option for exam DP-203 at https://docs.microsoft.com/en-us/learn/certifications/azure-data-engineer/.
Applied Power BI Book (7th Edition)

January 2, 2022 / No Comments »

Happy New Year! I'm excited to announce the availability of the seventh edition of my Applied Microsoft Power BI book! When the first edition was published in January 2016, it was the first Power BI book at that time and it had less than 300 pages. Since then, I helped many companies adopt or transition to Power BI and taught hundreds of students. It's been a great experience to witness the momentum surrounding Power BI and how the tool has matured over time. As a result, the book also got thicker and doubled in size. However, I believe what's more important is that this book provides systematic, yet dependent, view by showing what Power BI can do for four types of users (business users, analysts, pros, and developers). For the first time, the book features a Kindle PDF (Amazon Print Replica) ebook version which replaces the Kindle native "reflow" format...
Atlanta MS BI and Power BI Group Meeting on January 3rd

December 27, 2021 / No Comments »

Please join us online for the next Atlanta MS BI and Power BI Group meeting on Monday, January 3rd, at 6:30 PM ET. Paul Turley will show us how to integrate Power BI with paginated (SSRS) reports. And your humble correspondent will update you on the Power BI latest. For more details and sign up, visit our group page. Presentation: Power BI Paginated Reports: The New Old Operational Reporting Platform Date: January 3rd Time: 6:30 – 8:30 PM ET Place: Click here to join the meeting Overview: Power BI Paginated Reports (aka SQL Server Reporting Services) was old but now it's new again. Available on-premises or in the Power BI service with flexible licensing, you have multiple options to implement operational reports. This session will briefly cover the differences between analytic and operational reports; and help you understand the advantages and trade-offs using Power BI Paginated Reports, Power BI Report...
Power BI Hybrid Tables

December 17, 2021 / No Comments »

A while back a client wanted to avoid importing a large snapshot fact table with loan balances because its memory footprint would require them to upgrade to a higher Power BI premium plan. This of course required leaving the table in DirectQuery mode at the expense of query performance. Luckily, most users would be interested in the latest six months of data. To speed up performance, we opted for aggregations. However, to complicate things further, they had M2M relationships between dimensions and the fact table which Power BI aggregations don't support. So, we had to roll out our own "aggregation hits" by redirecting DAX measures either to the aggregated table if the as-of date was in the last six months or to the DirectQuery table otherwise. Seasoned BI pros might recall that Multidimensional supports measure groups with a mixed storage by creating MOLAP and ROLAP partitions within the same table....
Atlanta MS BI and Power BI Group Meeting on December 6th

November 29, 2021 / No Comments »

Please join us online for the next Atlanta MS BI and Power BI Group meeting on Monday, December 6th, at 6:30 PM ET. To finish the year at a high note, the famous Guys in the Cube (Patrick and Adam) will tell us how keep the data fresh in Power BI. And your humble correspondent will update you on the Power BI latest. For more details and sign up, visit our group page. Presentation: Keeping your Data Fresh in Power BI Date: December 6th Time: 6:30 – 8:30 PM ET Place: Click here to join the meeting Overview: We all want our data refresh to happen quickly so the most current data is available for our reports. In this session we will walk you through options to configure refreshing your data but more importantly we will help with performance. We’ll look at how to identify bottlenecks and then how to...
Power BI Bookmark Navigator – A Better Hack

November 17, 2021 / No Comments »

As a report author, you are constantly pressed to fit more visuals into a single page. The November release of Power BI Desktop introduced the Power BI Bookmark Navigator, which simplifies the process of creating a tabbed interface, such as this one. Since Power BI doesn't support visual containers or a "menu" visual, you must resort to the awful hack of hiding and showing UX elements by bookmarking them. This reminds me of the beginning of my career as a developer where we didn't have widgets and we had to hack our way through implementing a custom navigation "experience" by toggling visibility. Alas, this continues in the 21st century but at least the hack got simplified. To implement the tabbed interface: Add two (or more) overlapping visuals. Add two bookmarks (Bar Chart and Column Chart) that show and hide the appropriate visual. Don't worry about hidden visuals impacting the report...
“Serverless” Lessons Learned

November 5, 2021 / No Comments »

I've architected and currently implementing a solution that uses Synapse (my last newsletter has the details, plus the architecture diagram). Synapse Serverless is the Microsoft answer to Amazon Athena but instead of using open-source tools like Presto, it's built on SQL Server. In this project we extract many tables from 1,500 on-prem SQL Server databases and stage them in ADLS. From there we use Synapse Serverless to virtualize these files as tables that we query with T-SQL to load the source "table" data into a data warehouse hosted in Synapse SQL Pool. I have to tell you that I'm becoming a "serverless" fan. Here are a few lessons learned from this project: Save the files in parquet format in ADLS. Parquet can be compressed. It's columnar based, it's much faster to query. Serverless automatically creates statistics for parquet files on the first query and each time it detects changes. Less...
Atlanta MS BI and Power BI Group Meeting on November 1st

October 30, 2021 / No Comments »

Please join us online for the next Atlanta MS BI and Power BI Group meeting today (Monday, October 4th), at 6:30 PM ET. Sandeep Pawar will explain how to use the Power BI AI visuals for predictive insights. And your humble correspondent will show you how to use the Power BI REST APIs. For more details and sign up, visit our group page. Presentation: Demystifying Power BI AI Visuals Date: November 1st Time: 6:30 – 8:30 PM ET Place: Click here to join the meeting Overview: Power BI has several powerful AI visuals that allow business analysts to create insightful reports that include predictive capabilities without writing any code. In this session, we will take a deeper look at these visuals, discuss how exactly they work, when & how you should use them effectively and importantly when not to use them. We will look at the algorithms driving them and...