Fact tables and collections
2 posters
Page 1 of 1
Fact tables and collections
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.
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
RE: Fact tables and collections
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.
I hope that helps to get started.
MTomasura- Posts : 4
Join date : 2011-10-12
Location : Scranton USA
RE: RE: Fact tables and collections
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.
Sorry for my most likely naive questions and concerns.
hadam- Posts : 2
Join date : 2011-12-12
RE: RE: Fact tables and collections
I would start with defining the Service dimension first.
MTomasura- Posts : 4
Join date : 2011-10-12
Location : Scranton USA
Similar topics
» Does it belong in the stage tables or fact tables?
» Difference between Aggregate Fact Tables, Rollup Tables and OLAP Cubes ?
» Storing Date Keys in dimension tables versus fact tables
» Multiple Fact tables, Aggregate tables or a different approach
» Number of Columns in Fact Tables vs. Dimension Tables
» Difference between Aggregate Fact Tables, Rollup Tables and OLAP Cubes ?
» Storing Date Keys in dimension tables versus fact tables
» Multiple Fact tables, Aggregate tables or a different approach
» Number of Columns in Fact Tables vs. Dimension Tables
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum