Multiple data warehouse

[Ppol]

Hello,
After a merge, we have several huge data warehouses coming from absorbed companies.
Is there a way to have a seamless access to all those data warehouse without a new data integration into a new entreprise data warehouse ?

I heard about MDX or SOA, would that be a solution.

Thanks

 

[RiverGuy]

What do you mean by seamless access? If you/they/whoever set up these other data warehouses to be accessible from your network, then I suppose your access would be seamless. If you're talking about viewing multiple data warehouses as one data warehouse, then I wouldn't want it to be seamless. There's no doubt that unless these are vendor-provided canned data warehouses that are black boxes, then business rules, measure definitions, dimensions and pretty much everything else will be different.

 

[johnherman]

Sorry. You need to develop a series of conformed dimensions and facts. Consider the "lowest common denominator" approach. Determine which dimensions and attributes exist across all companies. Develop a strategy to conform, convert, or translate the different values to a standard set of values. Do the same for your facts. This will be your conformed, integrated data warehouse. Instead of using actual storage, you can attempt to duplicate the conforming activities using views, but the performance burden may be too much.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[Ppol]

Thanks

By seamless, I meant as if it were a single data warehouse.
For instance if i have an MDX client, multidimensionnal cubes hosted in MS SQL Server or SAS , you would be able to access these cubes, am i right ?

 

[RiverGuy]

MDX is just a language to interact with the multidimensional databases. So, if your front end tool supports all the different data warehouses, the best you can hope for is using the same tool to connect to different databases. To combine them together, you really need to integrate them.

 

[johnherman]

One of the basic tenets of DW is to trade disk space and off-hours processing for ease of use and query response time. In this case, it's worth investing in the disk space to create redundant, but standardized, data at the lowest common fact and attribute level which can be shared across all the different, but similar data stores.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[Hans63]

At the very least you have to investigate, as johnherman pointed out, what the conformed dimensions are. How can you report across those DWH's.
After that you can use reporting tools like Cognos or Business Objects to gather data from several DWH's into 1 report.
I seriously doubt wether you will be able to answer all queries this way. For those queries you can consider setting up a databaseconnection between teh DWH's and define several views to use with reporting.

BUT i would urge you to see this as a temporary solution and to head for real integration. Get all information into 1 DWH as it makes maintainability so much easier.