Feeds:
Posts
Comments

Archive for the ‘SSAS Tabular’ 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).

capture

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.

Advertisements

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.

MDX:

WITH

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

MEMBER [Measures].[Total Individuals Not Achieving Goals]
AS COUNT(
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]
} ON COLUMNS
,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”.

MDX:

WITH

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

DAX:

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 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.

TabularSSRSReport1

 

TabularSSRSReportDataset

 

Read Full Post »

%d bloggers like this: