Clever Geek Handbook

Iccube: how to size a hierarchy with staging table

I'm a newbie and can't seem to find how to determine the dimension for a structure like this:

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

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

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

How do I determine the size of the company and link it to the invoices_data table?

+3


source to share


1 answer


If possible, I would change the structure of the table. Note that it is possible to create an iccube table from a SQL statement.

I would start by creating a few dimensions:

  • Company → using "companies"; Multilevel levels only one level with id & signature.
  • Date → Use time (master); add start / end matching data you have.
  • Product -> using "invoices_data": tiered levels just one level with id_product for key and name

If possible I will add the date to 'invoice_data', SQL connection. If not, you can create a Many-2-Many (extended) table that will do this for you. More details here , I would not go for m2m if not needed as these are advanced features (read the link carefully).



Create a cube, add "Facts" with tabular "account data". Click on the last wizard button, this will automatically link all measurements. Finally, add your measures (quantity and price).

And it should be.

hope this helps

+2


source






More articles:


All Articles