Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Dimension too big to move it to the datamart
- Thread starter hpaille
- Start date
johnherman
MIS
From a business delivery point of view - why not? It's our job to deliver what the end users need. They should have no care about where the data is coming from as long as their requirements are met (access time, ad-hoc queries, etc).
From an implementation point of view, there are probably quite a few obstacles to overcome. For instance, the ODS and datamart are probably on different databases, servers. The naming standards may be different. Data elements may have different formats (char vs varchar, number vs integer0 and different sizes. The update time and frequency between the ODS and datamart may be hours or days apart, resulting in possible orphan or mismatch records. Making the cross database/platform join may not be possible with existing query tools. I think you get the idea.
In summary, remember it is our mission to meet the user community's requirements. They should have no care how we do that. In addition, remember that all the DW standards and architecture guidelines are based on solutions already implemented. You may need a new breed of solution.
-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright
From an implementation point of view, there are probably quite a few obstacles to overcome. For instance, the ODS and datamart are probably on different databases, servers. The naming standards may be different. Data elements may have different formats (char vs varchar, number vs integer0 and different sizes. The update time and frequency between the ODS and datamart may be hours or days apart, resulting in possible orphan or mismatch records. Making the cross database/platform join may not be possible with existing query tools. I think you get the idea.
In summary, remember it is our mission to meet the user community's requirements. They should have no care how we do that. In addition, remember that all the DW standards and architecture guidelines are based on solutions already implemented. You may need a new breed of solution.
-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright
- Thread starter
- #3
johnherman
MIS
What are the differences for the ODS and Datamart?
Database and version? Server and ServerOS? Update frequency and latency?
Another idea I just thought of.....might have benefits...
Have part of your dimension in the datamart (the most popular values of the domain) and the rest in the ODS overlaid by a View which is a UNION query of the two tables. Might have benefits if your data is predictable as far as user access "popularity".
-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright
Database and version? Server and ServerOS? Update frequency and latency?
Another idea I just thought of.....might have benefits...
Have part of your dimension in the datamart (the most popular values of the domain) and the rest in the ODS overlaid by a View which is a UNION query of the two tables. Might have benefits if your data is predictable as far as user access "popularity".
-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright
my question is what you define as very big? If the number of records are the issue then depending on what platform your on then there are bound to be methods or tools to address this.
linking the servers will defintely come with a performance hit not only to the server resources but also to the network resources for the part of the network the servers exist on. If the number of records again is the issue then it seems like over the long term you will waste more time and resources feeding data over a link if users are requesting large numbers of dimensional records on a regular basis.
a few more details on the reasoning and size of the data might propmt more creative solutions.
Shoot Me! Shoot Me NOW!!!
- Daffy Duck
linking the servers will defintely come with a performance hit not only to the server resources but also to the network resources for the part of the network the servers exist on. If the number of records again is the issue then it seems like over the long term you will waste more time and resources feeding data over a link if users are requesting large numbers of dimensional records on a regular basis.
a few more details on the reasoning and size of the data might propmt more creative solutions.
Shoot Me! Shoot Me NOW!!!
- Daffy Duck
- Status
Similar threads
- Locked
- Question
- Locked
- Question