Tuesday, February 17, 2015
Creating Cubes with Oracle OLTP databases A test scenario with synonims
This is article is written based on below queries before I explore it.
1) Is it mandatory to have Star Schema based warehouse to create Cubes ?
2) In oracle if you have multiple users(lets say multiple schemes).. Is it possible t design a simple CUBE dimension from schema A of a table and measure from schema B of another table ?
The answer is , NO & Yes.
All the organizations may not maintain complete data ware housing solutions and run their transactional database using normalized OLTP models.
It is not mandatory to have a star schema based ware house to create OLAP cube to analyze your data but you can also use OLTP databases.
The new things I found while demonstrating a simple cube are..
1) Connecting to oracle database.
2) Fetching all the tables of all schemas.
3) Synonyms of a particular schema
You will find a lot of time taking to load the database into PSW tool once you connected to a schema.. Using FILTER_SCHEMA_LIST at Options section of the database dialogue will take the control to load only specified schema tables.
By default the PSW engine assumes to load all the tables in all the schemes.
A scenario of Dimensions and measures.
Suppose in a case, if you have a measure coming from table defined in SchemaA, measure coming from a table defined in SchemaB... in that case while giving tables for fact and dimension tables, the synonyms will not appear in drop down boxes...
You need to hard code the tables names ... Of course your dimensions & measures will be in X with red color, it will not affect your definition of hard coded table names.
Save the schema and publish it to the server... have tested it with Saiku plug-in ad-hoc editor and worked nicely.
Thank you.
References :
1) http://jira.pentaho.com/browse/PSW-135
2) http://forums.pentaho.com/showthread.php?162540-OLTP-tables-VS-RDBMS-tables-Designing-OLAP-cubes-Peformance-factors&p=374714#post374714
3) http://forums.pentaho.com/showthread.php?157997-Schema-Workbench-freeze-after-establishing-a-connection-in-db
1) Is it mandatory to have Star Schema based warehouse to create Cubes ?
2) In oracle if you have multiple users(lets say multiple schemes).. Is it possible t design a simple CUBE dimension from schema A of a table and measure from schema B of another table ?
The answer is , NO & Yes.
All the organizations may not maintain complete data ware housing solutions and run their transactional database using normalized OLTP models.
It is not mandatory to have a star schema based ware house to create OLAP cube to analyze your data but you can also use OLTP databases.
The new things I found while demonstrating a simple cube are..
1) Connecting to oracle database.
2) Fetching all the tables of all schemas.
3) Synonyms of a particular schema
You will find a lot of time taking to load the database into PSW tool once you connected to a schema.. Using FILTER_SCHEMA_LIST at Options section of the database dialogue will take the control to load only specified schema tables.
By default the PSW engine assumes to load all the tables in all the schemes.
A scenario of Dimensions and measures.
Suppose in a case, if you have a measure coming from table defined in SchemaA, measure coming from a table defined in SchemaB... in that case while giving tables for fact and dimension tables, the synonyms will not appear in drop down boxes...
You need to hard code the tables names ... Of course your dimensions & measures will be in X with red color, it will not affect your definition of hard coded table names.
Save the schema and publish it to the server... have tested it with Saiku plug-in ad-hoc editor and worked nicely.
Thank you.
References :
1) http://jira.pentaho.com/browse/PSW-135
2) http://forums.pentaho.com/showthread.php?162540-OLTP-tables-VS-RDBMS-tables-Designing-OLAP-cubes-Peformance-factors&p=374714#post374714
3) http://forums.pentaho.com/showthread.php?157997-Schema-Workbench-freeze-after-establishing-a-connection-in-db
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.