How Do You start Building up your DataWarehouse & Reporting Environment (Mark Rittman, New Orleans)

The final day of New Orleans 2 different in-depth sessions were organized: 'Oracle Tools-Application Expres/ADF' and the other one 'Business Intelligence'.

I decided to go to Mark Rittman's session regarding 'Advanced Development Techniques Using Oracle BI Suite Enterprise Edition Plus'. It was the first time I had the opportunity to follow a presentation of Mark Rittman and as you could probably figure out yourself, it was very interesting.

So let's start diging into Oracle BI Suite Enterprise Edition Plus ... I will give you all the Tips & Tricks (T) and Watch-Out's (WO) Mark has shared with us.

1. Use BI Administrator to define the logical, physical and presentation layer:

• T1: Denormalize dimensional tables into 1 single logical table
• T2: Data needs to be as close to a star schema as possible
• T3: Best Approach: Define logical layer first to define labels and names correctly and then define the map for the physical layer
• T4: Use 'Direct Query' in 'Oracle Answers' to load Discoverer Workbooks
• T5: Define data-sources at the DWH level to tidy up stuff. You can add functionality, multiple data-sources to report on (Excel & Oracle) and afterwards level these down into your DWH to have 1 single source of thruth.
• T6: Use lgo files on BI Server to have a look at the actual queries being fired. You can define different kinds of log levels depending on your needs at a specific point in time.
• T7: Time-Series Calculations can be used for reporting on historical data but within OBI ther's limited functionality. If you need this functionality it's better to use EssBase.
• WO: Errors and Compilation information isn't well documented, you have a reference guide but no getting started guide. You can have a look at Mark Rittman's blog and our iAdvise website if you need course material or information regarding walk-throughs, getting started guides.

2. Use EssBase to speed-up your OBIEE Reporting:

• T1: Using cubes can speed up data load and querying. Use OBIEE for front end purposes
• T2: In the next release you can load EssBase Cubes inside your model (in one of the futher releases ;o) )
• T3: Use 'Analysis Services' for the same purposes as EssBase, it's cheaper ;o)
• T4: OBIEE uses the aliases defined in EssBase on the dimensional attributes
• T5: Real Benefit: uses all dimensional metadata defined in EssBase, Analysis Services, ... In other words you can have a hybrid OLAP solution integrated in OBIEE
• T6: JDeveloper allows you to access EssBase and get data from different sources

3. Presentation Server:

• T1: You can define conditional formatting on columns, e.g. use images available in 'Oracle Answers' (Click on the 'image button)
• T2: Filter on data using 'Dashboard Prompt' defined in the Catalog-tab in 'Oracle Answers'
• T3: You can define a Dashboard Prompt on Report Level or Page Level
• T4: Use hidden dashboards for guided information using 'Guided Navigation Link'
• T5: Use Word Add-In to define BI Publisher Reports, best use an older version than Word 2007
• T6: Use existing 'Oracle Answer Reports' or Discovere query and base the report on this data, using Word Add-In
• T7: 'Presentation Variable' in Dashboards needs to match the parameter name being used in BI Publisher to parameterize and filter on report data

These are all the tips & tricks Mark has shared with use, using his experience and knowledge regarding Business Intelligence. It was a great session and hopefully you will have the ability to attend a session of Mark in the future as well.