Feeds:
Posts
Comments

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

      ,BodyLocationOfInjury

      ,MAX(Duration) AS Duration

FROM IssueReports

GROUP BY IssueReportId

        ,BodyLocationOfInjury

 

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])

 

Syntax:

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

https://msdn.microsoft.com/en-us/query-bi/dax/summarize-function-dax

SUMX(<table>, <expression>)

Advertisements

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];
GO

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

Reference:

https://docs.microsoft.com/en-us/sql/master-data-services/immediately-apply-member-permissions-master-data-services?view=sql-server-2017

 

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! 🙂

 

 

 

 

 

I have an SSIS package that does SCD type 2, i.e. when there is an update, it inserts a new record and then expires the previous record. In testing it, I updated one record but when the package was run, the INSERT has failed with the error saying that the primary key constraint has been violated and cannot insert duplicate key in object. I checked my code and the checksum making sure that the incoming record and the existing record have different checksums. Indeed they both have different checksums. So this does not make any sense.

I talked about this with my colleague and he Googled it and found a solution (https://stackoverflow.com/questions/20153310/identity-not-generating-the-seed-and-increment-correctly)  that worked for my case. Thanks to the user that has shared the fix. 🙂

The solution is to reseed the table by running the following SQL:

DBCC CHECKIDENT(‘Id_Table’, RESEED, 0)    —- Reseed to any smallest number

DBCC CHECKIDENT(‘Id_Table’, RESEED)      —- Reseed without providing any seed value

Reseed the table to Zero and then just execute the DBCC command without any seed value and it will reseed the Identity value to next highest available Identity value. You do not need to truncate the table to reseed.

Even though the solution has fixed the problem, however, it keeps recurring.

I had another chat with my colleague at work. He said he has seen this before and provided me with the fix.

The fix is to insert the default records (where the UIDs are -1, -2, -3) first then load the records from the source into the destination table in the SSIS package. UID = -1 is unknown, -2 is no value and -3 is invalid.

LAST_VALUE does not always return with the same value. The workaround is to use FIRST_VALUE but order the records descendingly.

 

%d bloggers like this: