Database Partitioning in BO

[Former Member]

Hi Gurus,

Just need some help on partitioned database in BO? like Range partition or list partition.

How can we use the tables in BO and make sure only the desired partitioned is queried , as the DW has millions of records and we have partition may be on Month , a range one. How can we do this on BO.

If anyone has done this before , it will be helpful if you can share your experience and implementation style.

Thanks,

Avinash

[Former Member]

Hi Avinash,

Usually a partition is followed by a "database view" that queries the desired slice identified by the partition.

for e.g. a typical MTD,YTD partition will have 2 database views that will be pulled in Universe as  Tabale-MTD and Table-YTD(pseudo names).

The fact tables, period tables and other dimension tables are joined with these MTD or YTD tables and identified together as MTD and YTD context.

-Prathamesh

[Former Member]

Hi Prathmesh,

Thats what I thought, but the table range goes enormous and it will be difficult to use views for all, and if I use Joins for all the partitiones tables then the joins will be complex .

I am looking for a more generic approach for the entire DW on BO, if anyone has done it before then it will be good to know how it was designed an overview.

Thanks,

Avinash

[Former Member]

I think, thats exactly the advantage of a 'view' since you can select the required columns from any number of tables (doesn't matter how enormous the range is).

This is how its being handled in the current system that I'm working and its been implemented by some of the experienced DW / data modellers. It is rendering good BO report performance and has helped in applying security logic as well.

for e.g. there will be a 'user' group which will always see 'only' MTD cube/view while the other user-base will see 'only' YTD, etc.

-Prathamesh