IanMeikle


This novice would like your assistance to solve a problem. The goal is to build a Data mart for use in a cube. The cube would have a Service Fact related to the Dimension of Personnel and Assets. The Measures would be:

Provision of Assets by Departments

Availability of Assets by Departments or Service

Volume of Support Calls by Departments

The model has one major problem: a many-to-many parent-child relationship in the hierarchy of the Asset Dimension. The hierarchy within the Dimension is

Bundle

Service

Assets

And the many-to-many relationship resides between Assets & Service. I wish to know how best to handle this situation; create a bridge table or create a Service Dimension. All advice is welcomed.




Re: many-to-many parent-child relationship in the dimension hierarchy

Bryan C. Smith


What is the business process you are modeling in your fact table The description of the measures is a little confusing to me.

Regarding the many-to-many relationship, you are saying that the fact table has a relationship to an asset, the asset belongs to multiple services, and a service belongs to a single bundle. Is that correct

Also, what is the relationship between the fact and the service Does a single fact record associate to a single service or does it associated to multiple services through the asset

Thanks,
Bryan







Re: many-to-many parent-child relationship in the dimension hierarchy

IanMeikle

The main objective is to create a cube on IT Support for performance reporting against SLAs, with a secondary objective being to expose the use of IT within the organisation. The high level categories of measures are: provisioning, availability, volume, and minutes. These would translate into measurement exposing the aggregated number of Assets provisioned; the percentage an Asset was available; the aggregated number of support calls received for an Asset; the minutes taken to close a support call on an Assest. Basically, sliced, diced and rolled up and down by organisation section, business unit, IT bundle and service. This is datamart design based on this information:

Code Snippet

Assets Dim -> Support Fact <- Personnel Dim
AssetID AssetID PersonnelID
Class PersonnelID Job
Service %Available Business Unit
Bundle Provisioned Assets Section
Call volume
Call minutes



The Dimensions have been model to the lowest level of granularity. The Personnel Dimension offers no problems with the hierarchy; a staff member only belongs to one job, business unit & section. This is not the case for Assets. All Assets are assigned to a member of staff. An Asset is a member of a class --mobile, pc, server,...--, one or many services and those services are member of one bundle.

"Regarding the many-to-many relationship, you are saying that the fact table has a relationship to an asset, the asset belongs to multiple services, and a service belongs to a single bundle. Is that correct "

Yes. The IT Business Unit offers 5 bundles of services. Each bundle is comprise of mutually exclusive services. An example would be the End User Computing Bundle comprising of Desk Support, Printing & Fax, etc.

"Also, what is the relationship between the fact and the service Does a single fact record associate to a single service or does it associated to multiple services through the asset "

One or multiple through the asset. Each Asset is tired to at lest one service. Assets such as servers offer more than one service.

I appreciate your response and am happy to supply additional information.





Re: many-to-many parent-child relationship in the dimension hierarchy

Bryan C. Smith

According to Kimball methodology, a fact table should represent a single business process. So in this data mart, you would have a fact table for the handling of service tickets, another one for provisioning, another one for asset uptime/availability, and so on and so on. You might bring all this data together in a report using conformed dimensions between the fact tables. I'd recommend taking a look into that. It will make your maintanance of the data much, much easier. (You can easily tie all this data together in your olap cube so your users won't have to jump between fact tables aka measure groups.)

When you do that, I think you will find that some facts relate directly to assets while others relate directly to the services.

Still, you will have assets providing services and need to model that relationship. I'd recommend building a service group table that ties together the services being offered. Then associate an asset with a service group. Here's a rough example:

Asset (AssetID, ServiceGroupID, ..., StartDate, EndDate)

ServiceGroup (ServiceGroupID, ....)

ServiceGroupServiceJunction (ServiceGroupID, ServiceID, ....)

Service (ServiceID, ....)

So, if you had a fact associated with an asset, you would be able to identify what services are provided by that asset. If you are working with Asset as a Type 2 slowly changing dimension, there would be different records for an asset in the Asset table based on the ServiceGroups it was associated with across time.

If you have a copy of "The Microsoft Data Warehouse Toolkit", this is covered on page 60. (I think this is covered in "The Data Warehouse Toolkit" as well but someone has borrowed by copy.)

Hope that helps,
Bryan






Re: many-to-many parent-child relationship in the dimension hierarchy

IanMeikle

Bryan,

Thanks for your suggestions and insight. I will have a read of the books mentioned and most likely return with a few questions...hopefully your generosity will continue to provide answers.

Ian




Re: many-to-many parent-child relationship in the dimension hierarchy

Deepak Puri

Assuming that you're using AS 2005, the many-many relationship between Assets and Services could be modelled, if separate Asset and Service dimensions are set up. There is 1 measure that isn't just additive, and hence may need further fields in the fact table: "the percentage an Asset was available". Generally, availability is a ratio like available to total time. Even in the simplest scenario, the numerator and denominator would be separately summed (say across Assets in this case), then their ratio taken. So, if the fact table has either "%Available" or "AvailableTime", and "TotalTime", the aggregate "%Available" could be computed.

The schema for AS 2005 could then be something like:

PersonnelDim SupportFact AssetDim Asset<->ServiceBridge ServiceDim

AssetID --> AssetID <-- AssetID

PersonnelID <-- PersonnelID Asset ServiceID --> ServiceID

Job AvailTime Class Service

Business Unit TotalTime Bundle

Section Provisioned

Call Volume

Call Minutes

There would be a Measure Group on SupportFact, with "sum" measures: AvailTime, TotalTime, Provisioned, Call Volume, Call Minutes.

["%Available] could then be defined as: AvailTime / TotalTime.

The Personnel and Asset dimensions would have a regular relation to the SupportFact Measure Group.

An intermediate Measure Group is then defined on the Asset<->ServiceBridge table, to which Asset and Service dimensions are related.

This would allow the Service dimension to have a many-many relation to the SupportFact Measure Group, via this intermediate MG.