Posts

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

/0 Comments/in , /by

MS BI fans, join us for the next Atlanta MS BI and Power BI Group meeting on November 4, Monday, at 6:30 PM at the Microsoft office in Alpharetta. Andy Lawrence will share best practices for impactful Power BI Dashboards. CCG Analytics will sponsor the meeting. For more details, visit our group page and don’t forget to RSVP (fill in the RSVP survey if you’re planning to attend).

Presentation:Best Practices for Impactful Power BI Dashboards
Date:November 4, 2019
Time6:30 – 8:30 PM ET
Place:Microsoft Office (Alpharetta)
8000 Avalon Boulevard Suite 900
Alpharetta, GA 30009
Overview:Power BI is gaining momentum as a preferred tool for dashboards and interactive reports. Let’s revisit some best practices for dashboard development, such as:

·       The importance of form and function

·       Facilitating user adoption

·       Mistakes that everyone makes

·       Fast shortcuts for clean reports

·       Hidden settings that are lifesavers

·       Best Power BI updates of 2019

·       Live demo of a fast dashboard build

·       Questions

Speaker:Andy Lawrence is a senior Power BI consultant at CCG Analytics and the leader of the Tampa Power BI user group. He’s a Florida native and a proud UF Gator (MBA) and USF Bull (MIS). At CCG he provides guidance on data modeling, tabular environments, azure administration, DAX writing, T-SQL and data visualization best practices. All of which he can expand upon if you have questions during his presentation.
Sponsor:CCG specializes in deploying solutions that not only provide value to the business but are adopted by users ensuring accountability of the IT driven system.  Moving beyond reporting, our Business Intelligence solutions support data governance, quality and standardization across the organization and enable stakeholders with tools like predictive analytics, user-defined alerts, data mining, what-if analysis and visually appealing dashboards.
Prototypes with Pizza“Lineage view” by Teo Lachev

PowerBILogo

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

/0 Comments/in , /by

MS BI fans, join us for the next Atlanta MS BI and Power BI Group meeting on October 7, Monday, at 6:30 PM at the Microsoft office in Alpharetta. Qubole will show us how Presto, Azure Data Lake, and Power BI can be used to analyze Big Data. Qubole will sponsor the meeting. For more details, visit our group page and don’t forget to RSVP (fill in the RSVP survey if you’re planning to attend).

Presentation:Leveraging Power BI on Presto for the Azure Data Lake
Date:October 7, 2019
Time6:30 – 8:30 PM ET
Place:Microsoft Office (Alpharetta)
8000 Avalon Boulevard Suite 900
Alpharetta, GA 30009
Overview:Presto is a distributed ANSI SQL engine designed for running interactive analytics queries. Presto outshines other data processing engines when used for business intelligence (BI) or data discovery because of its ability to join terabytes of unstructured and structured data in seconds, or cache queries intermittently for a rapid response upon later runs. Presto can also be used in place of other well-known interactive open-source query engine such as Impala, Hive or traditional SQL data warehouses. Attend this event to learn:

·        Why Presto is better suited for ad-hoc queries than other engines like Apache Spark

·        How to jumpstart analysts across your organization to harness the power of your big data

·        How to generate interactive or ad hoc queries or scheduled reports using Presto

·        Real-world examples of companies using Presto

Speaker:Man Zhang is a Solutions Architect at Qubole. He has 20+ years in software systems architecture, development, and integration and 4+ years in Big Data architecture. https://www.linkedin.com/in/man-zhang-34a887/
Sponsor:Qubole delivers a Self-Service Platform for Big Data Analytics built on Amazon Web Services, Microsoft and Google Clouds. We were started by the team that built and ran Facebook’s Data Service when they founded and authored Apache Hive.  With Qubole, a data scientist can now spin up hundreds of clusters on their public cloud of choice and begin creating ad hoc and/or batch queries in under five minutes and have the system autoscale to the optimal compute levels as needed.  Please feel free to test Qubole Data Services for yourself by clicking “Free Trial”​ on the website.
Prototypes with PizzaTBD

DCI_PowerBI

Prologika Newsletter Fall 2019

/0 Comments/in , /by

090918_1951_DAXVariable1.pngWe all need to share. But until a couple of months ago, a Power BI training or assessment wouldn’t be complete unless I got hammered on the Power BI sharing limitations. Fortunately, Microsoft has addressed most of these and I have now a much better story to tell. And this is the subject of this newsletter.

Quo vadis, Power BI Sharing?

Power BI sharing road has been a long and winding one. I covered the gist in my blogs “Power BI Sharing is Getting Better“, “Power BI Sharing is Getting Better 2“, and “The Power BI Viewer Role“. The following table summarizes prior and standing sharing limitations. The Solution column lists the “fix” by Microsoft.

LimitationSolution
Workspace dependency on Office 365 groupsV2 workspaces don’t depend on O365 groups
No IT oversight on creating workspacesYou can now control who can create workspaces in the Power BI Admin center
Group membership limitationsV2 workspaces support all O365 group types
Coarse content access levelContributor and Viewer roles
No cross workspace sharingA dataset can be shared across workspaces
No data governanceA dataset can be promoted and certified
Power BI Premium sharing with viewers require report sharing or appsThe Viewer role supports sharing with viewers
Only one app supported per workspaceThe Viewer role deemphasizes apps
No nesting support (subfolders)

Sharing Best Practices

Given the current state of Power BI, I’d like to share some best practices for organizing and sharing content:

  1. Don’t use report and dashboard sharing as it can quickly turn into a maintenance nightmare.
  2. Create workspaces to reflect your organizational functional areas, e.g. Sales, HR, Finance. Unfortunately, workspaces still don’t support subfolders (Microsoft hinted that they are working on such a feature), so for now you must resort to a flattened list.
  3. Instead of individual user assignments, add users to security groups and then add these groups as members to the workspace. This way, when the user leaves and company or moves to another department, you only need to change the user’s group membership without making changes to the workspace security policy.
  4. Come up with data governance policy. The Power BI data governance story is work in progress, but you can set up some ground rules. For example, once a contributor believes that a dataset is ready for a broader consumption, he can promote the dataset. Then, IT can verify the dataset and certify it. Currently, Power BI supports certifying only datasets (reports and dashboards aren’t certifiable yet).
  5. This one is hard. Teach data analysts best data modeling practices. Instead of creating a dataset per report, they should create a data model that correctly represents their subject area. A data analyst should create the model once so it can support multiple reports. Once the dataset is published and certified, users can create their own reports.
  6. Although Microsoft is pushing apps very hard and adding some nice features, such as navigation, a standing limitation is that you can create only one app per workspace. So, you can’t publish multiple apps from a workspace, such as to share some reports with one group of users and another set with a different group. Instead, grant access directly to workspaces by using the Contributor and Viewer roles. The Viewer role lifts a significant limitation that forced you to use report/dashboard sharing or apps to share content out of a premium workspace with viewers. If you want to share the entire content of the workspace, you don’t have to use apps. Instead, you can simply add users or groups as viewers to the workspace.

Despite some long standing limitations, Power BI sharing is coming out of age. Follow the above practices and you’ll have now a much better way to organize and share content.


Teo Lachev
Prologika, LLC | Making Sense of Data
Microsoft Partner | Gold Data Analytics

logo

Atlanta MS BI and Power BI Group Meeting on September 10th

/0 Comments/in , /by

MS BI fans, join us for the next Atlanta MS BI and Power BI Group meeting on September 10, Tuesday, at 6:30 PM at the Microsoft office in Alpharetta. This is a meeting not to miss. A DAX founder, Jeffrey Wang (Principal Software Engineer Manager at the Power BI product group) is coming all the way from Seattle to share DAX best practices with our group! Captech Consulting will sponsor the event. For more details, visit our group page and don’t forget to RSVP (fill in the RSVP survey if you’re planning to attend).

Presentation:Common DAX Patterns
Date:September 10, 2019
Time6:30 – 8:30 PM ET
Place:Microsoft Office (Alpharetta)

8000 Avalon Boulevard Suite 900

Alpharetta, GA 30009

Overview:We will learn common DAX patterns by examining several issues frequently reported by DAX users through PowerBI customer support tickets. Users will be able to:

1.      Learn from mistakes made by other DAX users so you can avoid them in your own work.

2.      Learn the common techniques to debug DAX problems.

3.      Learn the best DAX patterns to solve common problems.

4.      Gain deeper understanding of DAX by learning relevant history and underlying design of the programming language.

5.      Ask me their own DAX questions

Speaker:Currently working as Principal Software Engineer Manager at Microsoft, Jeffrey Wang stumbled upon BI after the Y2K bubble burst, fell in love with the field, and stayed in the industry ever since. After I joined Microsoft Analysis Services engine team in 2004, I quickly discovered that working on the programming language is the best way to stay close to the end users, so I joined the MDX formula engine team. After shipping a couple of releases of SQL Server Analysis Services, I joined the committee that created DAX. Today I lead the development effort of the DAX engine and the modeling engine inside the Power BI product group.

DCI_PowerBI

Implementing Asymmetric Crosstab in Power BI

/0 Comments/in /by

A recent requirement called for an asymmetric crosstab report in Power BI. Transitioned to Adventure Works, the final report looks like this.

The Internet column shows the sales amount from FactInternetSales. Then, the matrix pivots on the BusinessType column in the FactResellerSales. Because, Internet sales don’t relate to BusinessType, it doesn’t make sense to pivot it. Instead, we want to show Internet sales in a single static column before the crosstab portion starts.

Implementing such a report in SSRS is easy thanks to its support of adjacent groups and static columns but not so much in Power BI. The issue is that Matrix would happily pivot both measures and the InternetSalesAmount would be repeated for each business type.

Fortunately, with some blackbelt modeling and DAX, we can achieve the desired effect. I attached the pbix file and here are the high-level implementation steps:

  1. Add a ReportCategory table (you can use the Enter Data feature), with a single column Category and two values: Internet and Reseller. Add a Category calculated column to FactInternetSales with a value of “Internet”. Add a Category calculated column to FactResellerSales with a value of “Reseller”. Create relationships FactInternetSales[Category]->ReportCategory and FactResellerSales[Category]->ReportCategory. These relationships are required because Matrix doesn’t support unrelated tables. Your model schema should look like this:

  2. Create a SalesAmount measure with the following formula:
     SalesAmount = IF(SELECTEDVALUE('ReportCategory'[Category]) = "Internet" && NOT ISINSCOPE('FactResellerSales'[BusinessType]),
     SUM(FactInternetSales[InternetSalesAmount]),
     SUM(FactResellerSales[ResellerSalesAmount]))

    The IF condition checks if the measure is under the Internet category. NOT ISINSCOPE(‘FactResellerSales'[BusinessType]) will return TRUE only for the total column in the Internet section. Otherwise, it will return the reseller sales. Because there are no reseller sales in the Internet category, only Internet sales will be shown. Note that SELECTEDVALUE will return FALSE for empty cells which is why I had to create the ReportCategory table.

  3. Add Matrix viz and bind Product[Color] to rows, ReportCategory[Category] and ResellerSales[BusinessType] on columns, and SalesAmount in Values. Expand the columns to the next level (to show the BusinessType level).
  4. In the Matrix format properties, go Subtotals, enable the “Per column” setting, and turn off the Category subtotal to avoid showing the reseller sales total twice.

Although Power BI has made great progress on the visualization side of things, it still lacks in flexibility. The two features that miss the most from SSRS are nesting visuals (to create repeat sections) and the flexible Matrix layout. However, with some black belt modeling and DAX, workarounds are possible.

High Memory Usage and Calculated Columns

/0 Comments/in /by

Scenario: A client reports a memory spike during processing. They have a Tabular semantic model deployed to Azure Analysis Services. They fully process the model daily. The model normally takes less than 50 GB RAM but during processing, it spikes five times and Azure Analysis Services terminates the processing task complaining that it “reached the maximum allowable memory in our pricing tier”. Normally, fully processing the model should take about twice the memory but five times?

high_memory_usageSolution: Upon expecting the model design, I discovered that the client has decided to add (many) calculated columns to the two fact tables in the model. Most of these columns are used to calculated variances to prior year. The formulas contain DATESYTD and other DAX date-related functions. After data is read, Tabular processes calculated columns, relationships and hierarchies. In this case, the spike was due to calculations involving large time ranges and ineffective DAX expressions. Converting these columns to measures resolved the issue.

As a best practice, abstain from using calculated columns (especially in fact tables). Make sure you understand the difference between measures and calculated columns (I cover this extensively in my latest book “Applied DAX with Power BI“). If you do need expression-based columns, such to materialize expensive calculations, consider defining them upstream, such as in SQL views or Power Query.

Gateway AD Lookups

/0 Comments/in /by

A data gateway is required for a cloud service, such as Power BI, to access on-prem data, either for live connections or for refreshing imported data. The gateway picked up an interesting feature to look up Active Directory on one property and return the value of another, which proved extremely useful for one client of mine.

Scenario: An enterprise client has adopted a hybrid architecture I set up for them, where Power BI reports connect directly to an on-prem Analysis Services Tabular model. The issue is that besides the regular network login, every user has a cloud identity (the employee number) to access cloud data sources. Before the gateway enhancement, this required explicit per-user mappings in the gateway data source. Imagine you must do this for thousands of users. and you’ll understand the maintenance issue they faced rolling Power BI across the enterprise.

Solution: Instead of explicit mappings, all they need to do now is configure the following two settings in the Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config file:

<setting name=”ADUserNameReplacementProperty” serializeAs=”String”>

<value>UserPrincipalName</value>

</setting>

<setting name=”ADUserNameLookupProperty” serializeAs=”String”>

<value>employeeNumber</value>

</setting>

When a Power BI user opens a report connected live to the on-prem Analysis Services Tabular model, the gateway receives the request mapped to the email that the user logs in Power BI. Then, the gateway queries the Active Directory to look up the user principal name (login) based on the employeeNumber which is what the Power BI login is. This query should complete fairly quickly so no worries about report queries. Make sure you restart the gateway after the configuration change.

AD lookups could save you configuring per-user mappings for live connections to on-prem data sources. Adam Saxton has a Power BI User lookup with the gateway using Active Directory video on this topic that goes into more detail.

The Power BI Viewer Role

/0 Comments/in /by

As I discussed in “Power BI Sharing is Getting Better” and “Power BI Sharing is Getting Better 2“, the Power BI sharing saga has been improving and Microsoft is closing gaps. As recently announced, Microsoft added an important Viewer role that grants view permissions to the workspace content.

The most important part in the announcement is:

“Viewer role requires a Pro license or for the content to be in Power BI Premium. If your workspace is in Premium, users with Viewer role who don’t have a Pro license can view the workspace in the workspaces list, navigate to the workspace, and view the content without getting a Pro Trial prompt.”

Therefore, the Viewer role lifts a significant limitation that forced you to use report/dashboard sharing or apps to share content out of a premium workspace with viewers. So, if you want to share the entire content of the workspace, now you don’t have to use apps. You can simply add users or groups as viewers to the workspace.

Speaking of apps and selective sharing, my joy will be complete once workspaces supports nesting (folders) with the ability to overwrite the securing permissions at a folder level so we can have SSRS-like folder-based security. This will allow you to organize content anyway you want (instead of resorting to a flatten list of workspaces) and grant selective permissions to different groups of users. Now I have a much better Power BI sharing story to tell!

Power BI Sharing is Getting Better 2

/0 Comments/in /by

We had to wait four years but here they are: Power BI shared datasets are here. This makes it a good time to revisit the remaining Power BI sharing limitations, which I listed in my previous blog on the same subject.

  1. No nesting support – Workspaces can’t be nested, such as to have a global Sales workspace that breaks down to Sales North America, Sales Europe, etc. You can’t organize content hierarchically and there is no security inheritance. Consequently, you must resort to a flattened list of workspaces. Microsoft has hinted about subfolders, but no further details are provided about how they will work and ETA.
  2. UPDATE 6/27: The new Viewer role supports sharing with viewers. Sharing with “viewers” – You can’t just add Power BI Free users to share content in a premium workspace. This would have been too simple. Instead, you must share content out with either individual reports/dashboards or apps. A Viewer role has been in the works to address this, but no official release date yet.
  3. One-to-one relationship with apps – Since for some obscure reason that only Microsoft knows, broader sharing requires apps, an app needs to be created (yet another sharing layer) to share workspace content to a broader audience. But you can’t publish multiple apps from a workspace, such as to share some reports with one group of users and another set with a different group. Hopefully, the forthcoming Viewer role will remove this limitation and de-emphasize apps, which in my opinion add unnecessary complexity without bringing much business value.

It’s nice to share and it’s good to see that Power BI is making it simpler but more is needed to bring sharing to where it should be.

Atlanta MS BI and Power BI Group Meeting on June 3rd

/0 Comments/in , /by

MS BI fans, join us for the next Atlanta MS BI and Power BI Group meeting on June 3, Monday, at 6:30 PM at the Microsoft office in Alpharetta. I’ll show you how to bridge the analytical and developer worlds by integrating Power BI with PowerApps. Eric Flamm will demonstrate SQL Notebooks in Azure Data Studio. Prologika will sponsor the event. For more details, visit our group page and don’t forget to RSVP (fill in the RSVP survey if you’re planning to attend).

Presentation:Bridge Analytics and Developer Worlds with Power Platform
Date:June 3, 2017
Time6:30 – 8:30 PM ET
Place:Microsoft Office (Alpharetta)

8000 Avalon Boulevard Suite 900

Alpharetta, GA 30009

Overview:One of the Power BI most prominent strengths is that it’s a part of the much broader Microsoft Power Platform and Data Platform that spans various on-premises and cloud offerings for data storage, processing, and analysis. Join this session to learn how to integrate Power BI with PowerApps and discover exciting new possibilities that redefine the meaning of a report and let you do more with less. During this entirely hands-on, almost no-slides session I’ll walk you through the steps to implement a popular report requirement for writeback, that Power BI doesn’t natively support.
Speaker:Through his Atlanta-based company Prologika (https://prologika.com), a Microsoft Gold Partner in Data Analytics, Teo Lachev helps organizations make sense of their most valuable asset: their data.  Teo has authored and co-authored several bestselling books on organizational and self-service data analytics (his latest is “Applied DAX with Power BI”), and he has been leading the Atlanta Microsoft BI and Power BI group since he founded it in 2010. Teo has been a Microsoft Most Valued Professional (MVP Data Platform) since 2004.
Sponsor:Prologika is one of the most trusted names in Data Analytics. Our clients, from small businesses to Fortune 100 enterprises, derive tremendous value from our services. Our mission is to help organizations make sense of data by applying the latest technologies for descriptive and predictive analytics and get actionable insights. Your organization will spend less time mining for information and be better equipped to make sound business decisions.
Prototypes with Pizza“SQL Notebooks in Azure Data Studio” by Eric Flamm

092417_1708_AtlantaMSBI1.png