You are not logged in.

Can't have two "Lookup" fields, which reference one facttable field...

Dear visitor, welcome to Palo Community Forum. If this is your first visit here, please read the Help. It explains in detail how this page works. To use all features of this page, you should consider registering. Please use the registration form, to register here or read more information about the registration process. If you are already registered, please login here.

xeonix

Trainee

  • "xeonix" is male
  • "xeonix" started this thread

Posts: 3

Date of registration: Jul 15th 2010

Location: Belarus, Minsk

  • Send private message

1

Tuesday, July 20th 2010, 6:12pm

Can't have two "Lookup" fields, which reference one facttable field...

Hi All!

I have following flat source tables:
FactTable(Customer,Total)
Customers(Id,Name,Country,City)
Cities(Id,Name)
Countries(Id,Name).

So, the problem is: I have two dimensions Country and City, and i want to see the totals by country or by city through Customer field in FactTable.

I have setup City and Customer data to import by means of Palo ETL, and make the FieldTransform for FactTable to add "Lookup" fields for City and Country.
When i import the whole data, i see that Country's or City's first element contans aggregated Total, although when i have import only single Country or City it works well (I mean i see distributed data by country(city)). I suppose that it isn't possible through one facttable field has two or more "Lookup"s!?
I have solution to add calculated fields for each "Lookup" like:
FactTable(Customer,Total,City,Country).

Can i do mentioned-above problem by means of PALO ETL?

Thank you very much!

Best regards from Xeonix

holger_b

Sage

  • "holger_b" is male

Posts: 271

Date of registration: Jun 1st 2010

Location: Freiburg, Germany

Occupation: BI Consultant

  • Send private message

2

Tuesday, July 20th 2010, 8:57pm

RE: Can't have two "Lookup" fields, which reference one facttable field...

Hello again Xeonix,

your data model would probably consist of two dimensions: One might be named "region" (pretty much like in the Palo demo database) or "market". They are all hierarchy levels in the same dimension: Top level is countries, these consist of cities, and base level is customers.

The other dimension might be called measures, currently there seems to be just one element in it: Totals (what is it? Turnover? Quantities sold?) Later on, you will quite probably want to have more dimensions: Time, version...

So the basis of your "regions" dimension import would be a SELECT statement which joins the Customer, Cities and countries tables. Or you could also do it as a table join in a Palo ETL transform. As a result of this join, you have three columns which you can now use as levels in the dimension setup:

Customer ID (plus Customer Name as attribute), parent element of customer is city, grandparent is country, plus a top level element like "All customers" or "All regions".

Last thing you need is the measures dimension (one element "Totals" will do for the moment). And now you can create a cube from your fact table.

Hope this helps
Holger

This post has been edited 3 times, last edit by "holger_b" (Jul 20th 2010, 9:07pm)

Rate this thread