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

Securing Power BI Report Pages

/0 Comments/in /by

Every now and then I get a question about how to secure Power BI report pages. A business analyst has created a multi-page report that requires limited access to some sensitive information in one or more report pages. I always cringe at this idea and recommend securing the data instead. That’s because report-level or dashboard-level security can be easily compromised. For example, if the user has permissions to use Analyze in Excel, they will surely bypass report security and get access to all the data. The same will happen if they use Report Builder or connect a third-party tool to the published Power BI dataset. Instead, you should apply row-level security (RLS) to protect the data. Currently, Power BI doesn’t let you secure report pages but if you must apply report or visual security, you have options:

  1. Break the report into two set of reports: one with open access and another with “secure” pages. Share the report with the secure pages with the people who can access it or publish it to a separate workspace.
  2. Add a hidden page-level or report-level slicer that filters on a DAX measure. The DAX measure can check the user identity using the USERPRINCIPALNAME() function, so the slicer doesn’t return any values if the interactive user is not authorized. Granted, a better (and preferred) way to achieve this could be to apply RLS but RLS will restrict data to all visuals where as a slicer can filter only selected visuals.

Filtering Power BI Visuals by Measure

/0 Comments/in /by

Power BI lets you filter the visual data by a measure, such as by a measure that dynamically calculates the customer’s rank. This is convenient but there are performance implications related to the way Power BI autogenerates the DAX query. If a visual has a visual-level filter on a measure, Power BI Desktop defines a variable table filter that includes all measures in the visual. In the following example, the visual filters on the IncludedInRank measure, but the filter table includes all measures in the visual:

VAR __ValueFilterDM1 =
FILTER (
KEEPFILTERS (
SUMMARIZECOLUMNS (
‘Customer'[PARENT_NAME],
‘Customer'[Label_DM],
‘Customer'[VP_NAME],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__DS0FilterTable4,
“Vol_Gr_WKND”, ‘NRT_PRR_Parent'[Vol_Gr_WKND],
“Vol_Gr_WE_Mon”, ‘NRT_PRR_Parent'[Vol_Gr_WE-Mon],
“Vol_Gr_WE_Tue”, ‘NRT_PRR_Parent'[Vol_Gr_WE-Tue],
“Vol_Gr_WE_Wed”, ‘NRT_PRR_Parent'[Vol_Gr_WE-Wed],
“Vol_Gr_WE_Thu”, ‘NRT_PRR_Parent'[Vol_Gr_WE-Thu],
“Vol_Gr_WE_Fri”, ‘NRT_PRR_Parent'[Vol_Gr_WE-Fri],
“WTD_Gr_ptc”, ‘NRT_PRR_Parent'[WTD_Gr_ptc],
“IncludedInRank”, IGNORE ( ‘NRT_PRR_Parent'[IncludedInRank] )
)
),
[IncludedInRank] = 1
)

As a result, these measures are evaluated twice: once in the filter table and twice in the main query (EVALUATE clause). This could definitely impact performance if the measures are slow to begin with. While waiting on Microsoft to provide more control over the autogenerated query, one approach to mitigate the performance impact is to use the Power BI-provided TOPN filter. In this example, instead of sorting and filtering on IncludedInRank to find the top N customers, you would apply a TOPN filter on Customer[Parent Name] based on the IncludedInRank measure. When TOPN is used, the autogenerated filter table will filter only on the measure used for TOPN, thus avoiding evaluating the rest of the measures.

Staying Cool

/0 Comments/in /by

A discussion overheard between two attendees at SQLSaturday yesterday:

  • What is this Tabular thing?
  • Like a cube but better.
  • How’s it better?
  • It’s in memory.
  • I wonder if it can improve the performance of my report…
  • It should, it’s also in the Cloud.

Note to myself. Use “in memory” and “cloud” more often.

Presenting at SQL Saturday Atlanta (BI Edition)

/0 Comments/in , /by

SQL Saturday Atlanta (BI Edition) will be held on October 19th. I’ll present a “Power BI Pro, or Premium: that is the question” strategy-oriented session at 12:30.

One question that always pops us during my Power BI assessments for companies planning to adopt Power BI is which SKU to buy. The choice might seem obvious to your organization, but there is much more below the surface. Join this session and learn important considerations that might influence your decision, including:

•    Licensing cost

•    Hosting organizational semantic layers

•    External reporting

•    Features

•    On-premises reporting

 

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