November 12, 2015 at 4:39 am
HI All,
I have a particular DW design question that I want to run by you?
We have a requirement to build over 100 internal Data Marts and OLAP cubes for clients we work with. Each clients data is different, houses different type of transactional data and different grains etc., also the dimensions that the cube will interact with will differ from client to client. e.g.. one may use a product dimension while one may not. Hence, the cube structures will look different for each client. We would like to create a data-driven ETL and OLAP design pattern to handle this requirement.
So having a mapping table to which has the cube structure, the dimensions etc. And from there being able perform ETL and create the SSAS xmla cube structure and deploy.
My question, is there any design pattern or examples I could reference to achieve such a design? In a nutshell being able to create Data Marts and OLAP dynamically based on a set of mapping\configuration tables?
Have any of you done something similar or have created these mapping tables? Any ideas on what they will need to look like will be greatly appreciated.
Thanks Ashal
November 16, 2015 at 9:58 am
There are some third-party tools that claim to generate all of these things based on the metadata you supply. From what I've seen of those (my opinion only), making everything completely agnostic from a code generation standpoint is almost as cumbersome as developing it from scratch (plus many times I just don't like the code that is being generated).
I'm currently busy with my own little pet project, that will take a source-to-target mapping document (in Excel), and create SSIS packages, table scripts and dimension/fact load scripts from the metadata...using mostly BIML for the SSIS portion. The tricky part to building these types of solutions is to determine how far you should take the code generation piece. You could probably get a 60% solution and take the most mundane tasks (generating tables, base SSIS packages and to some extent the load scripts) out of the equation...but anything more than that and you get into a scenario where capturing/storing the metadata becomes an administrative nightmare.
To answer your question directly: Yes, there are some third-party tools but I would evaluate them very closely before deciding to use it. There are just so many variables involved when creating data marts or data warehouses, and even from your description it seems like no 2 solutions would be the same.
Your best bet in my opinion would be to identify those mundane and time consuming tasks you could automate, and then do so based on your standards and principles. Getting to a 60% solution quickly while enforcing naming conventions etc., and without create an administrative nightmare is already a good step towards a win from my point of view.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply