Matching cubes to databases - redefine cube over and over?

[dean12]

This is a procedural or philosophical question. I've got multiple SQL2000 databases, call them DB1 and DB2 and I have multiple ODBC entries that point to those datbases. In my software, I use which ever ODBC the user says to use and I connect to the appropriate database.

It looks to me as if I will have to define multiple cubes that are all the same but relate to their respective databases "IF" I intend to be able to use any of the cubes at a moment's notice. I don't think that changing the data source on a cube and doing a refresh or process is going to be effective.

So the question is: Anyone run into this kind of environment and how did you approach handling this?

Appreciate the feedback.

 

[ram1975]

If the both the fact tables structure in both the databases is same and if you want to use that in the same cube, you can create a partition in the cube. i.e. different partitions can refer to different fact tables.

 

[MDXer]

Let me see if I understand what your trying to accomplish.

Current:
You have DB1 DB2 and DB3 the user can specify what DB they want to run their reports against. So user 1 could report against DB1 while user 2 can run the same reports against DB3 and get totally diffrent results.

Goal:
You want to allowing the current reporting against diffrent Data Sets but within a Cube Structure.

Am I close?

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[dean12]

Yea, you are just about on target given the information I provided.

We report using Crystal Reports and when that happens we are able to change the data source on the report so that it draws data from one of a number of different (but schema identical) databases.

Ultimately what I am looking at is developing reporting in Crystal Analysis. I have not even looked at this product so I don't know how I will change from one cube to another.

But, I was guessing that I would have to have separate cubes just like I have separate databases. This guess may be right or wrong, in the context of Crystal Analysis reporting.

Here's what happens when you ask a question and don't really have all the underlying information together to phrase the question properly.

 

[MDXer]

Yes you could maintain seperate cubes, which may actually prove to be easier to implement but more dificult to adminiter. Another path you may want to investigate is to create a Dimension Called DataSources, with members DB1, DB2 etc, add a column to your fact records that would hold this key. your Olap DB would require multiple datasources you could then have 1 cube or multiple cubes brought together into a virtual, and by selecting the Data source member in the Data source dimension you would be able to call the data for just that ds. It's just an idea that may be worth investigation. The less to adminitor the less to break.



"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck