autonumberhash128 Syn Key issue

[#1]

Hi Guys

I have 3 Synthetic keys on my script load , I have read many posts about how to remove them and whether it actually is important to do so.

I have used some concatenate loads to remove some of them , but I still have these 3 remaining. They are based on fields that I have renamed.

Please see sample of script above,as the Autohash is based on the renamed fields I'm getting an error on the load, can anyone advise a way around this ?

Thanks

A

[Anonymous]

Hi A,

First I would start with removing the Month, Week and Year field-lines from your script, you only need the field Date at first, and add a calender later.

This will make it easier for you to work with.

After that you have to think over what you need. And you have to deside what your key-fields will be, how do you want to join your information.

[ToniKautto]

As said above the first thing for you to do is to remove the month/week/year from each table and instead go for a master calendar table to which to link based on the date. Below you find an example of a how a calendar table can be generated.

It seems that you are currently loading data based on there structure in your sources. In most cases you probabaly need to convert the loaded data to fit a model suitable for what your are trying to accomplish in QV. Hence you need to identify what values to put in your fact table and what to put in attribute tables to get a good star or snowflake schema.

The problem with synthetic keys is that you easily end up in situations where you can predict your datat relations. For example in your current setup with the synthetic keys, if you execute Count(Assistant Manager) what value will you get? QV is unable to determine from which aspect to count the number, is it from Master Table, Mydata or Staffhours? The thing with the syn table is that it will contain all possible combinations of the syn keys form the linked tables, giving you much larger datat sets than you anticipate when runing calculations including fields from tables related by a syn key.

Hope this made any sense, and that you see a bit of the potential problem with having syn keys. It might work well, but most likely will not in some aspect of your app.

---

LET vCalendarStart = Date#('2000-01-01');
LET vCalendarEnd = Date#('2015-12-31');

LET vCalendarLength = Num(Date#(vCalendarEnd)) - Num(Date#(vCalendarStart)) + 1;

MasterCalendar:
LOAD
*,
Year(Date)   as Year,
Num(Month(Date)) as Month,
Num(Day(Date))  as Day,
Week(Date)   as Week
;
LOAD
Date($(#vCalendarStart) + RecNo()-1) as Date
AutoGenerate $(#vCalendarLength);