Blog – Page 8 – Prologika

Atlanta MS BI and Power BI Group Meeting on July 11th (Pushing the Query Folding limits with Power Query)

July 2, 2022 / No Comments »

Please join us online for the next Atlanta MS BI and Power BI Group meeting on Monday, July 11th, at 6:30 PM ET. For more details and sign up, visit our group page. Presentation: Pushing the Query Folding limits with Power Query Date: July 11th Time: 6:30 – 8:30 PM ET Place: Click here to join the meeting Overview: One of Power Query’s most powerful features is its ability to translate the Power Query formula language (M) back to a source systems native language and in this session, we’ll push the limits and possibilities to avoid “breaking the fold” and explore some potential dark magic with List functions. A base understanding of T-SQL is helpful though not required for this session. Speaker: From financial services to felines, the World Wide Web to professional wrestling - Alex Powers has an affinity for the conventional and unconventional when it comes to information....
Power BI Field Parameters

June 7, 2022 / No Comments »

Coming back from a long vacation and I almost missed this new Power BI killer feature: Field Parameters! Not to be confused with Dynamic M Query Parameters that I ranted about here, field parameters solve a long-standing limitation of Power BI that prevents binding dynamically fields to a visual. Dynamic binding isn't an issue with measures because they are dynamic and can evaluate runtime conditions, such as slicer selection, but dimensions are a different story. Once they are bound to a category bucket in a visual, you couldn't change them on the fly. Yet, one common scenario was to let the user control which fields will be used for slicing the measure(s) in a visual. I've seen rather convoluted implementations to get around this limitation. Field parameters to the rescue. Now once you create a field parameter and bind it to the visual, the user can simply select which field...
Power BI Datamarts: the Good, the Bad, and the Ugly

June 6, 2022 / No Comments »

As Microsoft announced here, Power BI datamarts are upon us. I can almost see an important enterprise client demanding "self-service datamarts me now or else… ", thus inspiring an opportunity for another premium feature, spearheaded with great vision and effort, but questionable practical value. In a nutshell, a Power BI datamart is a combo of Power BI Premium and a Microsoft-hosted Azure SQL Database aiming to simplify the implementation of a departmental datamart. The Good Unlike other vendors, such as Domo and their proprietary and overly expensive stack, Microsoft has decided to go with somewhat open solution consisting of tools that Power BI users already know: Power Query, Power BI Desktop (for the first time some of its modeling features, such as relationships and DAX measures, made it to the cloud), and SQL Server. Microsoft provisions the database for you although surrounds it with some red tape (more on this...
A Case for Azure Analysis Services

June 5, 2022 / No Comments »

Microsoft BI practitioners have three options for hosting semantic models: SSAS (on prem), Azure Analysis Services (cloud), and Power BI (cloud). AAS is somewhat caught between a rock and a hard place. Given that Power BI gets the most attention for cloud deployment, why would you consider AAS at all? There are two main reasons: Cost – Organizational semantic models might require a lot of memory and crunching power. Hosting them on AAS might be more cost effective. For example, AAS S4 runs at around $5,000 which at the same price point as Power BI Premium P1. However, it gives you 100 GB of RAM and 20 cores, whereas P1 has only 25 GB and 8 cores. Scaling out – A feature unique to AAS is ability to scale out to multiple query replicas. This is not an option with Power BI Premium, and it requires quite a bit of...
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...