Tip - design technique - sequential processing of TXT file

[jan.herout]

Have a nice day,

I would like to know your opinion about the following:

- suppose I need to import pricelist into Analyst
- each price object can have multiple records in source data, each with START DATE and END DATE, price group, country, currency and product
- suppose that the price list is that complex, that to prepare dcube with dimensions such as start sate, end date, price group etc. is virtually impossible (> 2.000 millions cells)

This means that to import the pricelist, what I need is to prepare dcube with two dlists
- dlist 1 : numbers, 1 to let us say 20 milions
- dlist 2 : data from the pricelist

Then I need to go throgh the pricelist, line by line, and map each line to dlist 1.

Is there a way how to do that, without having number of line directly in source data?

Thank you for any tips you might have!

[jan.herout]

Update: maybe use AWK or SED to include number of line into the source file

[sascha]

Hi Jan,

what you intend to do is not the way to deal in Analyst. Can you please explain what exactly you need to do and what kind of information do you need?

I can imagine that you only need the latest price so you should only load this one.

Furthermore you shouldn't load the currency into this cube as the currency is not dependant on your price object. It's only dependant on your coutry, so that you can separate teh relation from currency to country into a separate cube.

Try using SQL statements against your txt-file by setting up an ODBC on the text file. This gives you the possibility to take use of the SQL functionality (which by the way should also give you some row() functionality).

Regards,
Sascha

[chriso]

Jan, what are the quantities
#Products?
#Price Groups in total? max per product?
#What is the time horizon, start and end span over what period?

or perhaps how many price records you have per product?

20M items on a d-list is bit too much ;-) is the pricelist for your planning process 20M records?

[ykud]

Common price list design is to have 3 dimensioned cube with products, {Price_Start_Date; Price} and {1..2k} dimensions.
So it'll look like:
Product = Apples
# Price_Start_Date  Price
1 01.01.2008 100$
2 02.02.2008 110$
etc

You can add row-num in a number of ways I've tried to summ up in this post.