ETL Rule of Tumb for loading Dimension and Fact

I would recommend following simple rules for loading Dimension and Fact tables

Dimensions:

1)      No duplicate values in Natural Keys.

2)      Dummy record (-1) has to be present in the table, if the table is truncate and load then inserting the dummy row should be first step.

3)     The Natural key shouldn't be blank except the dummy record.

Facts:

1)      Any fact field that is not mapped from source should always be 0.

2)      Any fact field that is mapped from source should always be NON-0 value.

3)      All mapped fact key values should tie up with dimensions.

4)      If no date value is received from source then it will be resolved to 12/31/4444 or any other date you like

5)      If no values for Measures are received from source then it will be resolved to 0 .

There are many rules to apply, I recommend these are the important ones.

SAP BO Information Design Tool UNX universes

The 4.0 release of SAP BusinessObjects introduced a new universe format called UNX. This new format will eventually replace the existing UNV universe format. UNX universes also add a bunch of new features, perhaps most notably the ability to create multi-source universes. These UNX universes are created using a new tool called the Information Design Tool(IDT)

Universe Consumption in BI 4.0

Web Intelligence

 Web Intelligence tool supports both UNV and UNX universes, you can create report from both formats

Crystal Reports for Enterprise

Crystal Reports for Enterprise is the new Java-based rewrite of the Crystal client. Because of this, CR4E can only consume the newer UNX universe format and is completely incompatible with the older UNV universes.

Dashboards (Xcelsius)

The Dashboards tool supports both the UNV and UNX formats, but with a caveat. UNX universes must be consumed through the new integrated Query Browser. However, UNV universes can only be consumed via the traditional Query as a Web Service tool. This limits you from utilizing some new features of Dashboards, such as exporting to a mobile-compatible HTML5 dashboard.

Explorer

The Explorer tool can only access UNX universes in BI 4.0

Live Office

 Live Office only supports the legacy UNV universe format.