Reg. Schema design

building semantic layer
1

Hi BO Gurus,

I am trying to model a schema that will fit to the business requirements.

In our database (sql server 2000), there is one table (ex: Products)
& in this table there are 6 columns, say prod_category1 to prod_category6. All these columns contain different product categories (Finshed, Semi-FInished etc.).

But in the universe, I want to model this as single coulmn.
Can anyone please tell me how to do this.

Thanks in advance
Madhuri

madhum1078 (BOB member since 2007-07-20)

2

a) First thing, That should be done on ETL side… you have to have a column populated with all of the categories you are asking for… in one your tables…!!

or

b) you can create a derived table using UNION clause…to get all the values for category columns into one derived table column … but performance will degrade…

BO_Chief :us: (BOB member since 2004-06-06)

3

Hi,

Thank you and I have one more question.
We have 5 tables (ex:- Products, Location, Purchase, Inventory & Sales)database is sql server 2000 and I need to design the new universe.

I also need to bring Customer and forecast tables which are in the existing universe by linking the new universe to it.

Instead of linking the new universe to existing universe for getting the customer and forecast information, I would like to have Derived tables for customer & forecast.
Can anyone please let me know is it feasible to do in this way…

Thanks
Madhuri

madhum1078 (BOB member since 2007-07-20)

4

Why do you want to create another universe for those 5 tables, instead try to include them in existing universe having customer and forecast tables and make joins if possible.

.

haider :es: (BOB member since 2005-07-18)

5

Hi,

Thank you and those 2 tables customer & forecast are in different universe & database, it tells about forecast.

In the new universe, its on different database. client wants it to be separately as it tells about purchasing.

Can you please let me know instead of linking the new universe to the existing one is it advisible to have derived tables. And what are the advantages and disadvantages of having derived tables.

Regards
Madhuri

madhum1078 (BOB member since 2007-07-20)

6

Since they are pointing to 2 different databases, then keep both universes separate.
While designing reports in DeskI you can create multiple dataproviders on these 2 universes and link them using common dimensions.

.

haider :es: (BOB member since 2005-07-18)

7

Hi

You can use DB Link option to get your tables from another schema. this will save your time to design a new universe.

Regards

Siva.M

looksmee :uk: (BOB member since 2006-02-08)

8

Hi Haider,

Thank you and for designing reports we are using Webi. Could you please let me know is it possible to create reports from multiple data providers in webi 6.1.

Regards
Madhuri

madhum1078 (BOB member since 2007-07-20)

9

Hi
No…its not supported. this feature is available in XI R2. and it should be universe based.

Regards

Siva.M

looksmee :uk: (BOB member since 2006-02-08)

10

And read this post

.

haider :es: (BOB member since 2005-07-18)