topic Developments datawarehouse within QlikView in QlikView

Developments datawarehouse within QlikView

jjordaan — Fri, 23 Mar 2012 20:47:43 GMT

Hi all,

I am new to working with QlikView. I'm also inexperienced in working in BI.
Can anyone recommend a book if I want more depth in the development of BI and especially the ideas behind the development of data warehouses (within QlikView) models with the Star Schema and Snowflake schemas and how to develop these.

Thanks for your help.

Developments datawarehouse within QlikView

Jason_Michaelides — Sat, 24 Mar 2012 12:34:26 GMT

Hi,

If you are only looking to use QlikView for your reporting and analysis them you may find that you don't need a traditional data warehouse. If this is the case, start with the QlikView white papers and presentations that are available and go from there. There are loads of discussions on this forum about data modelling etc. have a good read and start to play!

Hope this helps,

Jason

Developments datawarehouse within QlikView

jjordaan — Sat, 24 Mar 2012 13:11:47 GMT

Jason,

Thanks for your comment.
Now I use QlikView indeed mainly for analysis and reporting.
However, I want to get involved more.
Now I have made a QVD generator that extract the data from the source and generated QVD.
Then I make the data model in the dashboard on the QVD and also some ETL operations.
However I wish to improve data model within the dashboards and perhaps the star schema system to work.

Developments datawarehouse within QlikView

Jason_Michaelides — Sat, 24 Mar 2012 17:28:08 GMT

In most cases a star-type schema will be most efficient in QlikView. Post an image of your current data model with some information on the tables (field types and numbers of records etc) and we may make some suggestions.

Re: Developments datawarehouse within QlikView

jjordaan — Mon, 26 Mar 2012 07:42:03 GMT

Hi Jason,

It is a dashboard on Microsoft Navision.
Below the explanation of the tables:
- QV Transactions table > these are the Sales Lines, Sales History Line, via Concatenate.
- QV Headers > Headers are the Sales (orders) and the Sales History Header via a concatenate.
- QV Season Exchange Rate > Here are the currency used for calculation values of other currencies.
- QV Item> These are the articles
- QV Item HC> This is within NAV within a specific module for Size (Medium, Large, etc.)
- QV Item VC> This is within NAV within a particular module used for colors (Black, Brown, etc.)
- QV Item Cat> This it the item categories (item (main) groups), set up

I hope I've given you enough information so.

Developments datawarehouse within QlikView

Jason_Michaelides — Tue, 27 Mar 2012 08:29:28 GMT

Hi Jeroen,

Your data model looks pretty good to me on the face of it. I would make a few further changes to reduce the number of joins:

ItemVC, ItemHC and ItemCat - use ApplyMap() to bring the xxxDesc fields into the main Item table. (The F1 help gives a good description of how to use ApplyMap() if you've never done it before)
ItemCR - is this 1 to 1 on ItemVariantID? If so, consider either using ApplyMap() or a Left Join to bring this table into the Item table as well.
I would Left Join the Transactions table into the Headers table (assuming a standard 1 to many relationship applies from Header to Transactions) - LEFT JOIN (Headers) LOAD * FROM Transasctions...;
What is the field "Document"? Good practice is not to use fields in the joins that you may use in counts etc in the UI. So maybe create a new key here.
How many transactions are you dealing with? If it's a large number then consider adding a customer counter table to avoid using COUNT(DISTINCT CustNo) for your customer counts (see below link)

So, as I say, it already looks pretty good to me - although I obviosuly can't speak for the data itself! To help you further, see this thread for links to some best practice documents - there are some real gems in there.

Good luck!

Jason