April 13, 2010 at 6:02 am
Hi there,
Quickie - I hope 🙂
Is it acceptable to duplicate the same fact table(s) in different data marts?
i.e. I have two data marts that "share" fact tables but are part of different business processes and would therefore prefer to have different data marts.
Or should I just go for one datamart?
Thanks!
April 13, 2010 at 9:01 am
One datamart per FACTual table, no FACTual table duplicates.
_____________________________________
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.April 13, 2010 at 9:14 am
Thanks Pablo!
And taking it further to the cube level... Would you also stick with a single datamart feeding a cube or is it acceptable to having multiple marts as datasources?
April 14, 2010 at 8:23 am
For anyone coming across this post, below is an article worth looking at:
http://prologika.com/CS/blogs/blog/archive/2006/06/27/1331.aspx
April 14, 2010 at 8:44 am
TVR (4/13/2010)
...taking it further to the cube level... Would you also stick with a single datamart feeding a cube or is it acceptable to having multiple marts as datasources?
Here is my personal point of view.
I see cubes as nothing but a delivery layer. Most queries require data from a single datamart so related cubes will be fed by a single datamart but some queries require data for more than one datamart so I see as a natural occurrence to have cubes fed by multiple datamarts.
_____________________________________
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.April 15, 2010 at 10:13 am
I would consider putting everything in 1 DataMart, and perhaps making 2 perspectives from that Datamart, one for each distinct group of reports/users.
May 12, 2010 at 11:57 am
Is it acceptable to duplicate the same fact table(s) in different data marts?
Yeah, of course it's acceptable.
But there are plenty of reasons why it wouldn't be advised, and I'm not going to list them here, you know what they are.
It might be the right thing to do in your case, if the benefits of doing so overcome the overheads you incur. (Processing, storage, administration, maintenance, etc etc).
May 13, 2010 at 1:02 pm
peter.j.hanlon (5/12/2010)
Is it acceptable to duplicate the same fact table(s) in different data marts?
Yeah, of course it's acceptable.
But there are plenty of reasons why it wouldn't be advised, and I'm not going to list them here, you know what they are.
:blink: No. We do not know.
I would love to know how to present to the business the need of having a duplicate of a 2 billion rows fact table in the datawarehouse. 😀
Those are the facts of the company! -- there is just one version of them 😎
_____________________________________
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.May 13, 2010 at 2:28 pm
Well I did say that it wasn't advised. 😉
Of course to answer the question 'is it acceptable?', you have to ask to whom?
One of my current Production systems for an end client, runs on a clustered server with data storage on Raid 10 SAN. I'm no infrastructure expert, but that means I'm storing the main table, 1.55Billion rows (600Gb), twice, and I've bougght two servers for the cluster.
That's acceptable for the Storage guy who specified the SAN and the DBA who specified the servers. Why is that any didfferent to having two seperate, non clustered servers, each with a copy of the fact table.?
OK, I'd have to amange the two version myself, instead of it being handled automatically, so I incur a support overhead, in managing it, and of course they'll get out of sync at some point and I'll have to put it right.
So if it's acceptable, of a sort to the IT guys, then it's easy to construct an arguement that a business user would want a seperate copy. They usually want all kinds of things with very little thought to why not. "If it can be done, then get on and do it."
As for one version, Agree, but that doesn't prevent you holding two copies.
So, Acceptable to whom? I'm sure I would come across situations where I would find it acceptable to hold a second copy. I'm sure if I thought hard enough I could think of an example I have allowed to happen... and that's not including aggregate tables, which are a copy of sorts, and Jeez I've created hundreds of them in my time...
May 13, 2010 at 2:32 pm
Aggregate tables are a delivery layer and not a copy of a factual table 😉
_____________________________________
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.Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply