Search for dimensions members in MDX using inStr()

Sometimes it might be handy to search dimensions members in MDX. The MDX below will show you an example on how to get all products that contains the name ‘Bike’

SELECT [Measures].[Sales Amount] on 0,
	Instr( [Product].[Product].currentmember.Properties( 'Member_Caption' ), 'Bike' )  > 0  
) on 1
from [Adventure Works]

And here is some other examples

List products that does not contain the word ‘Bike’

Instr( [Product].[Product].currentmember.Properties( 'Member_Caption' ), 'Bike' )  = 0  

List products that is named ‘Bike Wash – Dissolver’

Instr( [Product].[Product].currentmember.Properties( 'Member_Caption' ), 
'Bike Wash - Dissolver' )  = 1


An alternative approach on how to get “ProcessAdd” to work on update / delete in your partitions

I am having quite a lot of problems when running a ProcessUpdate and then a ProcessIndex on a multidimensional cube. The ProcessIndex takes hours to run on a large cube.

So I wanted to test an alternative approach. I really wanted to use ProcessAdd on my cube. But I also know that my data will be added, changed and deleted. So how can I make this work?

First I wrote a simple MDX to see the internet sales.

select [Measures].[Internet Sales Amount] on 0
from [Adventure Works] 

And the result I got was $29,358,677.22

To simulate an update I will add a new row that changes the original row. The original row might be a sales of 10 dollars. So if this row should be updated to a sale of 7 dollars I will add a new identical row with -3 dollars. So in the cube it will look like a 7 dollar sale.

To test this I added a new row in the database

INSERT INTO factinternetsales 
SELECT productkey, 
FROM   factinternetsales 
WHERE  ( salesordernumber = 'SO43697' )   

Please note the sales amount of -1000 and the SalesOrderLineNumber that is set to 2. This will decrease my internet sales with 1000.

Then you must change your partition query so that this only returns your new row. I changed my partition query on “Internet_Sales_2011” to this

SELECT [dbo].[factinternetsales].[productkey], 
FROM   [dbo].[factinternetsales] 
WHERE  salesordernumber = 'SO43697' 
       AND salesorderlinenumber = 2   

Then run a ProcessAdd using XMLA

<Batch xmlns="">
    <Process xmlns:xsd="" xmlns:xsi="" xmlns:ddl2="" xmlns:ddl2_2="" xmlns:ddl100_100="" xmlns:ddl200="" xmlns:ddl200_200="" xmlns:ddl300="" xmlns:ddl300_300="" xmlns:ddl400="" xmlns:ddl400_400="">
        <DatabaseID>Adventure Works</DatabaseID>
        <CubeID>Adventure Works</CubeID>
        <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>

If you then run the MDX it will return $29,357,677.22.

So how can I make this work in my deployment routine?

In my database I can split my fact table in 2. So that I have one table (current) with data that changes. And one table (archive) with data that does not change. I will do the same in my cube. I will make a partition for data that changes (from the current table). This partition will be processed with ProcessAdd and only return new rows. The other partition will have a regular SQL that returns everything from the archive table. The Archive partition should only be processed when new data is added. Maybe find a way that the archive table only contains real rows? And not the rows for updating and deleting?

If you only have small amounts of data you can of course keep your current table in your DWH with original rows from your source. And then drop and re-create your current SSAS partition. Then run a ProcessAdd.

Any comments?

I tested this in SSAS multidimensional but I am quite sure it will work in SSAS Tabular too

Count your top selling products with DAX

One of my customers wanted to count their top selling products. Since sharing is caring I will show you how this can be achived in this post 🙂

I want to list all products that sold for at least 5000 dollars in the year 2005.

    MEASURE 'Internet Sales'[Sales] =
        SUM ( 'Internet Sales'[Sales Amount] )
        ADDCOLUMNS (
            FILTER (
                SUMMARIZE (
                    CROSSJOIN (
                        VALUES ( 'Date'[Calendar Year] ),
                        VALUES ( 'Product'[Product Id] )
                    'Date'[Calendar Year],
                    'Product'[Product Id]
                'Internet Sales'[Sales] >= 5000
                    && 'Date'[Calendar Year] = 2005
            "Sum of Sales", 'Internet Sales'[Sales]
) ORDER BY 'Internet Sales'[Sales] DESC

And the result will look like this

Top selling 1

If you want to only display the number of products you can add “row” and “countrows” like this

    MEASURE 'Internet Sales'[Sales] =
        SUM ( 'Internet Sales'[Sales Amount] )
    "Count", COUNTROWS (
        ADDCOLUMNS (
            FILTER (
                SUMMARIZE (
                    CROSSJOIN (
                        VALUES ( 'Date'[Calendar Year] ),
                        VALUES ( 'Product'[Product Id] )
                    'Date'[Calendar Year],
                    'Product'[Product Id]
                'Internet Sales'[Sales] >= 5000
                    && 'Date'[Calendar Year] = 2005
            "Sum of Sales", 'Internet Sales'[Sales]

And the result will look like this

Top selling 2

Can Tabular models be optimized by sorting the fact tables?

In multidimensional cubes you can create faster cubes by sorting your facts before you process the cube. By doing this your models will be more compressed (smaller) and your queries will run faster.

Today I wanted to test the same in Tabular. So I created 2 models with one fact table and a few dimensions. The only difference between the models are the fact table. In the first model I used the orginal table (FactInternetSales). And in the other model I used a view against FactInternetSales with a order by on all columns.

These are the tables I used


Then I processed and deployed the models

When checking the size of the folders that contains the fact data I was kind of surprised. I looked to be the opposite! The folder for the regular table was actually smaller than the one that represents the view. So the table seemed to be more compressed than the view.

What to you think? Is this caused by the fact that one of the facts is a table and the other one is a view? Or is the fact allready sorted in the table?

Hot swap table in SSIS / SQL with retry logic

At one of my customers they wanted me to load data into a vital table during daytime. The main problem is that this table is sometimes used in other SSIS packages. Also during daytime. The SSIS package that fills this table is a trunc / fill job.

So I started working on a technique to load data into another table and then switch names. To make this solution more robust I also added a retry logic and a delay.

You might want to change the script so that it fits your environment and needs 🙂

So your SSIS package will fill the table “MainTable_swap”. Then you can add a SQL Task at the end that executes the script below.

Declare @retries int = 0
Declare @numberOfRetries int = 4


        EXEC sp_rename 'MainTable', 'MainTable_switch'
        EXEC sp_rename 'MainTable_swap', 'MainTable'
        EXEC sp_rename 'MainTable_switch', 'MainTable_swap'
	IF @retries < @numberOfRetries
		print 'Failed... retry...'
		Set @retries = @retries + 1
		WAITFOR DELAY '00:05:00.00' -- Wait for 5 m
		GOTO RETRY -- Go to Label RETRY
		RAISERROR (N'Could not rename table... giving up!', 10,  1, N'number', 5);
		select 1/0

The “select 1/0” is to force SQL Task in SSIS to fail if the script fails. It did not response to the RAISEERROR.

Feel free to drop me an email if you have an improvment to the script

Using BIML to create your staging packages

I have been reading some articles about BIML. And now I wanted to test it myself. In a new project I am starting with I have to move a lot of tables from source to staging. A perfect job for BIML.

Please note that copying the BIML code is not that easy. When pasting it to you own file it will change some of the markup. And your file will not compile. Please compare your copied code against the original code.

First of all I downloaded and installed the latest version of BIDS Helper. Restart Data Tools.

Then I created 2 new databases. One called “Meta” that should keep track of all tables I want to copy from source to staging. And one called STG. In the “Meta” database I will make a table called “MyStagingTables”.


USE [Meta]

CREATE TABLE [dbo].[MyStagingTables](
	[TableName] [varchar](250) NULL

-- Add some values (these are some of the tables in the AdventureWorksDW2012 database)

INSERT [dbo].[MyStagingTables] ([TableName]) VALUES (N'DimDate')
INSERT [dbo].[MyStagingTables] ([TableName]) VALUES (N'FactInternetSales')
INSERT [dbo].[MyStagingTables] ([TableName]) VALUES (N'DimPromotion')
INSERT [dbo].[MyStagingTables] ([TableName]) VALUES (N'DimSalesReason')
INSERT [dbo].[MyStagingTables] ([TableName]) VALUES (N'FactSurveyResponse')
INSERT [dbo].[MyStagingTables] ([TableName]) VALUES (N'FactResellerSales')
INSERT [dbo].[MyStagingTables] ([TableName]) VALUES (N'FactCallCenter')


Then I want to script all of these tables for creating them in my staging database. I am sure this can be done with BIML. But I used SQL 🙂

use [AdventureWorksDW2012]

Declare @createStatement varchar(max)
Declare curP cursor For

select  'create table [' + + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
from    sysobjects so
cross apply
        '  ['+column_name+'] ' + 
        data_type + case data_type
            when 'sql_variant' then ''
            when 'text' then ''
            when 'ntext' then ''
            when 'xml' then ''
            when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
            else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' +
        case when exists ( 
        select id from syscolumns
        where object_name(id)
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
        ) then
        'IDENTITY(' + 
        cast(ident_seed( as varchar) + ',' + 
        cast(ident_incr( as varchar) + ')'
        else ''
        end + ' ' +
         (case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
          case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', ' 

     from information_schema.columns where table_name =
     order by ordinal_position
    FOR XML PATH('')) o (list)
left join
    information_schema.table_constraints tc
on  tc.Table_name       = so.Name
AND tc.Constraint_Type  = 'PRIMARY KEY'
cross apply
    (select '[' + Column_Name + '], '
     FROM   information_schema.key_column_usage kcu
     WHERE  kcu.Constraint_Name = tc.Constraint_Name
     FOR XML PATH('')) j (list)
where   xtype = 'U'
AND name    NOT IN ('dtproperties')
AND tc.TABLE_NAME in (select TableName from Meta.dbo.[MyStagingTables])

OPEN curP 
Fetch Next From curP Into @createStatement

While @@Fetch_Status = 0 Begin

print @createStatement

Fetch Next From curP Into @createStatement

End -- End of Fetch

Close curP
Deallocate curP

If you run this SQL it will print all the create table statements for you. Copy these and execute it on the stg database.

Then I want to add a new column called “ExecutionDate” to all of these tables. Run the SQL below, copy the output and then execute it on the stg database.

Declare @alterStatement varchar(max)
Declare curP cursor For

SELECT TABLENAME from Meta.dbo.MyStagingTables

OPEN curP 
Fetch Next From curP Into @alterStatement

While @@Fetch_Status = 0 Begin

print 'alter table ' + @alterStatement + ' add ExecutionDate datetime'

Fetch Next From curP Into @alterStatement

End -- End of Fetch

Close curP
Deallocate curP

You should now have all your tables in the stg database. And all of these tables should have a column called “ExecutionDate”.


Lets open Data Tools and create a new SSIS Project called “BimlTest”. Right click on the project and add 2 new Biml files. One called “CreateSTGPackages.biml” and one called “CreateMasterPackage.biml”.

In the file called “CreateSTGPackages.biml” copy this code:

<#@ import namespace="System.Data" #>
	<#@ import namespace="Varigence.Hadron.CoreLowerer.SchemaManagement" #>

		<Biml xmlns="">
				<Connection Name="sql2014" ConnectionString="Data Source=server;Initial Catalog=AdventureWorksDW2012;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;" />
				<Connection Name="stg" ConnectionString="Data Source=server;Initial Catalog=STG;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;" />

						Moving table from source to stage.
						Package generated from BIML

				<# string sConn = "Data Source=server;Initial Catalog=Meta;Provider=SQLNCLI11.1;Integrated Security=SSPI;"; #>
				<# string sSQL = "SELECT TABLENAME FROM MyStagingTables" ; #>
				<# DataTable tblPackages = ExternalDataAccess.GetDataTable(sConn,sSQL); #>
				<# foreach (DataRow pkg in tblPackages.Rows) {#>
				<Package Name="STG_<#=pkg["TABLENAME"]#>" ConstraintMode="Linear">

					<ExecuteSQL Name="Truncate table STG_<#=pkg["TABLENAME"]#>" ConnectionName="stg" ResultSet="None">
						<DirectInput>truncate table <#=pkg["TABLENAME"]#></DirectInput>

						<Dataflow Name="Moving data from source to staging">

						<OleDbSource Name="<#=pkg["TABLENAME"]#>" ConnectionName="sql2014">
							<DirectInput>SELECT * FROM <#=pkg["TABLENAME"]#></DirectInput>

						 <DerivedColumns Name="Adding Metadata">
                                <Column Name="ExecutionDate" DataType="DateTime">GETDATE()</Column>

						<OleDbDestination Name="stg <#=pkg["TABLENAME"]#>" ConnectionName="stg">
							<ExternalTableOutput Table="<#=pkg["TABLENAME"]#>" />


				<# } #>


And in the script called “CreateMasterPackage.biml” you should copy this code

<!--Includes/Imports for C#-->
<#@ template language="C#" hostspecific="true"#>
	<#@ import namespace="System.Data"#>
		<#@ import namespace="System.Data.SqlClient"#>
			<Biml xmlns="">
					ConnectionString="Data Source=server;Initial Catalog=SSISDB;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;">
					<Package Name="MyMasterPackage.dtsx" ConstraintMode="Linear" AutoCreateConfigurationsType="None" ProtectionLevel="DontSaveSensitive">
							<# string sConn = @"Provider=SQLNCLI11.1;Server=sql2014;Initial Catalog=SSISDB;Integrated Security=SSPI;Connection Timeout=0;"; #>
							<# string sSQL = "SELECT Packages.[name] FROM [SSISDB].[internal].[packages] as Packages INNER JOIN [SSISDB].[internal].[projects] as Projects ";#>
							<# sSQL += "on Packages.project_version_lsn = Projects.object_version_lsn WHERE = 'BimlTest' AND like 'STG_%' ORDER BY"; #>
							<# DataTable tblPackages = ExternalDataAccess.GetDataTable(sConn,sSQL); #>
							<# foreach (DataRow row in tblPackages.Rows){ #>
							<ExecutePackage Name="EPT -<#=row["name"]#>">
								<ExternalProjectPackage Package="<#=row["name"]#>">
							</ExecutePackage >
							<# } #>

Right click you SSIS Project again and choose options.

BIMLTest options

Change protection level to match the screenshot.

Now to the fun part. Right click on “CreateSTGPackages” and choose “Generate SSIS Packages”. If you get error please fix them. If everything is okay you will get new files in your project.

SSIS packages

The packages will consist of a SQL Task that truncates the table. And a Data Flow that copies the data.


Data Flow

Deploy the SSIS project to SSISDB and create a folder called BimlTest.


Then run the package “CreateMasterPackage”. This will create a master package that executes all the other packages.


Now you should be able of executing this master package and everything should be fine 🙂

Testing row level security in Tabular (DAX)

In this post I will demonstrate how to add row level security in your tabular model. This can be a useful feature if you want to restrict access to different dimension members for different users.

First of all we need to create a new windows user on your PC / server. Lets call this user “TestUser2”

Then I will create a new table with user names and which customers they should be able of viewing.

use [AdventureWorksDW2012]

create table [UserAccess] 
	FullDomainUserName varchar(200),
	DomainUser varchar (100),
	CustomerKey int

insert into [UserAccess] values ('yourdomain\TestUser2', 'TestUser2', 11511)
insert into [UserAccess] values ('yourdomain\TestUser2', 'TestUser2', 11512)

Open the Tabular model project in Visual Studio Data Tools.

Lets add this table to the model and then create a relationship to Customer and column CustomerKey. Click “Hide from client tools” on the UserAccess table.

After you have done this your model will look like this

Screenshot useraccess

Add a new role (Model -> Roles) called TestRole. Give this read permissions.

Then add this DAX to the DAX filter on the Customer table

='Customer'[CustomerKey]=LOOKUPVALUE('UserAccess'[CustomerKey], 'UserAccess'[FullDomainUserName], USERNAME(), 'UserAccess'[CustomerKey], 'Customer'[CustomerKey]) 

Role 1

Add your new Windows users to members

Role 2

Deploy your model.

Open a Excel pivot. When Connection to the SSAS server impersonate the TestUser2 user. After doing that your pivot will look like this.

Role pivot

Creating a KPI in Tabular (DAX)

In this post I will explain how to make a simple KPI in SSAS Tabular. First step is to make two calculated measure in our model. The first one will give us the sales for 01.10.2006. This will be our base measure.

KPI_SalesYesterDay:=Calculate(SUM([Sales Amount]);Filter(Date;'Date'[Date] = DATE ( 2006; 10; 01)))

The second measure defines the target value. That might look like this.

KPI_SalesComparableDay:=Calculate(SUM([Sales Amount]);Filter(Date;'Date'[Date] = DATE ( 2005; 11; 01)))

After creating these two measures you right-click on the measure “KPI_SalesYesterDay” and click “Create KPI”.

This screen will appear


Use the default values. Then deploy the model to the server.

If you create a new Excel pivot it will appear like this:


In a more real life example you might want to use dynamic dates. Like this

KPI_SalesYesterDay:=Calculate(SUM([Sales Amount]);Filter('Date';FORMAT('Date'[Date]; "yyyy-MM-dd") = FORMAT(NOW() - 1; "yyyy-MM-dd")))

This will give us the sales for yesterday. And then subtract 365 days on the target measure to get the comparable day.

KPI_SalesComparableDay:=Calculate(SUM([Sales Amount]);Filter('Date';FORMAT('Date'[Date]; "yyyy-MM-dd") = 
FORMAT(NOW() - 365; "yyyy-MM-dd")))

Or just leave the date filtering to whatever the user chooses

Percent of total in a hierarchy (DAX)

Getting percent of total in a hierarchy is easy in DAX. In this example we will use the Geography hierarchy.

Geography 1

Then I will create 2 calculated measures in my model.

TotalSales2:=calculate(SUM([Order Quantity]);ALL('Geography'))


Ratio To Total:=Sum([Order Quantity]) / [TotalSales2]

The last one should be formatted as percent.

Then process and deploy your model.

If you open an Excel pivot you should see something like this

Hierarchy Excel 2

If you summarize “Ratio To Total” in this pivot you will get 100 %.

When changing the pivot to show city instead of country the percent still works as intended.

Hierarchy Excel 3

I did not show all the cities in the screenshot above

Percent of parent in hierarchy (DAX)

Getting percent of parent in a dimension without a hierarchy is quite simple. But to get percent of parent in a hierarchy with several levels you will have to add some more code. In the current version of DAX you will have to write code for each level.

My sample query will only work for the hierarchy in the “Geography” dimension called “Geography”.

Geography 1

Then I will add 2 calculated measures to my model.

ParentSalesEmployee :=
IF (
    ISFILTERED ( 'Geography'[City] );
        SUM ( [Order Quantity] );
        ALL ( 'Geography'[City] )
    IF (
        ISFILTERED ( 'Geography'[State Province Name] );
        CALCULATE (
            SUM ( [Order Quantity] );
            ALL ( 'Geography'[State Province Name] )
        CALCULATE (
            SUM ( [Order Quantity] );
            ALL ( 'Geography'[Country Region Name] )

And then

Ratio To Parent:=Sum([Order Quantity]) / [ParentSalesEmployee]

And the result will looks like this:

Geography 2

I hope hierachy handling will become better in next version of DAX…