Data Modelling Question

[#1]

Hi All,

My source data model has many tables with Order and a Suffix columns in common to join to my main Sales Order table(which has measures hence acts as fact).

What is the best method to implement these joins in qlikview?

At present I am using external joins in SQL to bring everything in single select statement. Is it a best approach to do?

Few tables are really huge so as per some readings in few documents I took this approach but I am still not convinced with what I am doing. Your inputs will be highly appreciated.

Thanks,

MC

[#2]

Hi Mrcool4u,

Where you should do the code transformations for the tables depends of your environment and business requirements. You can do all in sql query before load in qlikview, and qlikview take all data prepared, but You can also bring the tables as these are in sql, and in qlikview you make the transformations.

I generally do the second alternative for improve the performance of the transactional client system and then do all in qlikview and qvd files.

In qlikview you can use the same join function as sql, inner join, left join, right join, outer join, join (natural). So you can do it the same in the load statement. In your case I dont know the data structure but i think that you should resolve it with a left join to the main fact table.

Let me know if I can help you with something more.

Regards

[#3]

Thank you so much Guzman..

I found below lines in one of the qlikview document:

"A join can be defined inside a SELECT statement, which in the script execution is sent as a string

to the connector, usually a relational database management system (RDBMS) using the ODBC or

OLE DB connection. Then QlikView waits for an answer. In other words: the SQL join is performed

on the DB system. This is sometimes more efficient and more robust than if you let QlikView

manage it. This is especially true if you have large tables; then you should make the join inside the

SELECT statement."

At present my source is a replication of live OLTP database but would change in future to point to Live directly , so I would like to design my model keeping that in mind.

As you suggested I will try with qlikview joins in load script than having in SQL.

Thanks,

Sudheer

[#4]

Ok, remember when you do this, that the join in qlikview is automatically through all fields with the same name between tables at the reload moment.

Good luck and tell me if you have more questions.

Regards.

[#5]

Sure Mate! Thank you

[#6]

Hi,

Simple Joins, etc are ok to be done in qlikview. But complex business logics are recommended to be done at the source.

Benefit:

1 The main benefit of doing this is that, say the company decides to implement a new BI tool, the logic is easily available.

2 Qlikview becomes easier and light, with just a simple extract and populate the results in graphs, etc.

Having it in Qlikview has the dangers of the qvw being corrupt and all the logic being lost OR re-implement the whole thing if another BI tool replaces QV.

This is just my opinion.

Regards,

Robinson

[mgavidia]

Just to add my 2 cents...

The best method is for you to decide as you get more familiar with QlikView syntax and ways of doing things. However, for the same reasons Robinson mentioned, I also find it easier to do most my complex joins in SQL. Remember that QlikView is a memory hungry application, performing complex calculations in QlikView will eat up your memory fast and it will take a long time to calculate/reload.

That is my experience.

Best Regards,

M. Gavidia

[#8]

Thank you all..I really appreciate you all for quick response.