Creating Key column in DIM and Loading Fact?

[shyamcharan]

Hi Experts,

I have a few dimension tables which joins to the Fact table on a key column. However, the key column has string values with a length of 38 characters.

To avoid joining the tables on String based key columns, I am trying to create Auto number column in the dimension tables.

However, loading the fact tables is challenging for me.

Do i use ApplyMap() function in fact table to call in the newly created auto number column values from the dimensions?

Any help here is highly appreciated.

Thanks in advance.

--Shyam

[senpradip007]

Can you upload some sample data?

[ziadm]

Have you tried using AutoNumberHash128 creating a key from multiple fields

[harsh44_bhatia]

hello,

Could you elaborate what difficulties you are facing while loading the fact tables.

Just to help you out.

when you try to use multiple autonumber() generated keys, give index as well.

for eg:

Employeedim:

load

autonumber(employeeID,'employeeIndex') as employeeKey,

.

.

from employee.qvd;

Customerdim:

load

autonumber(CustomerID,'customerIndex') as customerKey,

.

.

from customer.qvd;

fact:

load

autonumber(employeeID,'employeeIndex') as employeeKey,

autonumber(CustomerID,'customerIndex') as customerKey,

.

.

.

from fact.qvd;

[jonathandienst]

I suggest that you upload a sample qvw or at least your script. I would not use ApplyMap(), you can just use AutoNumber on the key fields for both the dimension and the fact tables. Note that Autonumber() resets for each reload, so the autonumber for the fact and dimension must be generated in the same reload.

[avinashelite]

Try to use the Hashkey or Auto haskey....that should be better ...

[shyamcharan]

Thank you all.

Please find the attached qvw sample document with 10 records in it.

I have used one DIM_TABLE and FACT_TABLE in the document.

Each of these are joined on RVA_PK column.

However, they are string column. I am trying to join them using a Numeric key column rather the string to improve the performance.

I am trying to create a numeric column in dimension table. Once this is done I would like to load the fact table and join them on new key column.

Any further help on this is highly appreciated.

Thanks in advance.

Shyam.

[shyamcharan]

Hi All,

I have used the Autonumber(Field) script function in Dimension and in Fact Table and used these new Autonumber(Field) as Key to join both these tables.

Note:Please check the attached sample QVW doc I have uploaded

I understood if both the tables are in the same load then unique values are generated in these both tables. Moreover, the generated values will be same in both the tables if the Field used to generate Autonumber has same values.

For example:

Table1                                                 

Col1     Col2     Autonumber(Col1)

abc     1234     1

XYZ     976     2

lkj          897     3

Table2                                              

ColX     ColY     Autonumber(Col1)

abc     1234          1

XYZ     976          2

poi     234            4

Now I can use the new Autonumber(Col) to join both these tables.

Please correct me if I am wrong.

Please check the attached sample QVW doc I have uploaded

[shyamcharan]

Hi All,

I have created a new document as an example. Here I have used three tables and used Autonumber() function on all of them.

However, I am not getting the wrong results.

As shown in the attached example, why I am not able to see values under C1 or C2 when I select C5=Northern Place?

Please view attached example.

I appreciate any help on this. Thanks in advance.

Regards,

Shyam.

[rwunderlich · Accepted solution]

You have autonumbered O4 in T3. You must also autonumber O4 in T2 to create matching values

-Rob