Newbie - designing data warehouse for cube
Page 1 of 1
Newbie - designing data warehouse for cube
Hi,
I have been asked to design a data warehouse that is to be populated by ETL SSISs, then i must design a cube from this data warehouse which a Sharepoint site will point to. My place of work is a statistics gathering organization and the data on the database i need to 'translate' into the data ware house is gathered on what we call 'returns' (rather like online spreadsheets filled in by reporting institutions). these returns are filled in by the institutions reporting to us weekly, monthly, quarterly, annually etc. There are a few obvious (I think) dimensions for all our fact tables - Reporting period (say 31st December 2010 would be the date for quarter four 2010 data), institution (maybe a hierarchy of institution type and institution), return type (there are many different returns sent to us from many different institutions, always a specific list of institutions report a specific list of returns. the data on these returns is tabular, like an excel spreadsheet with lots of worksheets. each worksheet is what we call a form on a return. I am not sure if form is a dimension though, i think it is just an organizational tool used to display and gather the data. The actual data is varied, usually numeric data but the actual meaning of the figures is what confuses me. Some forms have one column of data, and each row (there may be 20 or 30 say) represents a totally different figure. Does that mean i should have 20 or 30 fact tables as the figures do not represent the same information. (i.e. it's not a simple fact table with region & product dimensions with a 'sales figure' fact). Some of the forms DO fall nicely into a fact / dimension relationship (investment type dimension, investment amount fact) but generally the column headers are the more generic 'amount' which is a monetary amount that could mean anything depending on the row header. Sorry if this is very long winded and confusing. Some work has been done on other sets of returns already and the table design seems ... odd ...to me. there is a 'return fact' table that holds the institution, reporting date, return type and other attributes. And this is foreign keyed to by a 'investment fact' table which just has the primary key of the return fact table in it, and some other amounts. i don't think the amounts / facts are described at all by the dimension 'return' i think they are described by all the dimensions on the current 'return fact' table which i don't think is a fact table at all. but as i'm new to this i don't want to jump in and say the current design is wrong! Any advice is greatly appreciated.
I have been asked to design a data warehouse that is to be populated by ETL SSISs, then i must design a cube from this data warehouse which a Sharepoint site will point to. My place of work is a statistics gathering organization and the data on the database i need to 'translate' into the data ware house is gathered on what we call 'returns' (rather like online spreadsheets filled in by reporting institutions). these returns are filled in by the institutions reporting to us weekly, monthly, quarterly, annually etc. There are a few obvious (I think) dimensions for all our fact tables - Reporting period (say 31st December 2010 would be the date for quarter four 2010 data), institution (maybe a hierarchy of institution type and institution), return type (there are many different returns sent to us from many different institutions, always a specific list of institutions report a specific list of returns. the data on these returns is tabular, like an excel spreadsheet with lots of worksheets. each worksheet is what we call a form on a return. I am not sure if form is a dimension though, i think it is just an organizational tool used to display and gather the data. The actual data is varied, usually numeric data but the actual meaning of the figures is what confuses me. Some forms have one column of data, and each row (there may be 20 or 30 say) represents a totally different figure. Does that mean i should have 20 or 30 fact tables as the figures do not represent the same information. (i.e. it's not a simple fact table with region & product dimensions with a 'sales figure' fact). Some of the forms DO fall nicely into a fact / dimension relationship (investment type dimension, investment amount fact) but generally the column headers are the more generic 'amount' which is a monetary amount that could mean anything depending on the row header. Sorry if this is very long winded and confusing. Some work has been done on other sets of returns already and the table design seems ... odd ...to me. there is a 'return fact' table that holds the institution, reporting date, return type and other attributes. And this is foreign keyed to by a 'investment fact' table which just has the primary key of the return fact table in it, and some other amounts. i don't think the amounts / facts are described at all by the dimension 'return' i think they are described by all the dimensions on the current 'return fact' table which i don't think is a fact table at all. but as i'm new to this i don't want to jump in and say the current design is wrong! Any advice is greatly appreciated.
AmandaWoods- Posts : 7
Join date : 2011-04-13
Similar topics
» data warehouse or not ? when is it okay to use OLAP without a data warehouse database
» Designing a data warehouse to store academic publications for natural language processing
» Ebook The Data Warehouse Lifecycle Toolkit, 2nd Edition: Practical Techniques for Building Data Warehouse and Business Intelligence Systems
» Newbie Creating a Data Mart
» data warehouse and data warehouse system
» Designing a data warehouse to store academic publications for natural language processing
» Ebook The Data Warehouse Lifecycle Toolkit, 2nd Edition: Practical Techniques for Building Data Warehouse and Business Intelligence Systems
» Newbie Creating a Data Mart
» data warehouse and data warehouse system
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum