Archive for the ‘Analysis Services’ Category

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 »

Older Posts »

%d bloggers like this: