This content has been marked as final. Show 4 replies
It depends on how much logic you have in your source sql-view and will it take longer to return all the records during dimension refresh or cube load.
In some cases it is slower to load a cube from a sql-view compared to a table (created from the same sql view).
So as a best practice I always truncate/insert source tables from all source sql views, and then load all the dimensions and cubes. In AWM, I use the source tables for mappings.
In your dimension/cube loading sql procedures, you can do the truncate/insert statements before doing DBMS_CUBE.BUILD command.
Thanks you very much for your reply.
I got the hint from your reply, what I am expecting.
I am desiging a datawarehouse.
I may use ETL tool or SQL scripts for data loading from Source to my target datawarehouse.
I am not worried about truncate/insert or update/insert, becaz I have to use only truncate/insert only.
Last week there was big debate between me and one of my client manager:
I said the best practice is to use tables rather than views in our mapping or SQL scripts for data loading from source to target database, unless it is very complex. He says nothig like that.
Can anyone help me in this regard.
If I understand it correctly, the debate here is whether to create star-schema tables first and then "map" those to olap dimensions and cubes OR just create sql-views on top of source (possibly normalized) data and then "map" those sql views to olap dimensions and cubes.
Generally cube loading performance is better when loading from tables. The other two advantages are: (a). you can backup the source tables (which are mapped to olap cubes) in case you need it later on for some reason, (b). more control over what records to load into olap cubes.
But you can certainly create sql views against source (normalized) data and mapped those to olap cubes and dimensions if there is not much performance difference.
Its all your decision.
My preference will be creating target tables first.
On a side note, since you mentioned ETL tool:..... OWB has built-in capabilities to create and maintain Oracle OLAP cubes since version 10gR2, and ODI has knowledge modules (RKM Oracle OLAP, IKM Oracle AW Incremental Update) since 10gR3 version to maintain Oracle OLAP structures.
So if you are using one of these ETL tools, then look at these features also.