How should I design my SSAS cube to handle non aggreagable data?

I'm working on a SSIS/SAAS project to build a BI solution.

One of my data sources contains informations about a Service Desk.

A user can create a new request related to a service catalog (for example because his laptop crashed). So it will generate a new row in the Request table (creation date, comment, tracking number, etc.). To solve this issue, few actions will be perform. So these actions will be recorded in the action table (there is a One to many relationship between Request and Action tables).

An action can be : "try to format computer", "change hard drive", etc. In the production environnent a Request contains aproximatly from 10 to 100 actions.

I'm facing a problem about designing this because many columns of my fact table cannot be aggregated. In fact there are many date columns, tracking number (string), bollean values and only few SUM attributes.

Here is an extract of the dw model :

FactRequest :

ID (DW primary key)
Business Key (original PK)
Request number (string)
Begin date (datetime)
End date (datetime)
Max resolution date (datetime)
Time to solve request
Comment (string)
Delay (int)
...

FactAction :

ID (DW primary key)
Business Key (original PK)
Begin date (datetime)
End date (datetime)
Name (string)
Time to solve action
...

I know adding non aggregable data in a fact table is not the best solution.

In my SSAS project, I created a new cube based on my FactRequest table. It works fine except for "string" attributes such as the request identifier because it is a string. Should I use an SSAS "fact dimension" to create a "Request" dimension based on my FactRequest table ?

Any idea ? Thanks so much,

Solution

Sounds like you are lacking specific requirements (which is very common in BI projects). Is the textual data required to be displayed in the report at all? If yes: is it required only in some detail view?

Columns like ID, Business Key, Request number typically have little value in your cube. This data is only interesting for detailed reports (e.g. getting all actions taken for a certain request ID) and such lists often require no aggregates. You do not need a cube for lists like that, you can query the database directly with SQL.

Only if you require a summary report (e.g. getting the average time taken to solve a request per weekday) the cube could make sense - it may still not be worth the effort to use an SSAS database if you can get almost the same query response time with direct SQL queries.