-
A First Look at DAX Visual Calculations: the Good, the Bad, and the Ugly
February 28, 2024 / No Comments »
The February 2024 release of Power BI Desktop includes a preview of visual calculations. As its name suggests, a visual calculation is a visual-scoped DAX measure that works at the aggregate (visual) level. The Good Visual calculations make previously difficult tasks much easier. Consider the following matrix: Suppose you need a measure that calculates the difference between the product categories in the order they were sorted in the visual. Implementing this as a regular DAX measure is a challenge. Yet, if we had a way to work with the cells in the visual, we can easily find a way to get this to work. Ideally, this would work similar in Excel, but DAX doesn’t know about relative references. However, visual calculations do (kind of). Let’s right-click on the visual and select “New calculation”. In the visual-level DAX formula bar, enter the following formula: Category Diff = [Sum of SalesAmount] -...
-
Atlanta Microsoft BI Group Meeting on March 4th (Navigating Microsoft Fabric – Choosing the Right Workload for Your Needs)
February 27, 2024 / No Comments »
Atlanta BI fans, please join us in person for the next meeting on Monday, March 4th at 6:30 PM ET. The famous Patrick LeBlanc (Guy in the Cube) will take a deep dive into the Microsoft Fabric ecosystem, from Lakehouse to Warehouses and Power BI, ensuring you can make informed decisions about your data processing needs. Your humble correspondent will help you catch up on Microsoft BI latest. CloudStaff.ai will sponsor the event. For more details and sign up, visit our group page. Presentation: Navigating Microsoft Fabric - Choosing the Right Workload for Your Needs Delivery: In-person Date: March 4 Time: 18:30 – 20:30 ET Level: Beginner/Intermediate Food: Pizza and drinks Agenda: 18:15-18:30 Registration and networking 18:30-19:00 Organizer and sponsor time (events, Microsoft BI latest, sponsor marketing) 19:00-20:15 Main presentation 20:15-20:30 Q&A Overview: As businesses transition to the cloud and leverage advanced analytics, understanding the nuances of data infrastructure...
-
Nothing in Excess: Deciphering Data Architectures by James Serra
February 9, 2024 / No Comments »
In my last post, I talked about the lessons learned from the ancient Greeks. The second inscription on the temple of Apollo in Delphi was "Nothing in Excess." Yet, inspired to be modern and embracing vendor's propaganda, it's my experience that companies tend to overdo their data architectures. I talked about this in this newsletter. Another excellent resource that could help you strike a balance is the James Serra's latest book "Deciphering Data Architectures." I was privileged to know James Serra for a while, have him multiple times present to our Atlanta BI Group, and be an early reviewer of this book. Besides consolidating the wealth of information from his excellent blog, James took a vendor-neutral approach to various patterns. Each pattern describes the architecture followed by pros and cons analysis. After all, the right architecture and "best practice" is what works for you given your specific circumstances and restrictions....
-
Know Thyself: Power BI Source Control
February 8, 2024 / No Comments »
Last year my wife and I did a tour of Greece, and we had a blast. Greece, of course, is the place to go if you are interested in ancient history and the origin of democracy. One of the places we visited was Delphi. The ancient Greeks believed it to be the center of the universe. Now not much was left of it except lots of ruins and imagination. But back then it was magnificent. People from all over the world would come to consult with the Oracle of Delphi. She delivered her prophecies from the temple of Apollo, which had three inscriptions, with one of them being "Know thyself". The practical benefit for the oracle was that if you believed her cryptic prophecy wasn't fulfilled then your interpretation was wrong. Therefore, the problem was in you because you didn't know yourself. How does this translate into BI? I see...
-
Atlanta Microsoft BI Group Meeting on February 5th (Power BI – Time to Git CI/CD)
January 29, 2024 / No Comments »
Atlanta BI fans, please join us for the next meeting on Monday, February 5th at 6:30 PM ET. Note that this will be an online meeting via MS Teams and non-Atlanta BI fans are also welcome. Kristyna Hughes (Senior Consultant with 3Cloud) will show us how to implement source control for Power BI reports. Your humble correspondent will help you catch up on Microsoft BI latest. For more details and sign up, visit our group page. Presentation: Power BI - Time to Git CI/CD Delivery: Online Date: February 5 Time: 18:30 – 20:30 ET Level: Intermediate Food: NA Agenda: 18:15-18:30 Registration and networking 18:30-19:00 Organizer and sponsor time (events, Microsoft BI latest, sponsor marketing) 19:00-20:15 Main presentation 20:15-20:30 Q&A Overview: As report developers, requirements for reports are constantly evolving which leads the reports themselves to change. However, inevitably, there will be one party who uses that report and was...
-
SSRS Going on 20
January 24, 2024 / No Comments »
Last year around this time, I went to Antarctica on an expedition ship. Although I got my fair share of icebergs, whales, and penguins, I found it interesting to reproduce some of the experience of the first explorers. On our way back, we got it pretty rough crossing the Drake passage. 80% of the passengers succumbed to sea sickness. There was a huge swell during the night. The next day the captain told us that we faced some 10-meter waves. It felt like a bad roller coaster ride. Yet, the explorers did it. On wooden ships with no stabilizers and communication. Not knowing where they were going and with a high probability that they won’t survive! What does this have to do BI? The remote connection is that 20 years ago a small group from Microsoft was set up to create a new reporting tool that was code-named “Rosetta” and...
-
Atlanta Microsoft BI Group Meeting on January 9th (Getting Started with Power BI Paginated Reports)
January 2, 2024 / No Comments »
Atlanta BI fans, please join us for the next meeting on Monday, January 8th, at 6:30 PM ET. Eric Flamm will review paginated (SSRS) reports in Power BI. Your humble correspondent will help you catch up on Microsoft BI latest. For more details and sign up, visit our group page. Presentation: Getting Started with Power BI Paginated Reports Delivery: In-person Date: January 9 Time: 18:30 – 20:30 ET Level: Beginner Food: TBD Agenda: 18:15-18:30 Registration and networking 18:30-19:00 Organizer and sponsor time (events, Power BI latest, sponsor marketing) 19:00-20:15 Main presentation 20:15-20:30 Q&A Venue Improving Office 11675 Rainwater Dr Suite #100 Alpharetta, GA 30009 Overview: Power BI Paginated Reports are the successor to SQL Server Reporting Services reports (although SSRS is still part of the SQL Server BI Stack). Using the same RDL (report definition language) as SSRS, Power BI Paginated Reports enable report developers to create...
-
Notes on Fabric F2 Performance: Report Load
December 25, 2023 / No Comments »
What a better way to spend a lazy holiday afternoon than to do more Fabric performance testing? In my previous post, I shared my results from a single-threaded ETL load test to gauge the F2 ingest performance and F2 did pretty well (or at least outperformed Azure SQL DB). Will F2 hold as parallelism increases? Throughput testing is especially important for report loads because parallel tasks can run within a report, such as visuals executing DAX queries in parallel, and across reports, such as when concurrent report requests overlap. I used the artifacts are included in the "Microsoft Dashboard in a Day" for this test and load tested only the first report page. The Sales fact table in the semantic model has over seven million rows so it represents a good size dataset. Naturally, the more involved the report is and the more data the semantic model has, the more...
-
Notes on Fabric F2 Performance: Warehouse ETL
December 23, 2023 / No Comments »
As inspired by Amir Netz's encouragement to partners to test the Fabric F2 capacity performance, I got on a quest to test what it would do to ETL loads for Fabric Warehouse. I must admit that I was skeptical that a quarter of a core would take a warehouse off the ground, but as usual, life proved me wrong and "wrong" is a big understatement of what happened. After provisioning a Fabric F2 capacity and a warehouse, I settled on the Retail Data Model for World Wide Importers sample star schema dataset consisting of five dimension tables and one fact table. In terms of performance, I was mostly interested in how long it would take for the ADF copy activity to insert all the data (50 million rows) in the fact table. Granted, it's a limited test but enough to rule out the technology for real-life projects. Then, I compared...
-
A Case for Microsoft Fabric: Dynamics 365 Analytics
December 7, 2023 / No Comments »
I might have identified at last a good case for Microsoft Fabric, but I'll be in a better position to confirm after a POC with larger datasets. Dynamics Online, aka Dynamics 365, epitomizes the customer's struggle to export their data hosted in SaaS cloud offerings for analytics or other purposes. Since unfortunately Microsoft doesn't provide direct access to the Dynamics native storage (Azure SQL Database), which often could be the simplest and fastest solution, Dynamics has "compensated" throughout the years by introducing and sunsetting various options: Option Pros Cons OData API Original interface Slow, no query folding, currently deprecated Data Export Service Export to Azure SQL Database Deprecated TDS endpoint Real-time direct access to Dynamics Azure SQL Database Not designed for massive extracts; slow Dataverse API Currently recommended by Microsoft As far as I can tell, no REST operations (see below); questionable performance Dataverse connector Same as Dataverse API Same...