Nasar's Blog

Importing Analytic View through BI Admin tool

  1. Opened up existing RPD and added a new database for “Oracle Analytic View” with datasource set to (description=(retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=…. This was actually autonomous database in cloud.
  2. Did ‘Load Java data sources’ in BI Admin Tool and then ‘Import Metadata’ for this new data source.
  3. It only showed regular sql objects (tables, views etc.) and not the Analytic Views – which was surprising.
  4. Closed the rpd file. NOTE: did not exit out of BI Admin Tool.
  5. While the ‘Load Java Data source’ was still active, I created a new ‘Repository’ and then did the same step 1 and step2. This time it showed ‘Analytic Views’ and I was able to import it.
  6. So there is something in existing RPD file because of which Analytic Views were not showing up.
  7. I opened up another BI Admin Tool window and then (with two BI Admin Tool windows side-by-side) manually created new database and new connection pool and all the rest of the objects in the old RPD file. Note that first we define a Phyaical Catalog (like ‘EDW’) and then Analytic View is defined as “Oracle OLAP Analytic Workspace” under that Physical Catalog , and finally dimensions+Hierarchy as “Oracle OLAP Dimension” and finally Measures folder is defined as “Oracle OLAP Cube”.
  8. Manually defined all ‘Physical Joins’ between dimensions and Measures.
  9. In the ‘General’ tab of each “Oracle OLAP Dimension” – External name has to match AV hierarchy name. Columns are the columns which are member_name and member_description columns (like ID_01, NAME_01 etc.) for all levels.
  10. In the ‘Hierarchies’ tab of “Oracle OLAP Dimension” – define the hierarchy as “Level-based Oracle OLAP Hierarchy”. Define all the levels, starting from top down (ie., LEVEL1, then LEVEL2, ..) During each level’s definition, External Name has to matrch the level name in AV. And whatever column (Oracle OLAP Column) you add/select first, it will become key of that level. For example if you select ID_01 column for LEVEL1 then ID_01 will become key. If you select another column for that level (for example NAME_01) then that that will not be key.
  11. During every definition select VARCHAR and 4000 and nullable to true.
  12. Create ‘Oracle OLAP Cube’ called ‘Measures’. It should be exactly this name ‘Measures’. Add all stored and calc measures from Analytic Views into this ‘Measures’ cube . Each measure will be added as ‘Oracle OLAP column’ inside ‘Oracle OLAP Cube’ called ‘Measures’. Data Type is ‘NUMERIC’ and Nullable is true.
  13. When done with Physical Layer work, just drag ‘Oracle OLAP Analytic Workspace’ object from physical to BMM layer and then Presentation layer.

Timeline of events