DAX, Microsoft Technologies, Power BI, SSAS, Uncategorized

Creating a Matrix in Power BI With Multiple Values on Rows

This week I was asked to create a matrix in a Power BI report that looks like this:

Matrix with Values on Rows
Matrix with Values on Rows (numbers faked to protect the innocent)

To my surprise, Power BI only lets you put multiple values on columns in a matrix. You can’t stack metrics vertically. Note: this is true as of 8 Jan 2016 but may change in the future. If you agree that this should be a feature in Power BI, please make your voice heard and vote for this idea on the Power BI forum and encourage others to vote for it as well.

Since I needed to finish this report, I got a little creative with a disconnected table and some DAX to implement a workaround in my tabular model.

The Disconnected Table

I created a table called Row Order in my tabular model using the following SQL Query as a source.

select 1 as [Row Order], 'Lost Customers' as [Customer Metric]
union all
select 2 as [Row Order], 'Inactive Customers' as [Customer Metric]
union all
select 3 as [Row Order], 'New Customers' as [Customer Metric]

I set the Sort By Column property on the Customer Metric column to Row Order and hid the Row Order column.

Calculated Measures

I already had the base metrics for my matrix:

  • Lost Customers – Week (customers considered lost in the current week, based upon the selected date)
  • Lost Customers – Month (customers considered lost in the current month, based upon the selected date)
  • Lost Customers – Week (customers considered lost in the current month, based upon the selected date)
  • Inactive Customers – Week (customers considered to have moved to inactive status in the current week, based upon the selected date)
  • Inactive Customers – Month (customers considered to have moved to inactive status in the current month, based upon the selected date)
  • Inactive Customers – Quarter (customers considered to have moved to inactive status in the current quarter, based upon the selected date)
  • New Customers – Week (customers who have made their first purchase in the current week, based upon the selected date)
  • New Customers – Month (customers who have made their first purchase in the current month, based upon the selected date)
  • New Customers – Quarter (customers who have made their first purchase in the current quarter, based upon the selected date)

As a side note, when creating time-based measures I prefer to create the measure such that it accepts a date and then filters off of that rather than assuming everyone wants to see the current week/month/quarter. I then add an Is Today field on my date table so if someone does want to see the current week they easily select that in a filter on this field. This means that I can see lost customers for any week, not just the current. Maybe I want to filter my dashboard to the end of December. If I had just created a measure that assumed I wanted last month, I wouldn’t be able to just change my date filter and get the data I want.  As another example, lots of people create a metric to calculate sales in the last 6 weeks. But many people assume they want the last 6 weeks from today rather than allowing the user to choose a date and then calculating sales in the 6 weeks leading up to that day. I much prefer the more flexible calculation.

But having this flexibility in my calculations meant that I couldn’t just put some calculated column for time period in my date dimension. And even if I had made the assumption that I wanted to see the data as of today, it would probably be a separate table or a many-to-many relationship (e.g., 5 Jan 2016 would be in the last week, month, and year).

With my new disconnected table added, I just needed to add a few more measures. First, I needed something to check which customer metric I should be showing:
RowOrder:=Max('Row Order'[Row Order])

I hid the RowOrder measure. Then I created one measure for each time frame (week/month/quarter) that would select the correct measure based upon the Customer Metric on that row.
Weekly Customer:=IF(HASONEVALUE(Row Order[Row Order]), SWITCH([RowOrder],1,[Lost Customers - Week], 2,[Inactive Customers - Week], 3,[New Customers - Week],BLANK()), BLANK())
Monthly Customers:=IF(HASONEVALUE(Row Order[Row Order]), SWITCH([RowOrder],1,[Lost Customers - Month], 2,[Inactive Customers - Month], 3,[New Customers - Month],BLANK()), BLANK())
Quarterly Customers:=IF(HASONEVALUE(Row Order[Row Order]), SWITCH([RowOrder],1,[Lost Customers - Quarter], 2,[Inactive Customers - Quarter], 3,[New Customers - Quarter],BLANK()), BLANK())

Power BI Matrix

I opened my report on Power BI.com and created my matrix.

I put Customer Metric in Rows and Weekly Customers, Monthly Customers, and Quarter Customers in Values. Then I filtered my table to As of Today = “As Of Today”.

PBI Matrix
Fields List for Power BI Matrix

That gives me the matrix I wanted to create. I don’t love that I had to add the extra table, but it works for now until I can achieve this natively with Power BI.

Power BI Matrix
Power BI Matrix using disconnected table and calculated measures (numbers changed to protect the innocent)



Webucator Made a Video of My Blog Post

Back in July, I wrote a blog post about My Favorite BIDS Helper features for SSAS development. Webucator contacted me about creating a video based upon it, and it’s now available.  They are doing a free series called SQL Server Solutions from the Web where they highlight different SQL Server solutions found on blog posts around the web, and I’m pleased and honored that my blog is included.

Webucator provides technical and business training via private classes for your organization, public classes in a live virtual environment, and self-paced online programs. They are a Microsoft Certified Partner for Learning Solutions, and they offer several classes on SQL Server, including SSAS, SSIS, and SSRS.

Without further ado, here’s the video for My Favorite BIDS Helper Features for SSAS Development:

You can check out another blog post that Webucator made into a video here. The post by Koen Verbeeck is a great explanation of why it’s important to be aware of the defaults in T-SQL windowing functions.

BIDS Helper, Microsoft Technologies, SSAS

My Favorite BIDS Helper Features for SSAS Development

Bill Fellows and I presented Somebody Got BIDS Helper in My Data Tools at Mile High Tech Con in Denver last weekend, and it reminded me how much I love BIDS Helper.  I use it to develop all of my SSIS and SSAS projects, but I realized I haven’t blogged much about it.  So here are my favorite BIDS Helper features for SSAS development. If you haven’t tried BIDS Helper yet, I highly recommend it.  It’s free and there is great documentation for it, so it’s easy to get started.

Printer Friendly Dimension Usage for SSAS Multidimensional

Printer Friendly Dimension Usage provides you with a nice Bus Matrix for your documentation or design reviews. You can choose between a summarized view and a detailed view.  When I have clients that I know will do a lot of ad hoc reporting in Excel using their cube, I like to create an Excel workbook with a data glossary, summary bus matrix, and a few pre-populated pivot tables that can be used to answer the most common questions.  With BIDS Helper 1.6.5 or later, I no longer have to create my bus matrix by hand. When you click the Printer Friendly Dimension Usage button, it will ask if you want the detailed or summary version.


The report will open up in a new window, and you can choose to print it or export to Word, PDF, or Excel.

Bus Matrix report

Tri-State Perspectives and Deploy Perspectives for SSAS Multidimensional

The Tri-State Perspectives and Deploy Perspectives go together nicely. Tri-State Perspectives put a red box around any dimension or measure group in which not all children are included in the perspective. This can be very helpful as you update and enhance your cube.  New measures or dimension attributes are not added to perspectives by default when they are added to a cube, so this is a great way to double-check that you have added any new fields to the appropriate perspectives before you re-deploy.



If you need to make changes only to your perspectives (perhaps you realized you forgot to add a new field to a perspective), the Deploy Perspectives functionality allows you to deploy only the perspectives rather than the entire cube. This feature will delete any perspectives on the destination database that aren’t in the source in addition to deploying perspectives.


Tabular Actions Editor

Tabular Actions Editor fills a feature gap in the Tabular SSAS world.  There is a nice UI for creating drillthrough actions for Multidimensional Analysis Services. By default, tabular models have drillthrough actions that simply contain the fields in the underlying fact table for that measure, which isn’t very useful or friendly to your users since it is mostly dimension keys and a few aggregatable values.


Without BIDS Helper, the only way to alter that behavior is the manually change the .bim file, which can completely corrupt your tabular model if you aren’t careful. The Tabular Actions Editor provides a nice UI to create actions in a manner similar to multidimensional SSAS development so you can create a helpful detail data set that is analytically relevant.  With BIDS Helper you can also add actions other than drillthroughs, such as go to Url or go to report. With drillthrough actions, you choose:

  • action type
  • target type
  • target measure group
  • default setting
  • maximum number of rows
  • method of invocation
  • perspectives in which it should be available.


tabular actions editor

There are lots of good examples in the documentation.  You will also notice a warning in the documentation:

Warning: While actions work in Tabular models, they are not officially supported by Microsoft. If you encounter a bug in how Tabular handles actions and open a support case, Microsoft may not provide support.

I have not run into a situation where this has been an issue, but I wanted to make sure I called this out so you knowingly accept the risks.

I love BIDS Helper

If you can’t tell, I’m a fan of BIDS Helper.  I like it so much that I recruited a friend to help me create and deliver a presentation about it.  If you are developing in SSAS or SSIS and you aren’t familiar with BIDS Helper, I encourage you to check it out.  I believe is it one of the best free tools for MSBI developers available today.

Excel, Microsoft Technologies, Power View, SSAS

Create a Power View Sheet Connected to an SSAS Tabular Model Without SharePoint

I have created Power View reports based upon SSAS Tabular models many times, but I typically go through SharePoint to get my data connection from a BISM connection file. I am now working on a project where I need to create Power Views connected to a tabular model without using SharePoint.  The way to do this was not immediately obvious to me, so I thought I would share this information. If you click Power View on the Insert Tab, you will get the following message: “Power View needs data to work with. Select a range of cells that contain data and then on the Insert tab click Power View.” Excel is assuming you want to you Power Pivot as your data source.

Instead, add a new data connection to your workbook that points to an SSAS Tabular model.

  1. On the Data tab, Choose From Other Sources -> From Analysis Services.
  2. When the dialog box appears, enter the server name and choose Windows Authentication. Then click Next.
  3. Select the appropriate database and perspective. Click Next.
  4. Update the file name and friendly name, if desired. Click Finish.
  5. Now that you have your data connection in your workbook, an Import Data dialog box will appear. Choose Power View Report and click OK.

A Power View sheet will be created in your workbook.  If you need another Power View sheet:

  1. Go to a regular Excel worksheet (not a Power View sheet).
  2. Click on the Existing Connections button.
  3. Double-click on the appropriate connection in the workbook.
  4. Choose Power View Report. Click OK.

The Office help documentation for this topic is here.

I also made a quick video that shows me creating two Power View tabs connected to an SSAS tabular model in an Excel workbook.

MDX, Microsoft Technologies, SSAS

Retrieving Lowest Level Hierarchy Members and Leaves in MDX

The Original Answer

I was answering questions on Stack Overflow when I came across a question about getting the last level of a hierarchy in MDX when you don’t know how many levels there will be. SSAS multidimensional allows for parent-child hierarchies without having to define a maximum number of levels.  A common use case for this is an employee reporting structure. You can create the same type of functionality is SSAS Tabular, but you have to build a flattened out structure and relate each level through DAX so there will always be a set number of levels even if they aren’t all populated.

This question was fun to answer because it can be done fairly simply and elegantly with MDX functions. I can get the members of the lowest level of the hierarchy with this:


The Levels function can take a level number or name as the argument. [Hierarchy_expression].levels.count gives me the number of levels. I have to subtract one from that number when I supply that to the Levels function because it uses zero-based positioning (i.e. the 6th level is level 5).

I can accomplish the same thing using the Descendants function.

DESCENDANTS([Dimension].[Hierarchy].[All], ([Dimension].[Hierarchy].Levels.Count - 1), SELF)

I am giving the Descdendants function the distance as my second argument. I use the All member for the member expression so any descendant in the hierarchy could be returned.  Then you’ll see the same expression that I used above.  This time, I am subtracting one because this number represents the distance (number of levels) from the specified member. The description flag allows me to specify which descendants should be returned. It is optional and defaults to SELF if you leave it out, but I included it just to be explicit. It means that I am only returning the descendants on the level I specified.

The More Probable Answer

Once I had my answer all written up, I realized that I was answering the question that was asked, but I wasn’t sure that is what they really needed.  If you have a parent-child hierarchy, the bottom level might not mean anything in particular.  If the hierarchy is ragged, you can have leaves at various levels, so there would have to be a very interesting and specific question asked that would use the bottom level members as the answer.  I decided it was just as likely that the question might really be asking for all of the members that have no children (the leaves). I can use the Descendants function to answer this question as well.

DESCENDANTS([Dimension].[Hierarchy].[All], , LEAVES)

I once again supply my hierarchy expression.  I leave the level out and feed it a empty value and specify the description flag is LEAVES. This assumes that the cube is built with the HIDEMEMBERIF property set so that members are not repeated as children of themselves and there are no blank members.

Transitioning from thinking in T-SQL to thinking in MDX can be difficult, and sometimes the original poster doesn’t quite understand the way MDX works.  As I answer questions on Stack Overflow, I try to ask clarifying questions to get a better understanding of the reason for the question.  Many times someone will ask how to build a specific calculation, and other members will work with them to understand why they want to do it in order to provide a better answer.  I do this a lot at client sites as well.  I can save information workers lots of time by asking them what their end goal is rather than simply delivering an intermediate step when building their BI solution, providing them a complete and sometimes more accurate answer to their common analytical questions.

Excel, Microsoft Technologies, Power BI, Power Pivot, Power View, SSAS, T-SQL


I’ve had several occasions to use OPENROWSET recently in T-SQL.  Although you can use it as an ad hoc alternative to a linked server to query a relational database, I’m finding it useful to get data from other sources. I’ll go into details of how to use it, but first I would like to acknowledge: OPENROWSET is not the answer to everything.  Sometimes you should use a linked server or SSIS or some other method.  I had some specific cases where I felt OPENROWSET made more sense for my task.

Importing Data from an Excel Spreadsheet

Technical need: I had some data that needed to be validated before importing into SQL Server from Excel as part of an upload process in an application.  If it didn’t pass validation, I did not want to import the data. Rather than use SSIS to import the data into a table and then check it, I used OPENROWSET to pull it directly from the Excel file into a temp table, ran all of my validation checks, and then imported the data using SSIS all within a stored procedure that was called by an application.  Field and table names have been changed to protect the innocent.

The stored procedure:

@datafile nvarchar(127) 
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
        @sql nvarchar(max)
    ,   @rowcount int
        @rowcount = 0

    DECLARE @FileExists int

        EXECUTE master.dbo.xp_fileExist @datafile, @FileExists out
        -- Value of 1 = file exists
        -- Value of 0 = file doesn't
        IF @FileExists = 0
            CONCAT('The file ', @datafile, ' could not be found.') 
                                                   AS reasontext
        ,   NULL AS field2
        ,   NULL AS field3;            
            RETURN -1;
            ERROR_MESSAGE()  AS reasontext
        ,   NULL AS field2
        ,   NULL AS field3;
        RETURN -1;

 TRUNCATE TABLE dbo.finaldest;
    IF OBJECT_ID('tempdb..##tempf') IS NOT NULL
        DROP TABLE ##tempf;

--Create temp table to hold values from Excel file
Create table ##tempf (
Field1 [nvarchar](255),
Field2 [nvarchar](255),
Field3 [nvarchar](255),
Field4 [nvarchar](255),
Field5 [nvarchar](255),
Field6 [nvarchar](255)
--dynamic sql to populate temp table with values from Excel file
    @sql = N'INSERT INTO ##tempf
                ''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0;Database='
                + @datafile
                + ';HDR=YES;IMEX=1'', 
                ''SELECT s.* FROM [Sheet1$] AS S 
                WHERE [Field2] IS NOT NULL AND [Field3] IS NOT NULL'')';

        EXEC sp_executesql @sql;
            'File not found or invalid file type' AS reasontext
            --ERROR_MESSAGE()  AS reasontext
        ,   NULL AS Field2
        ,   NULL AS Field3;
 IF OBJECT_ID('tempdb..##tempf', 'U') IS NOT NULL 
            DROP TABLE ##tempf
      RETURN -1
/*Do lots of data validation here
--if data validation tests are passed, write to final destination table
Insert INTO dbo.[finaldest]
   Select [Field1],
      from ##tempf;

 Drop table ##tempf; 

To make this work you need to install the Microsoft ACE OLE DB 12.0 provider on your server. You cannot use the Jet OLEDB Provider with 64-bit SQL Server and 64-bit Excel. Once you have this installed you need to change some settings:

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', 
N'AllowInProcess', 1
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', 
N'DynamicParameters', 1
sp_configure 'show advanced options', 1;
sp_configure 'Ad Hoc Distributed Queries', 1;

Here is a blog post that explains the errors you might get if you don’t have these settings correct. It also notes that you can go check your settings in the registry keys. For SQL Server 2012 the path is HKEY_LOCAL_MACHINE -> SOFTWARE -> Microsoft -> Microsoft SQL Server -> MSSQL11.MSSQLSERVER -> Providers -> Microsoft.ACE.OLEDB.12.0.  You can then see that AllowInProcess and Dynamic parameters are set to 1.


This provider seems to be a bit fussy and there are some things that took me a while to troubleshoot once I got started.  First, make sure that the location of the Excel file you are reading is accessible to the account that is running the SQL engine and doesn’t require admin access. The Linked Server errors that get returned with this really aren’t that helpful, so you have to use your Google-fu to work your way through it.  I used a global temporary table so it would be accessible across connections if it needed it to be (I was having trouble when I used a local temp table).  The application that calls this stored procedure queues up the calls and executes them serially so one can’t override the other.

Returning results from an Analysis Services cube in SQL

Technical need: I had some data (several facts with common dimensions) with calculations in a multidimensional cube and an application that needed that data but couldn’t/wouldn’t consume the MDX result set.

The query: For demonstration purposes I’m using a small query that returns the values for a drop-down box for selecting a member of the date dimension.

select a."[Measures].[DateKeys]" as DateKey, 
a."[Measures].[DateLevel]" as DateLevel, 
a."[Measures].[DateValues]" as DateValue 
('MSOLAP','Datasource=localhost; Initial Catalog=MyCubeDB;',
'with member [Measures].[DateValues] as
    Iif([Date].[Fiscal Calendar].currentmember.level_number = 2, "    " 
    + [Date].[Fiscal Calendar].currentmember.member_value, 
    [Date].[Fiscal Calendar].currentmember.member_value)
  member [Measures].[DateKeys] as 
    [Date].[Fiscal Calendar].currentmember.uniquename
  member [Measures].[DateLevel] as 
    [Date].[Fiscal Calendar].currentmember.level_number

 {[Measures].[DateValues], [Measures].[DateKeys], 
                           [Measures].[DateLevel]} on 0 ,
 {Exists(Descendants([Date].[Fiscal Calendar].[FY2014], 1, 
                         SELF_AND_BEFORE), , "MyMeasureGroup") } on 1
 from [MyCube]
') as a;

You can see that I explicitly named my fields, but you can also do a select * to get all the fields from the OPENROWSET. I had to put the fields from the cube in quotation marks to make it work because of the brackets in the field names. For Analysis Services, MSOLAP is the name of my provider.  You must have the correct version of this provider listed under the linked servers on the server where this is being executed.

MSOLAP ProviderFor SQL Server 2012 SP1, you can download the Microsoft Analysis Services OLE DB Provider for Microsoft SQL Server 2012 SP1 here. There is nothing special about the data source and initial catalog. They are exactly the same connection info you would use when connecting to SSAS through Management Studio.  The last part is simply the MDX query.  You should be able to copy that part out and run it in Management Studio connected to your SSAS database and get results.

Inserting Images Into a Database

Technical need: I was building a demo for Power BI and I needed to show some images in my Power View reports in Office 365.  In order for the images to show in Office 365, you must import the images into SQL Server and then import them into Power Pivot. First I downloaded images of flags for each country from the CIA world factbook.  I took the folder of flags and put it in my pictures folder. I already had a table in SQL Server that contained country names and codes.  So I just needed add a flag for each country.

The query:

  CREATE TABLE [dbo].[countryflagsdemo]
  [id] int, 
  [country] varchar(100), 
  flagimage [varbinary](max)
 --get ID, country, and code from existing table
  INSERT INTO dbo.countryflagsdemo (id, country, code)
  SELECT id, country, code FROM dbo.Countryflag1;

  DECLARE @rows INT; 
  SET @i = 1;
--get max id value for loop
  SET @rows = (SELECT COUNT(country) FROM dbo.countryflagsdemo)
  WHILE (@i < @rows)

    SET @code = (SELECT code FROM dbo.countryflagsdemo WHERE [id] = @i);
    SELECT @sql = 'UPDATE dbo.countryflagsdemo 
      SET flagimage = 
      (SELECT BulkColumn FROM 
            ''C:\Users\mlongoria\Pictures\flags\large\' + @code 
               + '-lgflag.gif'', Single_Blob) as flagimage)
            WHERE  = ''' + @code + ''';'

    EXEC (@sql);
    SET @i = @i + 1; 
    --PRINT @code;


I figured out how to do this based upon this MSDN blog post. I inserted my other data first and then added the images.  I’m sure you could find a way to do this all in one select statement.  Since this was a one-time thing I didn’t see the need to find another way. It should be noted that the bulk insert must pull from a location on your computer; you can’t use links to the images on the internet. I downloaded the images in a folder. Each image name was the abbreviation for the country. If you are going to import this data into PowerPivot, be sure you change the table behavior properties so it will recognize the varbinary field as an image.


There may be other ways to accomplish my goals, but it’s nice to understand how to use OPENROWSET and have that in my toolbox.

Books, Data Visualization, Data Warehousing, Excel, PerformancePoint, SSAS, SSIS, Uncategorized

The One Book

I tend to get some variation of the following question as I present at SQL Saturdays and work with clients:

What is the one book I should read to gain a good understanding of this topic?

There are many great books out there on business intelligence, data warehousing, and the Microsoft BI stack.  Here is my list of those that had a significant impact on my learning and career or that I consider the current defining book on the subject.

Data Warehousing/Dimensional Modeling –  The Data Warehouse Toolkit by Ralph Kimball and Margy Ross (link is to the newest edition of the book) defines dimensional modeling, walks through many common data scenarios and explains recommended data modeling and ETL practices. If you are designing, building, or enhancing a dimensional data warehouse, please read this book.

Master Data ManagementMaster Data Management by David Loshin thoroughly explains the resources required, helps clarify goals, and discusses the challenges of implementing a master data management program in your organization. The book also discusses architecture options and provides a roadmap that you can adapt to your project. I actually did an independent study class based upon this book as part of my MBA. I would recommend it to anyone just getting into MDM as a great platform agnostic view of MDM.

Agile Data WarehousingAgile Data Warehousing Project Management: Business Intelligence Systems Using Scrum by Ralph Hughes is an enlightening read about applying the agile process to data warehousing by someone who understands both.  Use this book to help set expectations at the beginning of projects, estimate effort, decide what should be included in an iteration, and provide value early in your project.  It addresses DW enhancements as well as new DW projects.

SSISProfessional Microsoft SQL Server 2012 Integration Services contains great information whether you are just learning SSIS or you just need to understand the new features in 2012. It covers each task and data flow component, offers tips for performance tuning, and provides great examples.  If you haven’t moved up to SQL Server 2012 yet, you will want to learn about the project deployment model, environment variables, and project-level connection managers, which are all covered in this book.

SSAS TabularMicrosoft SQL Server 2012 Analysis Services: The BISM Tabular Model by Marco Russo, Alberto Ferrari, and Chris Webb
There aren’t that many books available on SSAS tabular models since it is fairly new. This one got me through a couple of projects as I learned tabular cubes and DAX.  You will read almost every page while building your first tabular cube.  Then you will return to find helpful tips on writing DAX calculations.

Data Visualization –  Information Dashboard Design by Stephen Few (link is to the newest edition of the book)
This book is what got me interested in data visualization.  The way I design reports and dashboards changed after I read it. I was torn whether to go with this book or Show Me the Numbers, but I think Information Dashboard Design covers the main points of Show Me the Numbers that I most want people to understand (it was also the first of Few’s books that I read). Today’s technology enables us to make reports that are high on shiny and low on actionable information. I see two troubling trends: reports that are just wide tables with several hundred lines that no one can read at one time, and really shiny dashboards with lots of bells and whistles that make them look really cool. This book will tell you there is an appropriate time and place for tables and dashboards.  And while visual appeal is good, it should not be at the expense of effectively communicating the intended message of the information.

Up Next

I just picked up some books that look very promising and may cause me to update my list above:


Happy reading!