Search code examples
ssasdimension

Non-Date Roleplaying Dimensions in SSAS

I am new to SSAS and am setting up a proof of concept. I love the idea of Role-Playing dimensions, but i'm having trouble getting one setup that is NOT based on dates. Here is the use-case:

In our ERP system, we have a fact table we'll call "Time Entries" that has:

  • User_ID
  • Biller_ID
  • Approver_ID
  • Hours Worked
  • ETC

I also have a "Resource" table that i'm relating these to as foreign keys:

  • Resource_ID
  • Department_Name
  • ETC

When I create my Data Source View, I create a relationship between:

  • User_ID -> Resource_ID
  • Biller_ID -> Resource_ID
  • Approver_ID -> Resource_ID

My "Resource" Dimension can be successfully deployed and processed, and has the following Attributes:

  • Resource_ID
  • Department Name

My "Work Entries" cube has one measure, "Hours Worked". When I add in my "Resources" dimension, it creates three roleplaying dimensions:

  • User
  • Approver Resource
  • Biller Resource

When I go to process, i'm receiving the following error:

Errors in the OLAP Storage Engine: The attribute key cannot be found when processing: Table: 'Time Entries', Column: 'user_id', Value: 'some number', The Attribute is 'Resource ID'.

So far, the only post I've followed that allowed me to successfully troubleshoot is this one:

https://www.sqlservercentral.com/Forums/1219713/Errors-in-the-OLAP-storage-engine-The-attribute-key-cannot-be-found-when-processing-Even-though-key-Exist-in-Dim-Table

TL;DR -

  • I've delete the relations between the factable and dim tables in the database.
  • I refresh the dataSourceViews and thera are no relations between tables
  • I remove the dimentions in the cube design
  • I recreate the dimentions in the cube design
  • I build then relations in the dataSourceViews between the foreign key in the factable and the primary keys in dim tables
  • i reprocesed the cube

The problem with this is that because we've added the dimension back BEFORE creating the relationships, we don't have our roleplaying dimensions.

I feel like i'm missing something simple here, but I can't quite figure it out. Can anyone tell me why my roleplaying dimensions aren't working?

Solution

  • Roleplaying function of a dimension does not depend on its type. Your dimensions can be used in role-playing scenario like Date dimension.
    On your problem - SSAS engine might build sometimes strange queries extracting dimension data, especially when your dimension is based on data from several tables. To check and investigate it:

    • Fix user_id value from your error message
    • Do process update or process full on corresponding dimension, and get SQL query used for processing user_id attribute from processing window form. It is under processing user_id attribute log entry.
    • Copy SQL query and run it. Check whether it returns id from the error message above.
    • If the value is missing - investigate the query

    In my experience - such things occurred when an erroneous dimension was built on two tables with some relation. SSAS engine have built query with strict inner join, and it has to be less restrictive left outer join.
    You can fix it with SSDT playing with DSV attribute being non-empty, but I found more simple to write a SQL query with proper joins in DSV directly.