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.

 

 

 

 

 

 

 

 

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

Creating a Business Intelligence Solution with D&B Business Data & Excel 2013 BI Tools

This series of video sessions will demonstrate how to create a business intelligence solution with sets of business data from multiple sources.  In the first session, I import business records from 5 different D&B data feeds using the Windows Azure Marketplace with desktop BI tools in Excel 2013 ProPlus.  In future sessions, I will combine public data sets, use features of Power BI and migrate to an enterprise scale BI solution with SQL Server, SSAS tabular and SharePoint.

D&B data fees in the Windows Azure Marketplace can be used to get detailed, current demographic and financial information about specific businesses or companies in different regions and categories.  These business records can be used for things like market analysis, sales, verification and research.

Session 1 – Getting started (48:00)
Analyzing Dunn & Bradstreet business records data from the Windows Azure Marketplace
using Excel 2013 Power Query, Power Pivot and Power View to create a basic dashboard

To watch this video with more playback controls, you can also view it on ScreenCast here.

Session 2 – Enhancing the Solution (32:00)
Combining Dunn & Bradstreet Customer Records with Public & Enterprise Data
Geographic visualization with Power Map, publishing to Power BI and using Q&A to search a model with natural language

FirstFrame

 

Digg This

Book Review of Microsoft Tabular Modeling Cookbook

by Paul te Braak, from Packt Publishing
on Amazon

Good technology books usually come in one of two forms.  Some of the books on my shelves go deep technically, contain useful tidbits of code but read like the phonebook.  Just a few are interesting and insightful.  This book is a rare gem that does both.  Paul te Braak is well-known in the Business Intelligence community for his expertise and contributions and he delivers a unique guide that starts with the basics and proceeds to cover all of the essentials with depth and practical examples to solve some challenging business problems.

You might expect a book that introduces fundamental concepts to gloss-over advanced topics and avoid complex applications.  This book covers the essentials of data modeling and analysis with Excel and Power Pivot in language that is plain and easy to understand but it doesn’t stop with the basics.  It covers practical and useful applications without insulting the reader’s intelligence.  As an experienced data modeler, I found several useful techniques and new methods to use the tools and language.  Paul’s coverage of practical techniques spans the spectrum of business applications and product features.  This is a rare book that is not only a good tutorial with many hands-on examples that can be repeated by the reader but it’s also a great reference of useful techniques and code samples.

Highlights include:

  • The integration of Excel features such as ranges, tables, pivot tables and pivot charts with the additional features of Power Pivot and Power View that extend and enhance these capabilities
  • Examples and instructions are directed at Excel 2010 users and the author compares some the different features in Excel 2010 and Excel 2013.
  • Fundamentals of the DAX calculation language
  • Importing data as text, different date formats and implied data type columns
  • Beyond the basics, a schema.ini file is used to define column data types
  • Importing data from a database, working with database tables, views and queries, managing connections and challenges encountered running the table import wizard multiple times
  • Data feeds using OData and using a Reporting Services report to provide a data feed
  • Decisions a designer makes to enable the user’s experience when browsing a model.  This includes sorting values, navigating hierarchies that enable drill-down interaction. 
  • DAX “X” functions (SUMX, MINX, etc.) to perform row-level aggregation
  • Working with parent-child hierarchies using specialized DAX path functions. 
  • Advanced browsing features, adjusting pivot table options to optimize the user experience
  • Building and using KPIs and using alternate table relationships
  • Time calculations and date functions.  This chapter covers running totals and totals to date
  • Date part aggregate functions (MTD, YTD, etc.),
  • Essential data math and comparisons
  • LastYear and PriorPeriod functions, TotalYTD
  • Manufacturing calendar, working with “445” dates
  • Creating a dynamic relative time measure, using a shell dimension table
  • Using DatesBetween to show the average value for the past 10 days
  • Apply advanced modeling technique to bin, sort and rank values for reporting
  • Expand concepts introduced in chapter 3, using the DAX “X” functions to perform row iteration in advanced financial applications
  • Defining and working with many-to-many relationships.  This is often no trivial task to completely understand many-to-many relationship requirements and to apply a working solution that provides the intended results
  • Addressing inventory and stock-keeping challenges
  • Conditional aggregation at different levels
  • Budgeting and forecasting vs actuals
  • Programming Excel to enhance the users experience
  • Excel VBA event programming to respond to slicers
  • Using cube functions
  • Interacting with charts and slicers
  • Building solutions for the enterprise
  • Using the SSDS Tabular designer
  • Migrating Power Pivot models to Tabular server solutions
  • managing connections, implementing impersonation, managing security
  • Using roles and perspectives
  • Generating and using XMLA script
  • Defining and implementing role-based, dynamic row filtering
  • Performing currency conversion
  • Managing and optimizing  a Tabular solution
  • Deployment scenarios
  • Using SSDT to deploy and process models
  • Using the SSAS Deployment Wizard
  • Generating and using deployment scripts
  • Creating and managing partitions
  • Scheduling and executing processing tasks
  • Utilizing DirectQuery for real-time data results
  • Using Profiler to troubleshoot and optimizing a model
  • Querying a model using DAX
  • comparison of similar and different concepts in multidimensional and Tabular semantic models
  • Query with MDX
  • Query with DAX
  • DAX tools and debugging techniques
  • Using DAX query techniques to simulate SQL query operations
  • Column aliases, joins, filters, deriving tables
  • Samples and top ranked results
  • Using Power View to present results and visualize data
    • Essential design features
    • creating a table report
    • using a matrix to pivot results
    • time and data filters
    • advanced filters
    • creating charts
    • bar charts
    • stacked charts
    • cluster chart
    • using tiles to navigate sectioned results
    • using images
    • managing tables with default field sets
    • table behavior and cards
    • data categories and visual behaviors
Digg This