Data Warehouse for incident ticket tool information - loaded from BO extracts

Hi Kimball group,

I appreciate the free and very useful information on your site about data and dimension modeling.
I still would like to understand a few questions which I do not fully understand.

I work for a service desk where we import created and resolved ticket information from different customers into our data warehouse.
At the moment we keep all created ticket information in a single table. We are in the process of rebuilding our data warehouse and would like to use a dimensional model now.

Regarding the dimensions I would like to understand if it is a good idea to have one value dimensions like Priority or Type with the following schema:

PriorityDIM:
SID, PriorityName, SCD1 columns

Example:
1, Unknown
2, Urgent
3, High, ....
4, Medium, ...
5, Low, ...

TypeDIM
SID, TypeName, SCD1 columns

Example:
1, Incident, ...
2, Request, ...

Furthermore I'd like to understand how to best load these dimensions as each customer uses their own naming convention for priorities.
One customer can call priority sid 3 "High" and another customer "1-High".

Shall we standardise/conform the priorities dim and maybe have a mapping table so that each customer team can also filter the reports with their own/customer naming convention?
Is it better to add the priority rows for each customer to the PriorityDim and maybe two columns for the conformed value and the customer identifier(sid) to whom this dim row belongs?

We already realised that loading the fact table is more complicated due to the amount of joins to the dims.
Before we simply moved most of the data from stagging area to the created/resolved fact tables with almost no joins at all.
We then had High and 1-High as priority in the fact table.

Thanks,

Mathias

Yes, the natural key for a row in the dimension would include both the customer ID and priority code. As normal, the dimension would have a surrogate primary key, which would be used as the foreign key in the fact table.

I don't follow the comment "fact table is more complicated due to the amount of joins to the dims". Are you referring to the difference in querying a star schema as opposed to a big wide table? Yeah, the flat table is simpler to query, but a star schema can be enhanced by the addition of other stars. This allows you to integrate information across common contexts (dimensions) and maintain a standard query form.

Hi Ngalemmo,

Hope you are well.

Many thanks for your answer, I highly appreciate that you confirmed our approach. From the amount of posts you wrote on the forum I consider you an expert on this subject.

I was referring to the more complicated Fact loading as we need to inner join all dimensions. As I see from your answer its is more complex but in the long term brings benefits in terms of standard queries.

Have a lovely Sunday,

Thanks,
Mathias