CODEWITHSUNDEEP
olapiccube
Oleksandr Voytsekhovskyy
Oleksandr Voytsekhovskyy

Reputation: 176

iccube: how to define hierarchy dimension with intermediate table

I am newbie, and can't find how to define dimension for such structure:

invoices:
--------
id
id_company
date

invoices_data:
-------------
id
id_invoice
id_product
amount
price

companies:
----------
id
caption

How to define companies dimension and link it to invoices_data table?

Upvotes: 3

Views: 194

Answers (1)

ic3
ic3

Reputation: 7690

If possible I'd change the table structure. Note, it's possible to create an iccube table from an SQL statement.

I'd start by creating a few dimensions :

  • Company -> using 'companies' ; Multi-Levels just one level with id & caption.
  • Date -> Use the Time (Wizard); add a start/end matching the data you've.
  • Product -> using 'invoices_data' : Multi-Levels just one level with id_product for both key & name

If possible I'd add the date to the 'invoice_data', SQL join. If no you can create a Many-2-Many table (advanced) that will do the join for you. More info here, I would not go for m2m unless really needed as it's an advanced features (read the link carefully).

Create a Cube, add 'Facts' with table 'invoice data'. Click on the last button 'Wizard' this will link all dimensions automatically. Eventually add your measures (amount & price).

And that should be it.

hope it helps

Upvotes: 2

Related Questions