Looking for best practices for a star schema in QlikView concatenated table

[#1]

Hello,

I have worked with relational databases for some time now and am new to QlikView and dimensional modeling.  I have started working with QlikView and have had some questions about the best way to work with several tables concatenated together with mixed granularity.

I have tried to put together a simple example that will hopefully make things a little more clear to me.  I have attached a .pdf to show a relational model with basically a Bicycle table and a Bike_Orders table.  There can be many orders for a bike.  I am attempting to make a star schema and concatenate these two tables together.  I would like to be able to answer questions like the following:

1.  How many bikes are there?

2.  How many orders are there?

3.  How many road bikes were ordered?

4.  How many bikes were made in 2016?

5.  How many consumer bikes were sold in 2016?

Can anyone please provide me advice on the best way to model this and let me know if anything I am currently doing is wrong?  I will also list some specific questions below.

The main tables have been concatenated together and the TYPE_DIM table created to translate the ids into values for the user as well as to drill down on type and sub type.

  -Do I need to duplicate the %TYPE_KEY data into each record of the concatenated table for the order information to be able to answer all questions or should it only exist in the first part of the concatenated table (as shown in the diagram)?

Currently, I there are two date dimensions so I can look at the date in the Bicycle table and the date of orders independently.

  -Is this typically the best way to be able to drill down on two independent dates or should only one date dimension table be used?

  -If I show a table  box of bikes and a table box of orders and drill down on order information, will the first table box be updated to show only the bikes pertaining to the orders in the second table box?

Thanks so much for any clarification!

[maxgro]

.pdf is missing

and a .qvw with your model and some rows could be better

[#3]

Sorry I just added the .pdf.

I could put a .qvw together but I am not sure that anyone would be able to see it.  I believe I am just using the personal edition right now.  Let me know if you'd be able to see it.  Thanks!

[maxgro]

A licensed QlikView can open a .qvw saved with a personal edition.

So, many people in the community would be able to see your .qvw.

[maxgro]

I would start with this

SET DateFormat='DD-MMM-YYYY';

SET MonthNames='jan;feb;mar;apr;may;jun;jul;aug;sep;oct;nov;dec';

Bikes:

load * inline [

BIKE_ID, TYPE_ID, SUB_TYPE_ID, SIZE, Date

100, 10, 10, 15, 1-Jan-16

200, 10, 20, 16, 1-Jan-16

300, 20, 10, 15, 1-Jan-15

400, 30, 10, 17, 1-Jan-14

];

Types:

Load * inline [

TYPE_ID, BIKE_TYPE

10 Road

20 Mountain

30 Hybrid

];

Subtypes:

load * inline [

SUB_TYPE_ID, BIKE_SUB_TYPE

10, Consumer

20, Professional

];

Orders:

load * inline [

ORDER_ID, BIKE_ID, SALE_DATE, PRICE

247900, 100, 10-Jun-16, 250

247901, 100, 11-Jun-16, 250

247902, 100, 11-Jun-16, 250

247903, 200, 11-Jun-16, 1500

247898, 400, 1-Jan-15, 500

];

Regarding data model you can read this To Join or not to Join

and also there are a lot of useful info here Get started with developing qlik datamodels

Currently, I there are two date dimensions so I can look at the date in the Bicycle table and the date of orders independently.

  -Is this typically the best way to be able to drill down on two independent dates or should only one date dimension table be used?

Answer: Yes if you want to be able to filter at the same time on different dates. In my model there isn't any calendar; you can add a calendar linked to date field and another one to SALE_DATE field

Why You sometimes should Load a Master Table several times

  -If I show a table  box of bikes and a table box of orders and drill down on order information, will the first table box be updated to show only the bikes pertaining to the orders in the second table box?

Answer: Yes, all the linked (associated) tables in the data model will be filtered when you click. Play with a simple model with some listbox and tablebox and you'll learn how Qlik works

[#6]

Thanks for the start and sorry for the delay.  I have modified it a bit to better replicate the scenario I am facing and will attach it.  I have concatenated the Bikes and Orders tables, added the date dimensions and put the types and subtypes into a dimension table.

It makes sense why QlikView acts the way it does, but I am not sure the best way to achieve the results I am looking for and still maintain a star schema (if possible).  My real scenario deals with a lot more data I just wanted to simplify it so I can better understand what should be done.

If I click on a bike type, I get no order information.  I would want to know for example what Road bikes were ordered.  Also, if I click on a bike that was created in 2016, I get no order information for the same reason.  Should I duplicate more data when I concatenate the tables?

Thanks a lot!

[#7]

Hello,

Just wanted to check and see if anyone has any more input since posting the .qvw file.  Thanks a lot!