CODEWITHSUNDEEP
pentahoworkbench
Konstantina Papagiannopoulou
Konstantina Papagiannopoulou

Reputation: 213

How to add sub dimensions in schema workbench

My data warehouse has snowflake schema and I am trying to create a cube for this schema. But I don't know how to add sub dimension. I am new to this and I am really confused with hierarchies and levels. This is my data warehouse:

ratings-fact_table   ---->   books_dm               ----> authors
    rating_id                  book_id                       author_id
    book_id                    author_id                
    user_id                    publisher_id         ----> publishers
                                                             publisher_id
       |
    users_dm   ----> cities   ---------> countries
       user_id          city_id             country_id
       city_id          country_id

Please help!

Upvotes: 2

Views: 947

Answers (1)

mzy
mzy

Reputation: 1764

I guess your data warehouse is build on the top of some relational database (MySQL, etc.). You can solve this problem by converting the snowflake schema to star schema manually ~ by creating SQL views in your database. Then in your OLAP schema you would use those tables:

  • Fact table (cube): ratings-fact_table
  • Dimension table: books_dm_view
  • Dimension table: users_dm_view

Where:

  • books_dm_view is SQL view: 

    CREATE VIEW books_dm_view AS 
SELECT * FROM books_dm b 
LEFT JOIN authors a ON b.author_id = a.author_id 
LEFT JOIN publishers p ON p.publisher_id = b.publisher_id

  • users_dm_view is SQL view:

    CREATE VIEW users_dm_view AS 
SELECT * FROM users_dm u 
LEFT JOIN cities c ON c.city_id = u.city_id 
LEFT JOIN countries n on n.country_id = c.country_id`

This way your dimensions have no sub-dimensions and you don't need to use extra joins in you OLAP schema.

Upvotes: 4

Related Questions