Archive for the ‘Analysis Services’ Category

I have got a business requirement to only allow users in the same department to see the data that belong to their department. To do that in the SSAS tabular model 2016, you would need to create a table in SQL database then bring this table into the model. The table will contain the user’s first name, last name, login id, department name and/or employee id. The login id consists of the domain name and username. Once done, you would need to create a new role in the model. Go to the Model menu then select Roles to bring up the Role Manager. In the Members tab within the Role Manager, add users into the role. In the Row Filters tab, set the DAX filter for the fact table(s).

An example of the DAX filter is shown below that restricts users to see the data in the Issue Reports fact table based on which health service they belong to. The Users table contains a list of the users with their health service.

=‘Issue Reports’[Health Service]=LOOKUPVALUE(‘Users’[HealthService],

‘Users’[LoginId], USERNAME(),


‘Issue Reports’[Health Service])

To test:

Click on the “Analyze in Excel” from the SSDT then choose the role you have just created or get the user to test it.

To test on SSMS, browse the Cube and then click on the Change User icon.

test role in ssms





Is that possible to use the AD groups to perform the row level security in the tabular model?

 Currently my DAX filter is below:

=’Issue Reports'[Health Service]=LOOKUPVALUE(‘Users'[HealthService],

‘Users'[LoginId], USERNAME(),


‘Issue Reports'[Health Service])

It does a lookup based on the LoginId in the Users table and the user’s username returned from the USERNAME(). If it matches, then returns with the health service the user belongs to and then returns with the issue reports for that health service.

 I googled if there is a IS_MEMBER function in DAX or something similar and I haven’t found any solutions.

The workaround is to create two roles and then do a dax filter on the objects.

We created two roles in the model i.e. one for East Metro and another for South Metro and then do the dax filter on the facts and/or dimensions.

We only have 2 dimensions that have health service so we will use bi-directional row level security on one of the dimensions and use dax filter on the other dimension.

Read Full Post »

I received this error even though I was able to connect to the data source. This is rather odd. I am on SSAS tabular server 2016 and Visual Studio 2015. VS complaint about the mismatched compatibility mode. I then checked my workspace and found out that my workspace was still pointing to SSAS tabular server 2014. So this error may not say what it means.

To change the workspace server:

Right click on the Model.bim and then choose “Properties”. In the “Properties” window, locate the Workspace Server and then change it to the SSAS 2016 tabular server.

To change to compatibility level:

In the “Properties” window, locate the Compatibility Level and then choose SQL Server 2016 RTM (1200).


To change the Workspace Database (note that it’s greyed out):

Refer to https://saysmymind.wordpress.com/2012/11/23/changing-workspace-database-name-in-ssas-2012-tabular/

1. Starts SSTD (obviously!!) and create a new Analysis Services Tabular Project. At this point I would also recommend that you rename the default Model.bim to something more meaningful to your project.

2. Right click on bim file and click properties.

3. Apart from all the other properties, you will find Workspace Database property in the bottom portion. The description of the properties is here. If you open SSMS and connect to SSAS Tabular instance, you should find a database with the same name as this property value. As you would notice, Workspace Database cannot be changed. It is read-only and that’s the one we want to change.

4. Now right click on project ,click Open Folder in Windows Explorer and close SSDT.

5. In windows explorer, there would be *.settings file. The format of this file seems to be ModelName.bim_username.settings. It might be hidden so change the folder settings to show hidden files.

6Open this file in notepad. It is an xml although everything will come on one line. I would also recommend that you don’t try to format the file.

7. Now look for tag. The value between this tag is name of the database. Change this value to something more meaningful, save and close notepad.

8. Open the project/solution in SSDT. Check the properties of bim file. The value of property Workspace Database would be what you set in Step 7.

9. You can also open SSMS and verify this.

Read Full Post »

I reprocessed an existing tabular model in order to refresh data but it came up with connection related errors as shown below.

Tabular model processing error

I do have permission to the views, cube and everything else but I still can’t reprocess the model. How odd!

The fix is to change the connection manager provider from SQL Server Native Client 11.0 to Microsoft OLE DB Provider for SQL Server.

Tabular model processing error FIX






Read Full Post »

I am presented with a task to support an existing tabular cube for a client.

ssas tabular model

There is a bug within the report where the number of the individuals achieving their plans are incorrect because an individual can have more than one achieved plan. It is also incorrect because it used a measure that count the number of plans achieving goals instead of the number of individuals achieving goals.

As an example, the Individual ID of 47313 and 52478 each achieved two plans, so it should be counted as one participant instead of two.

An individual can have more than one plan

As you may have known, when you create a report in SSRS over a tabular cube, the dataset query is in MDX, instead of DAX. As I am so familiar with MDX, this task is a piece of cake, however, as my client is also using Pivot Table in Excel, you can’t really modify anything in there. So the tabular cube needs to be fixed and I still need to learn how to do this in DAX.  I needed to add another measure to count the total distinct individual achieving plan goals i.e. [Measures].[Total Individuals Achieving Goals] into the cube in DAX.



MEMBER [Measures].[Total Individuals Achieving Goals]
FILTER( [Participant].[Participant_SK].[Participant_SK].MEMBERS , [Measures].[Total Plans Achieving Goals] >= 1 )

MEMBER [Measures].[Total Individuals Not Achieving Goals]
FILTER( [Participant].[Participant_SK].[Participant_SK].MEMBERS , [Measures].[Total Plans Not Achieving Goals] >= 1 )
MEMBER [Measures].[Total Individuals Achieving Goals %] AS CASE WHEN [Measures].[Total Reviews] = 0 THEN 0 ELSE [Measures].[Total Individuals Achieving Goals] / [Measures].[Total Reviews] END

MEMBER [Measures].[Total Individuals Not Achieving Goals %] AS CASE WHEN [Measures].[Total Reviews] = 0 THEN 0 ELSE [Measures].[Total Individuals Not Achieving Goals] / [Measures].[Total Reviews] END

SELECT NON EMPTY { [Measures].[Total Reviews]
,[Measures].[Total Plans Achieving Goals]
,[Measures].[Total Plans Not Achieving Goals]
,[Measures].[Total Individuals Achieving Goals]
,NON EMPTY { [Participant].[CostCentreName].&[Cockburn Kwinana] } ON ROWS
FROM [Model]
WHERE ( [Participant].[IsCostCentre].&[Yes]
,[Review Date].[CurrentYTD].&[Yes]
,[Plan Details].[IsApproved].&[Yes]

Total Individuals Achieving Goals

As shown in the screenshot above, the report should show the total individuals achieving goals of 49 instead of 51, because two of the individuals achieved 2 plans.

At first thought, how do I convert my calculated measure of [Measures].[Total Individuals Achieving Goals]  in MDX query to DAX? I needed to filter how many participants have achieved their plan goals and then count them.

So I tried a straight conversion to DAX as shown below. It complains about semantic error of “The COUNT function only accepts a column reference as an argument”.



MEMBER [Measures].[Total Individuals Achieving Goals]  AS COUNT(FILTER( [Participant].[Participant_SK].[Participant_SK].MEMBERS , [Measures].[Total Plans Achieving Goals] >= 1 ))


Measure 1:=COUNT(FILTER(Plan[ParticipantID], [Total Plans Achieving Goals] >= 1))

DAX is very different from MDX so there is no simple conversion.

To do that in DAX, first count the number of distinct participants for the whole fact table and then filter how many of the distinct participants have achieved plan goals.

Total Participants:=DISTINCTCOUNT([Participant_SK])

Total Individuals Achieving Goals:=CALCULATE([Total Participants], ‘Plan Details'[PlanGoalsAchieved] = “Yes”)

Following the same logic:

Total Individuals Not Achieving Goals:=CALCULATE([Total Participants], ‘Plan Details'[PlanGoalsAchieved] = “No”)

Total Individuals Achieving Goals Percentage:=DIVIDE([Total Individuals Achieving Goals], [Total Reviews], 0)

Total Individuals Not Achieving Goals Percentage:=DIVIDE([Total Individuals Not Achieving Goals], [Total Reviews], 0)

Once the the table in the model has been processed (menu: Model -> Process -> Process Table), it will auto populate the selected table with data and deploy a copy of cube with your username included in the name. Once you are happy with the changes made, you can then deploy to overwrite the existing cube.

ssas tabular model 2

I also noticed that only the calculated measures will be listed under Measures in the tabular model. The “base” measures are listed under Dimensions. In multidimensional, all base and calculated measures appear under Measures. Furthermore, tabular does not support role playing dimensions. Say your sales fact table has got a few different dates such as OrderDate, DueDate, and ShipDate. You would need to import the Date table three times and then rename them including the meta data accordingly. You would then need to create the relationship between the sales table and date table.

You really need to change your thinking around DAX versus MDX and Tabular vs Multidimensional.

Read Full Post »

I am able to connect the Power BI Designer Preview to SSAS Multidimensional, which is a big plus for someone like me who have worked with SSAS multidimensional for a few years! 🙂 I just tested it and it has worked. Of course, it can also connect to SSAS tabular.

What I like about Power BI Preview is that you can add many pages to a dashboard, it is a stand alone application (not dependent on SharePoint or Excel), and that it is more interactive.  I still need to find out how to host this in a shared environment (upload into SharePoint?) and how to manage security (is it done through cube?). Also yet to find out how to add an URL link on the graph, edit the graph title and the graph legends (we can do this in SSRS!).

What’s worth noticing is that I still have no idea how to create a drilldown from one graph to another. The only way to display the details of your graph is by creating a table or card next to it, it seems.

Screenshots below show a pie chart of the reseller sales amount by the reseller business types. There are 3 reseller business types I.e. warehouse, specialty bike shop and value added reseller. On the right side of the pie chart is a card showing the reseller order quantity and reseller freight cost of each business type. At the bottom side of the pie chart is a bar chart showing the reseller sales amount for each calendar year.

Clicking on a pie slice will show its details on the card and bar chart. Warehouse Value Added Reseller Specialty Bike Shop

Read Full Post »

Apparently you need to install 2 instances of SSAS in 2014 if you want to both Tabular and Multidimensional to co-exist. Right click on the SSAS server instance then select Properties to check if the server mode is tabular or multidimensional.

The difference between the two when opened in SSMS is shown below.

SSAS Tabular vs Multidimensional


Read Full Post »

I tested writing a report in SSRS over SSAS tabular model in 2014 and the dataset that it produced is in MDX not DAX. So how does this make any sense, Microsoft? :-O

There is no DAX query editor in SSMS but you could run your DAX query in the MDX query editor.





Read Full Post »

I have a fact table containing measures in decimal points such as decimal(5,2). When these measures brought forth to the OLAP Cube, they get rounded up. The data types of these measures in the cube are “Inherited”. Changing the data type from “Inherited” to “Double” had not worked resulting in cube processing failure saying that the data type must match the data type in the source. To resolve this, the measure  data type in the fact  table of the data warehouse needs to use float data type.


Read Full Post »

I created a tester role in the cube with read permission. In the Cell Data tab, I have the “Enable read permissions” ticked, however, I didn’t specify the MDX to allow reading of the cube content.

When a tester browse the cube, the measure data all return with “#N/A”. To resolve this, untick the “Enable read permissions” or follow the following URL.




Read Full Post »

When I access my Picnic dashboard from my laptop, the  images of fruit, beverages, bread, and vegetable on my slider and tile container were not displayed. This is because the URL of these images were referencing to http://localhost. The following screenshot shows that the URL links have been referenced correctly.

Member value in SSAS Tabular

To point it to a server name, right click on the SSAS Tabular database then select “Script Database as” option followed by the ALTER TO” option to the new query editor window as per screenshot below. This will output the script in XMLA. Do a quick find and replace from http://localhost to http://bidemovm2, in my case. Then process the database.

How to modify ssas tabular


Read Full Post »

Older Posts »

%d bloggers like this: