Hi,
I have inherited a big QlikView application that I suspect is doing things in an inefficient manner. Lately its performance has been unacceptably bad, and when I look at the data model it looks wrong. I would appreciate some help from one of the more experienced developers.
There are three tables of note here, which look something like the following after cutting out unnecessary fields:
TRANSACTIONS:
SecurityID
Trans_code
Trans_date
Quantity
RATES:
SecurityID
RDate
RClose
CALENDAR:
RDate
Calc_Year
Calc_Month
Calc_Date
Note: The RATE table contains the end-of-day price for each SecurityID for each day.
There are also a large number of dimension tables around the transactions table which are less important. The idea is to create a time series view of how the holdings of a dimension of customers has changed, for example a stacked bar chart showing how many percent of the total holdings are private/corporate customers FOR EACH MONTH selected in a list box containing all possible Calc_Month. So, there could be 12 bars in the chart, one for each month.
So, the dimension is Calc_Month which the user selects a range of in a list box and the expression calculates the number of remaining shares and multiplies it with the current share price (RClose).
SUM(IF(TRANS_DATE<=CALC_DATE,Quantity,0)*IF(RDATE=Calc_date,RClose,0))
This works, but is extremely slow. As you can see in the data model, the only connection between the TRANSACTIONS and RATES table is the SecurityID. To me, this is a red flag. I don't know how the internals of QlikView calculate an expression like the above, but it would seem to me it has to loop through the RATES table for each transaction since there is no date key binding them together.
I would like to fix this somehow, and preferably with as few changes to the data model as possible since it will take a long time to change all the charts. My thinking coming from an SQL environment would be to introduce a composite key consisting of DATE and SecurityID between the TRANSACTIONS and RATES tables, but that would include a synthetic key which I've understood is bad.
So, my question is: What is the preferred way to connect tables related by a composite primary key (one date, one identifier)?
