I thought my disk space was low so I cleaned up to have more space, as well as restarted my PC but the error persisted.

Googling found me the link below, which provided a solution to the problem.


  1. Do a cleanup of the hard drive
  2. Change the location for the buffer files by setting the BLOBTempStoragePathand BufferTempStoragePath properties of your data flow task


Thanks to the above blogger 🙂


Apparently, if the Code is set up to be generated automatically, MDS does not allow you to delete the records in the entity unless an index is specified. However, the index size must not exceed 900 bytes. In my case, my index is over 900 bytes so a workaround has to be put in place i.e. by manually inserting the records into the staging table and specifying the import type = 6. Check the batch execution on MDS and you will see that the deletion works instantly.

insert into [MDS].[stg].[SkillRollupMapping_Leaf] (ImportType, BatchTag, Code)

select 6, ‘delete me’, code from mdm.[Skill_Rollup_Mapping]


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.

The solution is to set the ConsumeContainerWhitespace property of the report to true.

When the subreport is run on its own, it does not generate any blank page, however, when it is placed on another report, it generates a blank page. To solve this, make sure the subreport has the same size as the original report (page size) for the subreport.


I have a dataset in my tabular model that looks like below:

As you see, when you sum it for the IssueReportId of 9825, it will return with the total duration of 51, instead of 17.  The end result should look like below:

In SQL, this can be done using the following query where you get the max duration for each IssueReportId first and then add them together.

SELECT IssueReportId


      ,MAX(Duration) AS Duration

FROM IssueReports

GROUP BY IssueReportId



To do the above SQL in DAX, use the SUMMARIZE function and then use the SUMX function to sum them up in the tabular model.

Sum of Duration:=SUMX(SUMMARIZE(‘Issue Reports’, ‘Issue Reports’[IssueReportId], “Sum of Duration1”, CALCULATE(MAX(‘Issue Reports’[Duration]))), [Sum of Duration1])



SUMMARIZE(<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…)


SUMX(<table>, <expression>)

I noticed that whenever I changed the user permission on the derived hierarchy or model, the change did not happen immediately.

To force it to take effect immediately, run the following query:

USE [database];

SELECT @Model_ID = ID FROM mdm.tblModel WHERE [Name] = N’Model_Name’;
EXEC [mdm].[udpSecurityMemberProcessRebuildModel] @Model_ID=@Model_ID, @ProcessNow=1;




I have 3 entities that have been set up in a way that when an attribute of a member has changed value in one entity, it will be copied or moved to another entity. I am doing this in a stored procedure under the usr schema and then setting up a business rule for it.

While testing it, the member didn’t get moved or copied across. So the business rule didn’t work. The record has a member error code of 4 in the staging table.

Run the following query to view the errors that occur during the staging process. Replace name with the staging table name in the query.

SELECT * FROM stg.viw_name_MemberErrorDetails

This error means that the attribute value references a member that does not exist or is inactive.  Check the attribute list and the attribute type in each entity and make sure that they have the same configuration.

Also, when you stage domain-based attributes, you must use the code, rather than the name. Applies to ImportType0, 1, and 2 (reference: https://docs.microsoft.com/en-us/sql/master-data-services/staging-process-errors-master-data-services?view=sql-server-2017). So in your stored procedure, ensure that you only use the code to insert and not the name or both.


The reason why it’s not updating is because the schema has not been included in the update. So if your objects are created under a certain schema, the schema needs to be included.

Also, the schema needs to be at the top of the list otherwise the rest of the objects will be not added into the project in TFS. To get it to the top of the list, you can group it by schema as shown below.


I have a business requirement where when a user inserts a new record via Excel on MDS, the Code will be automatically generated as a concatenated value (once published), that makes the record unique.

To do this in MDS, I have had to create a business rule on the entity to say that if the Code is equal to zero then Code equals a concatenated value. The example below uses a concatenated value that consist of position_id and cost_centre.

After the business rule is published, the user is now able to insert a new record, however, he/she must set the Code value to zero or some numbers because it cannot be empty. This can be annoying because after copy and paste the record into the Excel, the user needs to modify the Code to zero or some numbers. It will be better if he/she only needs to copy and paste and then delete the Code (so that the Code appears blank). The above business rule will not work as the Code cannot be blank.

To solve this, the Code must be an integer instead of varchar. On MDS, you can set the Code to be automatically generated. Go to Home > System Administration > Manage Models > Entities and then edit the Entity and tick the “Create code values automatically” tickbox and set the “Start with :” textbox to number 1, as shown below:

You would then need to truncate and reload data into the entity. If you add a member into the entity manually, you will see that the Code textbox is greyed out as shown below.

Once set up, all good to go! 🙂






%d bloggers like this: