Power BI for Grownups

The message from the Microsoft Business Applications Summit this week was very clear: Power BI is growing up.

We have known for a while that Power BI is a great front-end tool for enterprise-scale tabular and multidimensional models.  Power BI Premium will now allow almost limitless data scale using extremely large datasets by leveraging composite models that employ both DirectQuery and VertiPaq cached mode tables.  This feature shatters the previous barriers where data models could only support either DirectQuery or cached mode (imported data).  Additionally, Microsoft publicly unveiled plans to add IT developer centric features to Power BI Desktop for managing large models, version control and application development lifecycle.

These announcements leave many open questions about how Microsoft will continue to support self-service and enterprise customers with one tool and about the long-term future of SSAS and Visual Studio-based SSDT. At this point, none of these tools are on the chopping block, but it is clear that James Phillips and his team have significant momentum and are not slowing down to wait for other products to catch-up.

In this interview with Christian Wade, he describes the benefits of this new capability.  Christian is Group Program Manager on the Power BI and Analysis Services product team with focus on data model development and scalability features.

Composite Models & Aggregations

In the opening keynote and again in his sessions, Christian demonstrated Power BI reports on the taxi driver activity database with over a trillion rows of raw data.  The larger dataset was in a Spark cluster, accessed using DirectQuery.  Aggregated tables were stored in the in-memory model using the new composite model feature.  As the data was explored in report visuals, the Power BI engine would seamlessly switch from tabular in-memory aggregate tables to DirectQuery source data in order to return low-level details.  Composite models will allow mashing-up imported database and file-based data with an DirectQuery.

There are limits and complexities with these new features.  You cannot mashup imported tables in a Power BI model based in a direct connection to SSAS, but enterprise-scale features in Power BI arguably may not steer a solution architect to select SSAS over Power BI for serious data modeling.  With incremental data refresh, large model support, row-level security and many other “big kid” features, Power BI might be a preferable choice.  I’m not ready to rule-out Analysis Services as the better option for most enterprise solutions – at least not in the near future, but Power BI is definitely heading in that direction.

Power BI Desktop Features for Developers

News Flash: Power BI Desktop will be the primary tool for serious, enterprise data model development.  A bunch of features are in the works and will arrive soon for preview.  The obvious question is what will happen to SSDT for Visual Studio and the answer is that it’s not going away but the product team is putting their energy into enhancing Power BI Desktop for now.  It will likely have some kind of “Beginner/Advanced” switch to simplify things for the novice user and isolate advanced or complicated, developer-centric features.

Here are a few screen captures of the forthcoming features:

Schema Compare & Model Integration

Like the BISM Normalizer tool created by Christian Wade, there will be a fully-supported compare and merge tool baked into Desktop.  The early build is branded with components from MAQ Software.

image

Relationship Diagram Designer, Properties Pane and Application Lifecycle Management Features

A new-and-improved diagram designer is imminent.  It will allow large models to be more effectively managed and displayed.  You will have the ability to save individual views of the diagram with selected, subject-area tables.

image

Like in Visual Studio, properties can be set and managed in a Properties sheet window.  Support for standard SSAS features like display folders is coming and can be managed here.

image

Business Applications Release Notes

With the velocity of features and updates, a comprehensive set of release notes are available here.  This document will be updated frequently with the revised roadmap, feature history and announcements.

APIS and Powershell CmdLets for Power BI Administration

At the MVP Summit in March Kay Unkroth promised API endpoints and Powershell CmdLets for managing all aspects of Power BI.  He even did a dance and sang a song to make the announcement memorable.  This week, the product team delivered what they promised, with the announcement official here.

The following features and capabilities are on the delivery short list coming in the next 2-3 months:

Power BI Service

The new, “modern workspace” eventually replacing current workspaces integrated with Office 365 groups.  The new app workspace will no longer be bound to an Office 365 Group. The feature will be optional at first, but will replace current workspaces soon.

Now, users can be: Member, Admin, Contributor

Can add groups to workspace

Premium capacity tenants can have nodes in different regions

Admins will have visibility and access to all objects and resources

Microsoft Business Applications Summit

Keynotes and Featured Sessions from the Microsoft Business Applications Summit are here.

SQL, M or Dax? – part 2

[ Related posts in this series: SQL, M or DAX: When Does it Matter? SQL, M or Dax? – part 2 SQL or M? – SSAS Partitions Using SQL Server Table-Valued Functions (UDFs) SQL or M? – SSAS Partitions in Power Query/M ]

This is a post about a post about a post.  Thanks to those of you who are entering comments in the original May 12 post titled SQL, M or DAX?  This is a popular topic. And thanks to Adam Saxton for mentioning this post in his Guy in A Cube Weekly Roundup.

This is a HUUUUGE topic and I can tell that I’ve struck a chord with many BI practitioners by bringing it up.  Please post your comments and share your ideas.  I’m particularly interested in hearing your challenging questions and your thoughts about the pros-and-cons of some less-obvious choices about whether to implement transformations & calculations in SQL, M or DAX.

This week, I have had engaging conversations on this topic while working on a Power BI consulting project for a large municipal court system.  As a consultant, I’ve had three weeks of experience with their data and business environment.  The internal staff have spent decades negotiating the intricacies and layers upon layers of business process so of course, I want to learn from their experience but I also want to cautiously pursue opportunities to think outside the box.  That’s why they hired me.

Tell me if this situation resonates with you…  Working with a SQL Server database developer who is really good with T-SQL but fairly new to Power BI & tabular modeling, we’re building a data model and reports sourced from a line-of-business application’s SQL Server database.  They’ve been writing reports using some pretty complicated SQL queries embedded in SSRS paginated reports.  Every time a user wants a new report, a request is sent to the IT group.  A developer picks up the request, writes some gnarly T-SQL query with pre-calculated columns and business rules.  Complex reports might take days or weeks of development time.  I needed to update a dimension table in the data model and needed a calculated column to differentiate case types.  Turns out that it wasn’t a simple addition and his response was “I’ll just send you the SQL for that…you can just paste it”.  The dilemma here is that all the complicated business rules had already been resolved using layers of T-SQL common table expressions (CTEs), nested subqueries and CASE statements.  It was very well-written SQL and it would take considerable effort to re-engineer the logic into a dimensional tabular model to support general-use reporting.  After beginning to nod-off while reading through the layers of SQL script, my initial reaction was to just paste the code and be done with it.  After all, someone had already solved this problem, right?

The trade-off by using the existing T-SQL code is that the calculations and business rules are applied at a fixed level of granularity and within a certain business context.  The query would need to be rewritten to answer different business questions.  If we take the “black box” approach and paste the working and tested SQL script into the Power Query table definition, chances are that we won’t be able to explain the query logic in a few months, after we’ve moved on and forgotten this business problem.  If you are trying to create a general-purpose data model to answer yet-to-be-defined questions, then you need to use design patterns that allow developers and users to navigate the model at different levels of grain across different dimension tables, and in different filtering contexts.  This isn’t always the right answer but in this case, I am recommending that we do as little data merging, joining and manipulation as possible in the underlying source queries.  But, the table mapping between source and data model are not one-to-one.  In some cases, two or three source tables are combined using SQL joins, into a flattened and simplified lookup table – containing only the necessary, friendly-named columns and keys, and no unnecessary clutter like CreatedDateTime, ModifiedDateTime and CreatedByUser columns.  Use custom columns in M/Power Query to transform the row-level calculated values and DAX measures to perform calculations in aggregate and within filter/slicing/grouping context.

I’d love to hear your thoughts and ideas on this topic.

 

 

 

 

 

 

 

 

Power BI Global Hackathon Contest Results

The results of last month’s Power BI Global Hackathon are in! The Hackathon was facilitated by our our PUG here in Portland with the goal of welcoming global contenders in subsequent contest. Five teams entered the contest using publically-available data to visualize and tell data stories using our favorite data analysis platform.  Congratulations to Xinyu Zheng and Ron Barrett for winning the challenge with their entry, analyzing Yelp restaurant star ratings.  These were all great entries and you can view the contest results in the Power BI report below.

image  image  image

Here are the published projects that were entered in the Hackathon:

Xinyu and Ron analyzed ratings from nearly 1200 restaurant Pittsburgh, Phoenix and Las Vegas.  Results compare ratings and reviews by restaurant and food categories, sentiment and key phrases in the review comments

image

I loved the creativity of this solution from Jeremy Black and Kirill Perian who analyzed alcohol sales statistics using infographics and bookmarks to switch out visuals on the same page.  The presentation concludes on the last page of the report with an auto-advancing variation of “100 Bottles of Beer on The Wall”.  Nice touch.

image

I’m admittedly a bit biased because this was my design, with a lot of help from Brian, Ron and Greg.  We used a series of tables to prompt a user for Power BI solution business requirements and recommend fitting solution architectures and components.  We pushed some practical and technical limits in our project and I’ll write a separate post about it.

image

This entry from Ron Ellis Gaut is a nice, clean orchestration of county health data, measuring health and comparing personal well-being and program efficacy.

image

The entry from Daniel Claborne emphasizes machine learning predictions performed with R Script, commonly used in data science.  He actually includes the annotated code and explains the technique and the approach using training and prediction data sets.

image

The Portland Power BI User Group was one of the first and continues to be one of the most active in the international community.  We meet on the 4th Wednesday evening every month in Beaverton, Oregon. Today there are many active PUGs all over the world.

 

Keeping Up with Power BI – A Never Ending Story

This first week of the new year has been a lot of housecleaning for me (literally and figuratively…  my office desk and cabinet will be clean by the end of the day!).  Three years into teaching classes and workshops on being productive with Power BI, it continues to be a product requiring a lot of work to stay current.  New features are introduced in every Power BI Desktop monthly update; not to mention updates to the cloud service and on-prem server.  You would have to be a child of the 80s to get the Never Ending Story reference.  Otherwise, it’s just a really bad flying dog – and pop song, which are both hard to explain, so we won’t.  The point is that it’s an ongoing effort to keep skills, courseware and presentation material up-to-date.

Image result for never ending story

If you’re like me, sometimes all these updates can be a bit of a distraction (we’re talking about Power BI again – not the dog, movie or song… case in point).  I’m excited by the continual innovations and improvements to Power BI.  However, the foundational rules of good design don’t really change that much.  Effective data curation, correct modeling and good core visualization design are as critical as ever.  The trick is to know which new features may be used to improve foundational design and which ones you can leave as icing on the cake for minor enhancements.  Updating courseware and workshop labs seems to be a never ending task and I’m hard at work revising older material and adding new content to prepare for new events this year.  An important topic I will continue to revisit this year is how Power BI is used along with other Microsoft tools to create different types of solutions.  I’m working on a new presentation to describe all of the incarnations of Power BI, used to deliver everything from self-service desktop reports – all the way up to enterprise-scale solutions for corporate users with governed and secured data – and most everything in-between.

The first workshop of the new year will be a one day preconference before the Redmond SQL Saturday, on Microsoft campus Friday, February 9.  You can sign-up for this event here.  I’m working on a few others in the following months and will update this and other blog posts when they are scheduled.  I hope to see you there.  You are welcome to send me questions or suggestions about specific topics of focus.  Just add a comment to this post, or reach me through Twitter or LinkedIn.

image

 

Implementing Row-Level Security in Power BI

The best method to implement row-level security in a published Power BI model or SSAS Tabular model consumed from the Power BI service will depend to some degree on your data structure and requirements.  The method I demonstrate here is one of the most flexible approaches and one that I commonly use in my projects. Continue reading