Blog – Page 28 – Prologika

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

January 30, 2019 / No Comments »

MS BI fans, join us for the next Atlanta MS BI and Power BI Group meeting on February 4th, Monday at 6:30 PM at the Microsoft office in Alpharetta. The main presentation will be "Azure Data Lake for First Time Swimmers" by Samara Soucy from Innovative Architects. Julies Smith will do us Prototypes with Pizza on "Python Fuzzy Wuzzy for Master Data Identity Mapping". Innovative Architects will sponsor the event. For more details, visit our group page and don't forget to RSVP (use the RSVP survey on the group page) if you're planning to attend. Presentation: Azure Data Lake for First Time Swimmers Date: February 4, 2019, Monday Time 6:30 – 8:30 PM ET Place: Microsoft Office (Alpharetta) 8000 Avalon Boulevard Suite 900 Alpharetta, GA 30009 Overview: Data Lake may be the hot new way to store data for reporting, but it can be scary if you've never dealt with one. If you have...
How Filters Affect DAX Measures

January 26, 2019 / No Comments »

xVelocity (the storage engine behind Tabular, Power BI and columnstore indexes) is an in-memory columnar database, and it's such it's not suitable for detail-level reports, such as transaction-level reports. I wrote about performance implications with xVelocity and detail-level reporting here and here. In general, the lower the report grain and the more columns you add to the report (customer first name, last name, invoice number, etc.), the slower the performance will be as more and more columns would need to be scanned and cross-joined. A recent Tabular project brought another twist. Most measures (about 250 total) were produced on top of a biggish snapshot table (250 million rows) and Customer table (12 million rows). As a best practice, base measures were created with common filters and then other measures would piggy back on the base measures like Russian dolls. It's a best practice because if you must change a filter,...
T-SQL, Power Query, or DAX?

January 20, 2019 / No Comments »

I taught my Applied Power BI class last week to a group of smart data analysts. All of them were knowledgeable of T-SQL, which they have been using extensively for years to shape and transform the data and produce SSRS, Qlik and now Power BI reports. They haven't previously used Power Query, but they liked my overview of its features. However, they were rightfully confused when to use T-SQL, Power Query, or DAX. Starting with the data source and moving up the chain, Power BI lets you use expressions in: Data source (custom SQL Query, SQL view) Power Query DAX calculated columns DAX measures (the uppermost level) My advice is to invest time and learn all these capabilities. When you have diverse skills, you can use the best tool for the task at hand. Although is hard to generalize as every data transformation is different, I recommend you shape the...
Atlanta MS BI and Power BI Group Meeting on January 7th

January 2, 2019 / No Comments »

MS BI fans, join us for the next Atlanta MS BI and Power BI Group meeting on January 7th, Monday at 6:30 PM at the Microsoft office in Alpharetta. The main presentation will be "Predictive Analytics with Power BI". I'll walk you through the existing Power BI features for predictive analytics. Mark Tabladillo will show you exiting AI features that are coming up soon. CDATA will sponsor the event and do us a 15-min demo of their Power BI connector. For more details, visit our group page and don't forget to RSVP (use the RSVP survey on the main page) if you're planning to attend. Presentation: Predictive Analytics with Power BI Date: January 7, 2019, Monday Time 6:30 – 8:30 PM ET Place: Microsoft Office (Alpharetta) 8000 Avalon Boulevard Suite 900 Alpharetta, GA 30009 Overview: Predictive analytics, also known as data mining, machine learning, and artificial intelligence (AI), is an increasingly popular requirement. Fortunately,...
Applied Power BI Book (4th Edition)

December 31, 2018 / No Comments »

I'm excited to announce the fourth edition of my Applied Microsoft Power BI book! When the original book 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 matured over time. As a result, the book got thicker too and it now stands at 500 pages. 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). This is the only Power BI book that gets annual revisions to keep it up to date with this ever changing technology! Because I had to draw a...
Power BI External Users and Data Security

December 28, 2018 / No Comments »

Power BI lets you share content directly with external users for B2B and B2C scenarios. When the benefits of this sharing option outweigh its limitations (read-only reports, requiring Azure AD federated access, per-user licensing, rendering the report inside Power BI), this is the easiest way to share Power BI content with an external party. However, the documentation, which is otherwise excellent, doesn't explain the steps required to allow the external user to see only a subset of data when you have a dataset configured for data (RLS) security. Granting access to an external user to a dataset configured for data security is like the proverbial chicken and egg problem. To grant access to the report, you need to share the report with the user, but you can't add the user to the security role because the user is not provisioned yet. Hence, you'll first share a non-sensitive report with the...
Expression-based Formatting in Power BI

December 15, 2018 / No Comments »

If you have used SSRS, you know that paginated reports are very customizable, and you can make almost any property expression-based. Power BI is yet to deliver expression-based properties to change settings based on runtime condition, such as to change the font style based on the actual value or user selection. Currently, there are two places where you can use a DAX measure for expression-based formatting of colors: Conditional formatting You can use a DAX measure to change the fore color or background color when you apply conditional formatting to cells in Table and Matrix reports. To do so, when you configure the conditional formatting settings, choose the Field Value option. In the following report, I used a DAX measure to format the TaxAmount field with the following formula: TaxColor = SWITCH(TRUE(), SUM(ResellerSales[TaxAmt])> 100000, "Red", SUM(ResellerSales[TaxAmt])>50000,"Yellow", SUM(ResellerSales[TaxAmt])>10000,"Green", BLANK()) Although the measure defines three bands of colors, which you can do...
Are You Modern Yet?

November 28, 2018 / No Comments »

If you're a BI architect responsible for designing Azure-based cloud BI solutions and you're not following the James Serra's blog, you're missing a lot. Previously an independent consultant and MVP, James works now as a Big Data/Data Warehouse Evangelist at Microsoft. I can't agree more with his latest blog "Should I load structured data into my data lake?" We've heard a lot lately about what the "modern" data warehouse architecture should look like from Microsoft and other vendors. Here is the diagram taken from, you guessed it, the Microsoft Modern Data Warehouse page. The problem is that many organizations take this diagram verbatim when it makes sense and when it doesn't. For example, a client exports data from on-premises relational databases to text files in Azure Data Lake, only to load it later in a cloud data warehouse. To my observation that this is redundant and pointless, they answered that...
Atlanta MS BI and Power BI Group Meeting on December 3th

November 26, 2018 / No Comments »

MS BI fans, join us for the next Atlanta MS BI and Power BI Group meeting on December 3th, Tuesday at 6:30 PM at the Microsoft office in Alpharetta. I will discuss Power BI dataflows. A3 will sponsor the event. For more details, visit our group page and don't forget to RSVP (use the RSVP survey on the main page) if you're planning to attend. Presentation: Understanding Power BI Dataflows Date: December 3, 2018, Monday Time 6:30 – 8:30 PM ET Place: Microsoft Office (Alpharetta) 8000 Avalon Boulevard Suite 900 Alpharetta, GA 30009 Overview: If you're a business user, you might know about Microsoft Flow. If you're a BI pro, you might have used the SSIS Control Flow and Data Flow. But do you know about the Power BI dataflow? Data preparation is often the most labor-intensive component of an analytics project and getting this right is vital if the results are to be...
Dataflows: The Good, the Bad, the Ugly

November 16, 2018 / No Comments »

This blog is an update of my previous blog "Common Data Service for Analytics: The Good, the Bad, the Ugly". Things have changed in the past 6 months. The Common Data Service for Analytics is no more. It got superseded by Power BI dataflows, which Microsoft officially introduced at the PASS Summit last week. Think of dataflows as "Power Query in the Cloud". Dataflows is to self-service BI what ETL is to Power BI pros. A dataflow is a collection of Power Query queries, also known as entities, and it lives in a Power BI organizational workspace. For example, if I want to stage data from Salesforce, I can create a dataflow with as many entities as Salesforce tables I want to stage. Unlike Power Query, which can only output to a table in a data model, a dataflow saves its output as a pair of a CSV file (data)...