Adding a time dimension in an existing application

[#1]

Good day,

I'm facing a problem I know how to solve, but I would like to know if there is an easier solution.

Let's say that I have developped a big application, which display the data loaded (obvious).

Here is my data model :

There is no time dimension in this model.

Now, the big boss says "ok, your application displays charts using the last data we have... but I would like to see what this would like with older data ? Load the data from various snapshot, and add a selection list with the date the user wants to see".

I need to add a time dimension in my 3 tables, but I cannot give them the same field name :

Even if I use a good MasterTable model, my charts would give wrong result (should not be linked with this time dimension).

My solution is to use a distinct date field in each table :

and create a data island for a dimension "DateIsland".

But I am forced to modify every expression of every charts in my application to add a set analysis

{$ <Project.Date=P(DateIsland)>}

or a condition

if (Project.Date = DateIsland, ...)

This would take days since my application contains hundereds of expressions !

Is there another solution ?

[vidyut]

Hmm, You must have already explored the use of Expressions Overview dialog to quickly do the find replace? Will that not work?

[brenner_martina]

Hi,

you can also concat the to fields to create a new key:

ProjectID & '-' & Date As DateKey

let the fields ProjectID and Date only in one of your table and comment it in the 2 others

[johnw]

nmartin wrote:Now, the big boss says "ok, your application displays charts using the last data we have... but I would like to see what this would like with older data ? Load the data from various snapshot, and add a selection list with the date the user wants to see".
I need to add a time dimension in my 3 tables, but I cannot give them the same field name :

Even if I use a good MasterTable model, my charts would give wrong result (should not be linked with this time dimension).

I'm a little confused. Why shouldn't your tables be linked by date?

It sounded like you wanted to be able to see what your data looked like on the selected date. If you add the same date field to all three tables, and then select a specific date, all three tables will be limited to the data as it was on that date. That sounds like what the big boss was asking for?

[#5]

Vidyut Verma wrote:
Hmm, You must have already explored the use of Expressions Overview dialog to quickly do the find replace? Will that not work?

It's not as simple as it looks like :
- sometime I have to use "Table1.Date", sometime "Table2.Date", ...
- sometime I already have a set analysis and have to add a condition

Martina wrote:
you can also concat the to fields to create a new key:

ProjectID & '-' & Date As DateKey

let the fields ProjectID and Date only in one of your table and comment it in the 2 others

And how can I make the difference between the data in "Table1" from a specific date ?
My data are not always linked to the project table (and shouldn't, even by a date field).

John Witherspoon wrote:
It sounded like you wanted to be able to see what your data looked like on the selected date

Exactly.
As if I had several ".qvw" files, each corresponding to a specific day.
Instead of open a specific file regarding the date of data I want to see, I would like to have an all in one document with a list of selection for the desired date.

[johnw]

nmartin wrote:Exactly.
As if I had several ".qvw" files, each corresponding to a specific day.
Instead of open a specific file regarding the date of data I want to see, I would like to have an all in one document with a list of selection for the desired date.

Then what is wrong with your solution of adding the same date to all three tables? Is it the synthetic key you're concerned with? It's not a problem, and even if you don't believe that, you could replace it with a concatenated key performing the same function.

See attached. It's the data model you showed. You can select a date, and it shows you the data from all three tables as of that date. I've made an assumption that the Table1 value is part of the "key" to Table1, and the same for Table2. Otherwise, you might as well just join everything into one big table.

Is this not what you want?

nmartin wrote:My data are not always linked to the project table (and shouldn't, even by a date field).

Does the problem have something to do with this statement? Because the original data model you're modifying already linked on project. Are you saying your original data model was also wrong, and we need to change it in addition to adding a date? We need to split all three tables apart?

[Oleg_Troyansky]

It's really hard to prescribe a solution without knowing the specifics... For example, if one table has time records, then the Date might represent reporting time entries, while the other table might have tasks, and the date might represent Start Dates or End Dates for different tasks. In this case, you certainly won't want to link all the tables by Dates...

I'd consider concatenating all the transactional ("fact") tables into a single table, instead of linking between them. It might be the best solution for many business situations...

[#8]

It's really hard to prescribe a solution without knowing the specifics...

In a word : what is the best practice to manage data history in QlikView ?

I don't need the date to be a dimension to use in my charts, just date to select "data on date".

I'd consider concatenating all the transactional ("fact") tables into a single table, instead of linking between them. It might be the best solution for many business situations...

Really ?
Here, you would make a single table with
Project ID, Date, Table1 value, Table1 attribute 1, Table1 attribute 2, Table2 value, Table2 attribute 1, Table2 attribute 2
?

[Oleg_Troyansky]

nmartin wrote:
In a word : what is the best practice to manage data history in QlikView ?

This could make a good subject for a book, or at least a large wiki page... In a nutshell, we are typically trying to bring all different dates into the same field name, whenever possible, and trying to avoid date islands, because they are usually way too heavy and bring performance down.

nmartin wrote:Really ?
Here, you would make a single table with
Project ID, Date, Table1 value, Table1 attribute 1, Table1 attribute 2, Table2 value, Table2 attribute 1, Table2 attribute 2
?

Yes, what's a problem? Again, hard to prescribe without knowing the specifics, but generally speaking there is nothing wrong with doing it. See, unlike with RDB, we are not too concerned about having many nulls - they are harmless in QlikView, and denormalization typically works well.

No matter how you spin it, you seem to have multiple Fact Tables with multiple common fields. Your choices here are counted:

1. Live with a bunch of synthetic keys, which might not be too bad, even though I personally dislike them.

2. Create a Link Table, which might be or might not be the right approach for your specific business problem

3. Concatenate all Fact tables into one, which, again, might be or might not be right.

4. Use a Date Island and suffer from sluggish performance

There aren't too many other choices, as far as I know... Pick your poison 🙂