Multiple types of datasources in PowerPlay Transformer

[devcog]

Hi,

Can we have datasources from different databases in a single model in PowerPlay Transformer...?
For eg: Access, Oracle, Excel, etc...

Thanks,

 

[jobjoris]

Yes! That's one of the great advantages of Powerplay.

Just keep in mind that the lowest level catagories in all dimensions are unique.

 

[devcog]

Hi,
Thanks for the tip...
Now I am facing a problem with my cube:
The data for the cube is fed from two different types of data sources:
> Access
> Oracle

The fact table exists in Access database while the dimension tables are in Oracle.
Moreover, the dimension tables have a snow-flake structure. And the fact table contains reference key for only one of the dimension.

The cube must have all the dimensions. But, I am not able to associate the measure to the remaining dimensions which don't have a reference in the fact table.

Please let me know of a solution or approach, if any

Thanks,

 

[drlex]

devcog,
Are you able to alter the access database? If so, you could link the tables from Oracle into the access database and use this as the datasource for your impromptu catalog and create the iqd(s) required for transformer.

(If you are wary of amending the access db, you should be able to create another access db and link both the Oracle and existing access db to it)




soi la, soi carre

 

[jobjoris]

The fact table exists in Access database while the dimension tables are in Oracle.
Moreover, the dimension tables have a snow-flake structure. And the fact table contains reference key for only one of the dimension."

If the Fact table contains only a reference key for only one of the dimensions, how could you ever be able to link it to the other dimensions? Wether or not in an IQD, it's impossible to associate your measures to non-existing references I would say.

 

[GJParker]

Devcog

All you need to do is create one datasource from the oracle database containing all of the dimensional data, as long as one of the fields has a link to the Access datasource the measure will then be related to all dimensions.

jobjoris

Imagine this scenario

Access Database

Order Value Table

Field 1 Order No.
Field 2 Sales Value

Oracle Database

Order Detail Table

Field 1 Order No.
Field 2 Customer Code
Field 3 Item Code

Customer Table

Field 1 Customer Code
Field 2 Customer Name
Field 3 Rep Code

Item Table

Field 1 Item Code
Field 2 Item Name

As you can see the access table has only one direct reference to Order No on the oracle database, but this can then be used to return more information for use in seperate dimensions of a transformer model.


Gary Parker
MIS Data Analyst
Manchester, England

 

[devcog]

Hi All,

Thank you very much for the tips...

I linked the Oracle tables (dimension tables) in Access and managed to generate the cube from Access query.

Thanks,