Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Fact tables and collections

2 posters

Go down

Fact tables and collections Empty Fact tables and collections

Post  hadam Tue Dec 13, 2011 4:15 am

We have following scenario which we would like to model in a DWH. A service generates periodically measurement outputs. The output format depends on the corresponding service. A measurement output can be a simple type (integer, double, Boolean, string) but can also be a combination of simple types or complex types. A complex type can contain simple and complex types again. The measurement format is fixed for a certain service. Multiple services can have the same output format. However, a new service can have a currently unknown output format (which has to be defined at service creation time). For instance the service get_disk_usage generates following output every 5 minutes:
executiontimestamp (date),
[mountpoint (string), used (long), free (long), total (long)]*
Thus, a get_disk_usage output contains at least an executiontimestamp and a variable number of data values which depends on the number of mounted file systems.
We are rather inexperienced in the art of DWH modelling and are thankful for any suggestions.

hadam

Posts : 2
Join date : 2011-12-12

Back to top Go down

Fact tables and collections Empty RE: Fact tables and collections

Post  MTomasura Tue Dec 13, 2011 1:03 pm

I don’t know all of the details but it sounds as if you may need a field to classify the data types for the Service. You can create a Service dimension with the attributes and create a relationship with the fact table (periodically measurement outputs) on a surrogate key so you can track the changes to the services. To handle the data types at a DB level you may have to create multiple fields for each parameter or setup multiple tables. The ETL would have to handle the delivery to the correct field.

I hope that helps to get started.

MTomasura

Posts : 4
Join date : 2011-10-12
Location : Scranton USA

Back to top Go down

Fact tables and collections Empty RE: RE: Fact tables and collections

Post  hadam Thu Dec 15, 2011 8:02 am

Thank you for your fast response. Did I get you right - you suggest to create an own fact table for each different measurement output type? In our system a new measurement output could be defined together with a new service. Thus, we do not know all possible types and would require an ETL process that can detect new types and automatically creates new fact tables. This seems rather complicated to me. Moreover, we would require somehow a mapping such that our reporting tool knows the correct table to look for the outputs. Finally, since a complex type can consist of 0..n types (simple and complex) I do not know how to model that besides using multiple table entries for a single measurement output. Where could we store these entries? Don’t get me wrong – in a normal database application we know how to address these issues. But in the context of DWH we are not so sure.
Sorry for my most likely naive questions and concerns.

hadam

Posts : 2
Join date : 2011-12-12

Back to top Go down

Fact tables and collections Empty RE: RE: Fact tables and collections

Post  MTomasura Thu Dec 15, 2011 10:03 am

I would start with defining the Service dimension first.

MTomasura

Posts : 4
Join date : 2011-10-12
Location : Scranton USA

Back to top Go down

Fact tables and collections Empty Re: Fact tables and collections

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum