Master data through flat file error

[Former Member]

Hello experts, i am trying to load master data using excel file into BPC dimension. The file has 358542 rows of data and in 3 columns. I am unable to load because of the following error.

Error

======

/CPMB/MODIFY completed in 0 seconds

/CPMB/MASTER_CONVERT completed in 101 seconds

/CPMB/MASTER_LOAD completed in 1764 seconds

/CPMB/CLEAR completed in 0 seconds

[Selection]

--------------------------------------------------------------

FILE = \ROOT\WEBFOLDERS\SIL\SALES_PLANNING_SIL_2\DATAMANAGER\DATAFILES\MASTER DATA\ZCENSUS_FLATFILE_CITY.CSV

TRANSFORMATION = \ROOT\WEBFOLDERS\SIL\SALES_PLANNING_SIL_2\DATAMANAGER\TRANSFORMATIONFILES\EXAMPLES\TEST\FINAL\MASTER DATA\ZCITY_CENSUS.xls

DIMNAME = CUSTOMER_SALES_2

WRITEMODE = 2

[Message]

--------------------------------------------------------------

No 1 Round:

Reject count: 0

Record count: 358542

Skip count: 0

Accept count: 358542

Member 12MO-KAILAR-KHEDATOR KHERA JAGI does not exist

Member 12RI-JAWAD-BHAGWANPURA GUDANATH does not exist

Member 12WE-GOGAON-KHEDIKHURD NAWALPUR does not exist

Member 13SA-PATN-JAMDADWADI CHAUGULEW does not exist

Member 13SN-WALW-PHALKEWADI CHANDACHI does not exist

Member 18SU-RAURPS-I.D.L.FACTORY OTHE does not exist

Member 22TJ-THIYAR-NADUKKAVERY_EAST W does not exist

Member 22TP-UDUMAL-KUDIRAIAR KUKKAL R does not exist

Member 25DJ-DARJEE-LEBONG MINERAL SPR does not exist

Member 33JA-PATHAL-KUDEKELA DHARJIYA B does not exist

Submit count: 0

model: SALES_PLANNING_SIL_2. Package status: ERROR

              i have tried to load in two different dimensions for test, one test run keep on running for 1:45 hours with no result. the other test run showed the above error. The flat file consists of 3 columns.

District    sales area      city

12MO      KAILAR          KHEDATOR KHERA JAGI

            I am trying to concatenate the three columns into ID because of duplicate data in different area and city combinations. specifically I do not have these records in the flat file itself. But still  member does not exist error is pretty confusing.

Transformation file sample

===================

*OPTIONS

FORMAT = DELIMITED

HEADER = NO

DELIMITER = ,

AMOUNTDECIMALPOINT = .

SKIP =

SKIPIF =

VALIDATERECORDS=YES

CREDITPOSITIVE=YES

MAXREJECTCOUNT=-1

ROUNDAMOUNT=

*MAPPING

ID=*COL(1)+*STR(-)+*COL(2)+*STR(-)+*COL(3)

EVDESCRIPTION=*COL(3)

SALES_GROUP=*COL(1,3:4)

CITY = *NEWCOL(X)

*CONVERSION

Guide me and thank you in advance

Praveen

[Former Member · Accepted solution]

Convinced the client to plan at one level up which decreased the master data to 4000 odd members.

Thank you all for your help.

Regards

Praveen

[Former Member]

Hi Praveen,

I think you are using the wrong package, can you please take a screen shot of the package you are running to load the master data?

ps, it is not recommended to use -, please use underscore _

Andy

[Former Member]

Hello Andy,

I am sure i am unsing "/CPMB/IMPORT_MASTER"  for flat file master data loading. I have solved the issue by maintaining unique number based ID to each and every city. I now planned to make 6 conversion files for transaction data loading like population and house holds.

   conversion file 1 itself is not getting validated and getting struck with out end result. Is it a good idea or not. Guide me if anything i can do with this huge data.

Regards

Praveen

[former_member466312]

Hello Praveen,

As I Understood, now you have successfully loaded dimension.

Can you refresh dim cache by program UJA_REFRESH_DIM_CACHE and try again?

Thanks,

Deepesh

[former_member186338]

Hi Praveen,

First: I can see spaces in COL(3)

12MO-KAILAR-KHEDATOR KHERA JAGI

Second: Do you really want to work with dimension with 358542 members???

Vadim

[Former Member]

Thank you for the reply vadim,

The requirement is to plan for new cities of business along with existing cities. The client insists for all the cities to be seen as per the selection criteria of "Region" ans "Sales group".

The maximum length of the dimension is "31". It is not taking characters after that. Still i have some duplicates because of length issue, system is taking 31 characters and leaving the remaining.

If some how i shorten the name of the city manually, then i think there might be problems when transaction data load like "Population", etc. will be difficult to match the master data.

Thanking you

Praveen

[former_member186338]

How many cities you have? Have to be a separate dimension...

Vadim

[Former Member]

I have 358542 cities. Do i need to split this city data into two separate dimensions?

Praveen

[former_member186338]

358542 cities? Ups! In what region?

[Former Member]

Hello Vadim,

the region of the city is the first 4 digits of the ID

For example,

12MO-KAILAR-KHEDATOR KHERA JAGI does not exist

     12MO is the region where this particular city belong to

Regards

Praveen

[former_member186338]

I am talking about the huge number of cities 358542! What region of the world we are talking about?

Example: There are 1609 cities and towns in India

Vadim

[Former Member]

Thank you Vadim,

I am loading only cities and towns of country "INDIA".

India has 358542 towns and cities. The cities are divided into "Regions", 01,02,03,etc. Every "Region" has a "District (Sub Region)", 01AD,12MO,etc. Every "District" has "Sales Districts", KAILAR, GOGAON,JAWAD,etc. Every "Sales District" has "Cities" with it.

12MO-KAILAR-KHEDATOR KHERA JAGI

Country - INDIA (IN)

12 - Region 12

12MO - District 12MO

KAILAR - Sales District

KHEDATOR KHERA JAGI  - Name of the City/Town/Village.

     Although the report is at District Level only, We donot require "Sales District" In the Hierarchy.

Planning is not done at this level outside of INDIA as client is having very few customers outside India.

    There are 362 Districts In "INDIA". Every DIstrict has cities with it.

Thank you

Praveen

[former_member186338]

Still not sure that 358542 is the correct number, but even if it's true then working with the dimension with 358542 members can be very slow. May be it's better to define some subset of the used locations?

Vadim