Blog – Page 10 – Prologika

Atlanta MS BI and Power BI Group Meeting on June 6th (How Power Query Thinks)

June 2, 2022 / No Comments »

Please join us online for the next Atlanta MS BI and Power BI Group meeting on Monday, June 6th, at 6:30 PM ET. For more details and sign up, visit our group page. Presentation: How Power Query Thinks: Taking the Mystery Out of Streaming and Query Folding Date: June 6th Time: 6:30 – 8:30 PM ET Place: Click here to join the meeting Overview: How does Power Query produce the table data your expressions ask it to output? Query folding is one key concept—where behind the scenes, part or all of your M code may be rewritten into the data source’s native query language then offloaded to the source for execution. Streaming, though perhaps a less familiar term, is even more fundamental, as it describes how table data (as well as list and binary data) flows between functions in M. Understand these concepts, and you'll be better positioned to...
Power BI Dataflows vs ADF Mapping Data Flows

April 15, 2022 / No Comments »

An enterprise client wants to migrate many Alteryx workflows created over years by smart business users to the Microsoft ecosystem. During the initial intake, we discussed Power BI dataflows vs Azure Data Factory mapping data flows. Yep, Microsoft loves to confuse us, but these technologies have nothing to do with each other. Power BI Dataflows ADF mapping data flows Target audience Business users Professionals Availability Power BI Service Azure Data Factory and Synapse Studio Underlying technology Power Query Spark Expression language M Language Expression functions evaluated to Spark data types Computation Engine Power BI Compute Engine Spark clusters Output Azure Data Lake Storage (CSV files in CDM folders) Many sinks Debugging N/A Debug mode Monitoring Very basic (refresh failures) Detail output In a nutshell, Power BI dataflows are meant for self-service ETL, where the business users would be responsible for creating and managing the ETL flows. By contrast, ADF mapping...
FastTrack Recognized Solution Architect 2022

April 7, 2022 / No Comments »

Microsoft re-awarded me as a FastTrack Recognized Solution Architect - Power BI for 2022! This prestigious recognition is conferred by the Power Platform product engineering team for consistently exhibiting deep architecture expertise and creating high quality solutions for customers during project engagements. I'm one of the 36 individuals worldwide who met the following criteria: Must have a minimum of 2 years of experience with Power BI and a minimum of 5 years of experience with Enterprise BI solutions Must have a minimum of 2 years of experience as an Enterprise BI architect Must be working for a partner with Gold certification in Data Analytics MPN competency Must have been lead architect for at least 2 Power BI in-production implementations with at least 200 active users (Preferably for CAT managed customers)
Atlanta MS BI and Power BI Group Meeting on April 4th

March 26, 2022 / No Comments »

Please join us online for the next Atlanta MS BI and Power BI Group meeting on Monday, April 4th, at 6:30 PM ET. Michael Carlo (Power BI MVP) will show how to speed up your data modeling experience by using DAX templates. For more details and sign up, visit our group page. Presentation: World's Fastest DAX - Using Quick DAX Templates Date: April 4th Time: 6:30 – 8:30 PM ET Place: Click here to join the meeting Overview: DAX is easy to Learn but hard to master. This session is all about using the Growing Community Driven Library of Quick DAX Templates. Learn how to find the templates and use them in your daily workflow to drastically speed up your Data Modeling Experience. Session Covers the following topics: 1. Introduces the concept of Templatized DAX 2. Consuming a Quick DAX Template using Power BI External Tools 3. Discusses how to...
5 Storage Gotchas for Power BI Dataflows

March 22, 2022 / No Comments »

Over the past few years, the BI industry has come up with new file formats, such as Parquet, ORC, and Avro, which are widely used today. To facilitate its vision for cross-industry data integration, Microsoft introduced a few years ago the Common Data Model (CDM) and CDM Folders. Power BI dataflows output CSV files to CDM folders and each table is saved in its own folder. You can bring your own data lake to directly access these files. If do so, you'll find the following folder structure: Although accessing the dataflow files might open all sorts of data integration scenarios, here are some things to watch for concerning the dataflow output: If you plan to migrate from other self-service ETL tools, such as Alteryx, note that a Power BI dataflow can output only to a CDM folder and export the data as CSV. Each time the dataflow refreshes, a new...
Referencing Columns in DAX Table Variables

March 10, 2022 / No Comments »

Suppose you use a DAX table variable, such as to group by certain columns and add an extension column as a calculation. Then, you want to count the rows in the table by filtering on one of the columns. At your first attempt, you might try using CALCULATE. VAR _t = ADDCOLUMNS(SUMMARIZE(…), "SomeColumn", <some expression>) RETURN CALCULATE(COUNTROWS(_t), _t[SomeColumn] = something) You'll get an error that the column you reference cannot be found. To get this to work, you must use FILTER (notice that code uses [SomeColumn] instead of the column fully qualified name (_t[SomeColumn). VAR _t = ADDCOLUMNS(SUMMARIZE(…), "SomeColumn", <some expression>) RETURN COUNTROWS(FILTER(_t, [SomeColumn] = something) Why doesn't CALCULATE work? CALCULATE changes the filter context, but DAX filter context only allows columns in the model. It doesn't allow "extension" columns created in a DAX expression. [SomeColumn] does not exist in the model but only in the DAX expression and therefore...
Data Integration Options with Cloud Providers

March 9, 2022 / No Comments »

It's unlikely to envision a data analytics solution without having to ingest data from some cloud vendor. I previously ranted here about cloud prohibitors – cloud providers that negate the benefits of moving to the cloud by enforcing all sorts of hoops to get to your data. This blog summarizes a few data integration options with cloud vendors from best to worst. Direct access to the database – This is by far your best option. If the provider allows you to access the data by directly connecting to its native storage, you should take it even if it involves higher fees, such as upgrading to a "premium" tier (a travesty considering that you didn't have pay higher fees with their on-prem installations). There are no insurmountable engineering barriers for the vendor to provide direct access to the data in its native storage, but very few cloud vendors do it citing...
Atlanta MS BI and Power BI Group Meeting on March 7th

February 28, 2022 / No Comments »

Please join us online for the next Atlanta MS BI and Power BI Group meeting on Monday, March 7th, at 6:30 PM ET. Elayne Jones (Consultant with BlueGranite) will cover the pros and cons of migrating semantic models from Azure Analysis Services to Power BI. For more details and sign up, visit our group page. Presentation: Migrating Azure Analysis Services Models to PBI Premium Date: March 7th Time: 6:30 – 8:30 PM ET Place: Click here to join the meeting Overview: Migrating Azure Analysis Services models to Power BI Premium simplifies resource maintenance and drives efficient business intelligence environments. Here, Elayne Jones covers the benefits, drawbacks, prerequisites, and available deployment options. Speaker: Elayne Jones is a Staff Consultant at BlueGranite who brings experience with data analysis, visualization, and troubleshooting in the Transportation and Shipping industries. She graduated Summa Cum Laude from The University of North Georgia and brings her knowledge...
How Power BI Broke Amazon

February 20, 2022 / No Comments »

Amazon started in 1994 as an online marketplace for books. You'd think that after being in this business for almost 30 years, they've figured out the book ecommerce inside out. Unfortunately, rapid revisions (I revise annually my Power BI book) break their model. Every new revision wreaks havoc in the customer experience to purchase a book and punishes the publisher. Here is what I've learned after having my books sold on Amazon for many years. Amazon has a proprietary algorithm that decides what to show on the book detail page. My books are available in two formats: paperback and ebook (Kindle). When a customer lands on the book page, the natural expectation is that the Kindle tab would redirect to the ebook format and the Paperback tab would redirect to the paperback format of the same edition. Unfortunately, the publisher has no control over what the Paperback tab would eventually...
Power BI Dynamic M Query Parameters Reloaded

February 17, 2022 / No Comments »

In a previous blog from October 2020, I ranted about the narrow list of data sources supported by Power BI Dynamic M query parameters. The February 2022 update of Power BI Desktop expanded the feature reach to relational data sources, such as SQL Server (all flavors), Oracle, Teradata, and SAP. This warranted a second look to see where we're now and where we're going. Dynamic queries are now possible to popular relational data sources The extended support makes it possible to execute SQL queries or stored procedures by passing parameters based on the user selection. I attach a sample pbix file that demonstrates this scenario. It calls a stored procedure uspGetManagerEmployeesTL in the AdventureWorks2021 database. The stored procedure is a slightly enhanced version of the original uspGetManagerEmployees sp included with AdventureWorks because I wanted to experiment with different scenarios around data security. The idea is to execute the stored procedure...