problem with key table

[aheavy95]

Hi all,

in my project I used a key table in my star schema.

I Created a key for the SalesPerson 

And added it to the keyTable like the rest of the keys.

For some reason the fields that are in the Salesperson dimension which are not included in the key, are not associated to other fields in the model.

For example, I can get the "Orders amount" for a SalespersonID, but not for LastName_SP.

Any ideas why how to fix this?

thank you

[HugoRomeira_PT]

Hello there!

Can you share a printscreen of data model viewer with your start schema?

As well as a printscreen of the fact table script (where you have the order amounts), where we can see how do you create the salesPersonKey on that table?

[aheavy95]

Hi thanks. I calculated the orders amount in the app just by counting salesorderID.

I added the model viewer, key table, salesorders fact table

[HugoRomeira_PT]

Thanks!

From what I can see, the issue is in an inconsistency between the salesPersonKey.

In the fact table you create the key using 3 parameters:
SalesPersonID, AddressID, LastName_SP

While in the Key (Dimension table) you have only 2 fields:

You just need to update this an ensure consistency, either

• by adding the field LastName_SP to the AutoNumberHash256 in the SalesPerson_3 table
• or removing the field LastName_SP from the AutoNumberHash256 in the KeyTable table

You should choose the solution that ensures that the salesPersonkey will be a unique ID for the table SalesPerson_3.

Hope it helps!

[aheavy95]

Hi,

unfortunately this didn't solve the issue. One of the keys had the lastname field because I tried to add it hoping it would help and forgot to remove it.

Now the key consists of the salespersonID and addressId only and it still wont work.

a straight table will looks like this:

Any idea?

thanks a lot

[HugoRomeira_PT]

What may occur is that you may be getting different values for the AddressID and/or SalesPersonID in the key table and SalesPerson_3, which can lead to the creation of different ID.

In order to test this I would do the following:

1. In the SalesPerson_3 table, add the following fields to your script:

SalesPerson_3:

Sales
LOAD
AddressID as Debug_SalesPersonT_AddressID,

SalesPersonID as Debug_SalesPersonT_SalesPersonID,
....

2. In the KeyTable table, add the following fields to your script:

KeyTable:
Load 

AddressID as Debug_KeyTable_AddressID,

SalesPersonID as Debug_KeyTable_SalesPersonID,

...

3. Reload the data and Add all 4 fields into the straigh table you previously send to us, and make a printscreen and share.
We will find the next clue to solve this puzzle! 😄

[edwin]

while you are figuring this out, dont use autonumber also when you are concatenating your keys dont use a space as it could become ambiguous, use something like |, 

this way, when you debug it you will see what is actually being concatenated.  after your proven your code, then you can use autonumber

[aheavy95]

Hi thanks a lot,

Here is the screenshot:

looks like you were right, many records are created for each addressID

[HugoRomeira_PT]

Ok. Thanks for the print.

By looking at your star schema, I see that you have a Location_3 dimension, that links by the AddressID, it seems to me that this will categorize where the orders have been made.

By the previous printscreen(with debug columns) it seems to me, that:
The dimension sales person, in fact should only be made by the SalesPersonID (which seems to be unique in the SalesPerson_3 table). Despite the fact you have AddressID available in the SalesPerson_3, each SalesPerson only has on AddressID, which leads me to conclude that this refers to the SalesPerson main location.
While in your keytable each sales person seems to be allowed to sell to several AddressID: Locations

So in conclusion, I propose you :

Ignore the AddressID, and LastName_SP when creating the salesPersonKey both on the SalesPerson_3 as well as in the KeyTable.

So your salesPersonKey will be:

AutoNumberHash256(SalesPersonID) as salesPersonKey

or simply 

SalesPersonID as salesPersonKey /*SalesPersonID is a integer value so there is no clear advantage on creating a another ID.*/

Hope it helps!

[aheavy95]

thanks a lot. Yes I removed the field addressID from Salesperson, customer since the addressID in the fact table salesOrders is the relevant one .

If I want to keep the customer's address I think I should name it differently.

Also, I figuered that these key mess things up a little bit so I took them out for the customer, salesperson dimentsion.

thanks a lot