Replace Unique Key by Autonumber

[firebohl]

Hello,

I am not sure if I understand the use of the Autonumber Function correct.

At the Moment I got a Big Data Model with several Tables, using a Unique Key field which is a mix of sveral Informations (e.g. Company-Code-DocumentType-DocumentNumber). I used the Document Analyzer which says replacing this Field would save around 50MB Data. The Field is just used for linking the tables and not in use for anything else.

At the Moment I got something like

Table 1

KeyField (I like to replace), Document Number, Company, Document Date

Table 2

KeyField (Link to Table 1), Delivery Adress, Country

Table 3

KeyField (Link to Table 1), Sales Agent, %Commission

Do I understand right that when I add after Final Load an "AutoNumber(KeyField) as AutoKey" the KeyField will be automatically replaced in all 3 Tables and the Link between Data will stay the same? I am afraid to loose Data.

[chrismarlow]

Hi,

I am not sure about automatically replaced, this is more like on each table that uses this key there needs to be a load that replaces the long text key with the numeric, you also need to make sure you get rid of the long text key, or you won't see the gain. Pattern like;

fact:
Load
	AutoNumber(BadKey) AS GoodKey,
	*;
Load * inline [
BadKey, Mea1
XXXX, 1
];

dim1:
Load
	AutoNumber(BadKey) AS GoodKey,
	*;
Load * inline [
BadKey, Dim1
XXXX, AAA
];

DROP Field BadKey;

You shouldn't lose data here.

The one thing like this I have seen data lost is where there was a need to get different values for the long text key from different sources and and an assumption was made that one of the sources contained all of the values, hence the dimension table was only built off that, meaning a load of orphaned facts, fairly easily fixed by adding on the dropped values to the dimension table in the load.

Cheers,

Chris.

[firebohl]

Hi,

but will on a load with Autonumber the result be the same on all tables? For example before I got this Table.

Table1:

LOAD * INLINE [
BadKey, F1, F2
BadKey1, 100, A
BadKey2, 101, B
BadKey3, 102, C
];

Table2:
LOAD * INLINE [
BadKey, F1, F2
BadKey1, AB, 1
BadKey3, CD, 2
];

When I Load both Tables with Autonumber in BadKey will be the second entry have the same Autonumber than in Table1?

[firebohl]

Ignore my last Answer. Now I understand the Handling. With this little Example I was able to reproduce and get the result I like to have. Thank you!

[Brinkman11]

Thanks for the update and quick reply. I'll be sure to keep an eye on this thread. Looking for the same issue. Bumped into your thread. Thanks for creating it. Looking forward for solution.