Global selections and data modelling best practices

[#1]

Hey,

I asked a question relating to this topic earlier on and got an answer that solved the problem at hand...
But since I´m relatively new to Qlikview and not totally familiar with all of the best practices I wanted to open up a discussion on the matter, I´m hoping to share and gain some ideas and find the best (most efficient, memory and calculaion -wise) models for the case.

So let´s say that the imagined case would be the following:

Generally distributed data:
you have a calendar, set of customers, items, company dimension 1 company dimension 2 and company dimension 3.

3 KPI´s of different types let´s say sales, purchacing and logistics located on 3 different sheets in qlikview.

All of the general data can be used as filters and if you make a selection from one data, lets say one item, that selection would transfer to all of the 3 KPI´s and they would then show their own respectful data of those selections.

Now, how to go about in making the previous happen?

Option 1:
The first way would just to let qlikview deal with the multiple relations by creating sync keys and circular references... Not good and not actually an option.

Option 2:
Concatenate a key field containing all of the generally distributed data and thus making a star like relation data model, which is good, but...

The problem with this might be that you end up with a veeery long key and if a very big string if operation is needed everytime you do some calculations. Well, that sounds a bit wasteful to me.

And you would first need to join all the general data into a one huge table that would be able to concatenate the desired key with all the desired values.

So the floor is open for wild and/or mild ideas =).

[#2]

...and of course you can use autonumbering for the key to get rid of the long concatenating line...

[#3]

You can use the AutoNumberHash128 function to generate you an unique key...

Go to read the EXCELLENT Rob's writing about that : http://community.qlik.com/blogs/qlikviewblogs/archive/2008/05/29/memory-sizes-for-data-types.aspx

[#4]

Yep, like I posted later on, I´m using the autonumbering for the key but to create the key, every table has to have the same data right? So i can´t give the customer and the item table the same key.
One solution is joining all the data of customers and items into a one table and then concatenating the key from there... But how efficient will this be?

Is there a more efficient way, besides I tried the joining once and id didn´t actually work out as i´d wanted it to...

[#5]

What are the key between customer and item tables ? An Item_Id ?

So if this Item_Id is a field, there's no SynKey created... right ?

If this Item_Id is composed by multiple Fields, create a Unique key using AutoNumberHash's function in both tables.

[#6]

Ok, poorly illustrated above i´ll try to be more specific, but you´ve just gotten to the question.
I should maybe draw a picture, but i´m feeling too lazy right now so i´ll just put it in ascii format here...

AllCustomers: Allitems:
ID ID
Dimension Dimension
CustomerName ItemName
/ | \ / | \


KPI purchasing KPI Sales KPI logistics

ID ID ID
Dimension Dimension Dimension
PurchCust SalesCust LogistCust
PurchItem SalesItem LogisticsItem

Ok, now if i generate a key that has the id, dimension and the customer name, i cannot connect it to items right?
And if i create a key for items holding the respective fields, that will probably cause a circullar loop.

Now one possibility comes to mind, if the customer and item tables could be joined so that there would be id,dimension,custname and itemname all in the same table and then generate the key.

The point in this mind game is to create filters(n ammount, depending on how many different kinds of data we want to be "globally" accessible) that would show all the customers and all the items and not having to create their respective filters.

[johnw]

I'm lost on the "Dimension" field. I gather that you might be saying "Dimension" to actually stand for one or more dimensions, but as written, it appears that all tables specify the same field value in a very denormalized and seriously circular-reference way.

How about this - can you post some example data that would be in all of these tables? Then maybe I can figure out how I'd lay out the data instead of how you've done it.

[#8]

Hey,

Sorry for not following up, but I´ve been a bit busy. The dimension field was supposed to be just any kind of field on an abstract level. So don´t pay too much attention to it.

I´ve already solved this problem in a way of concatenating everything into an autonumbered key... and I guess it´s pretty much the only way to actually get the desired selections to traverse where I want them to.

I was just hoping that there would be another way of implementing this action. Also seems that I wasn´t too good in explaining what I wanted =). But thanks to all of you guys who answered me, I think Í´ll drop the subject for a while and stick with my concatenated key.