February 22, 2011 at 9:46 am
I am currently working as a SQL ETL developer for an Investment Bank in the Netherlands. The bank has recently been taken over and thus the financial product reporting has been divided into various reporting streams which has meant various feeds have had to be created to supply the various new stake holders of the bank. Whilst we are able to produce these feeds I feel the current database architecture (set up by my predecessor) is perhaps not the best solution. This is simply because I have had to constantly tweak the tables to get the desired output.
The database architecture is such that our sql servers are primarily used for ETL. The source data comes from Hyperion enterprise Management system which countries use to load their figures. The source data is uploaded into DDM (our SQL ETL environment) transformed and loaded into Essbase cubes. Due to there being copies of data in both Essbase and Hyperion only the latest MI data is stored in SQL Server.
I am of the feeling that if we had a central repository of the data in sql, i.e. a data warehouse. It would give us the flexibility to provide feeds via datamarts for all our customers as well as handling the metadata, such as product hierarchies and location hierarchies which are used as dimensions in our OLAP cubes.
I have attached a document showing the format of our flat file source data
We receive about 11 of these files each corresponding to a different region for reporting. In addition to these files we also receive once a month meta data from our offshore team. We thus have a window of maintenance were implement the new meta data changes. These include the new product hierarchy structure, location structure and FX rates. The location and product structure are then used to build the product and location dimensions in our Essbase models. These dimensions are both parent child dimensions.
I would imagine we would have fact table that shows the monthly amounts for each product in each category. Our dimensions would be product, account and location. For our reporting needs here analyst require YTD figures. Should this be included in the fact table or should the aggregation be done in the cube or a view?
Any advice or information you could provide would be very much appreciated.
February 22, 2011 at 12:31 pm
FACT tables should store data at granular level, aggregations can be done either on-the-fly or in cubes for recurrent ones.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 22, 2011 at 2:27 pm
Thanks for that information.
I also have another question. Once the source tables are loaded into the our staging area. I will probably have to use lookups to transform some of the data. Would the lookups be the same as the dimensions? For example the product dimension and account dimensions are hierarchical. Would i have to create a separate table for the product lookup or could i use hierarchical dimension as the lookup?
February 25, 2011 at 2:31 pm
eseosaoregie (2/22/2011)
I also have another question. Once the source tables are loaded into the our staging area. I will probably have to use lookups to transform some of the data. Would the lookups be the same as the dimensions? For example the product dimension and account dimensions are hierarchical. Would i have to create a separate table for the product lookup or could i use hierarchical dimension as the lookup?
Lookups are always based on DIMension tables, some ETL tools like *informatica* will build a lookup table on-the-fly (always based on underlying DIMension table) just to be used during the specific mapping.
I wouldn't create permanent separate lookup tables just to help during ETL, load source data into Staging, do whatever you have to do including lookups against proper DIMensions then move the data to core FACTual and DIMension tables as needed.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 25, 2011 at 2:34 pm
Many thanks for that. Will let you know how i get on.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply