Data Model - Loop

[#1]

Hello,

We are into publishing business. We post content to the web and users visit our website to view the content.

1) All the content we post is available in content table. The primary key Id is SKUID.

2) Each content is then tagged to an analyst or a market or a retailer. The tagging information is available in SKUTag table.

3) When a user visits the web page when the content is posted, We track the page visit and database in the usagetracker table.

4) To see what content the user viewed, We parse the URL to get the querystring and get the SKUID. We have that information in usagetracker table as SKUID_Viewed. This SKUID_Viewed is same as the SKUID in Content table.

We need to build the following reports,

1) How many pages the user visited this week/month.

2) How many content items are posted this week/month.

3) How many content items not viewed by users.

4) Count of Content produced by various analysts.

5) Top analyst who got max page visits for the article he produced.

Few of the above report are pretty easy to do.

The qlikview interface needs to have a year, quarter, month as parameters so the user will be able to see above reports at various time frames.

The issue we are facing here is that when we rename SKUID_Viewed to SKUID in usage tracker table we end up with a loop.

Just trying to figure out our last step in order to finish this data model with out any syn keys or loops.

Any suggestions appreciated.

Thank you.

[jaimeaguilar]

Hi,

without seeing your data, I guess there's no need of renaming SKUID_Viewed in usage tracker table to SKUID, because they actually represent different things:

• SKUID in CONTENT and TAGS tables represent dimension values (in this case all the SKU's that may exist in your analysis
• On the other hand, SKUID_Viewed is a fact value / Transactional value, because it represents how many SKUID have been viewed (with all the possible combinations like Date of transaction / view, company, etc), taking as reference all the possible values in SKUID. In theory all the  SKUID_Viewed should exist in SKUID, otherwise it would mean that in your transactional table you have a SKUID that for example doesn't have a description or that has not been registered in you dimension.

regards

[#3]

I think SKUID_Viewed  in usagetracker table and SKUId in content table are related. It's like two fact tables.

Its like what content is posted and what content is viewed by users.

If i do not change the name, how would i answer these questions?

3) How many content items not viewed by users.

4) Count of Content produced by various analysts.

5) Top analyst who got max page visits for the article he produced.

[#4]

I think qlikview works with the association model right?

[jaimeaguilar]

Ok, with that extra information you provided, your model may be built with a different approach. In QlikView you have basically 2 ways of dealing with multiple fact tables. You can either concatenate tables that represent different facts or join them (or maybe use mapping function) if they're are directly related.

The other approach is to use link tables, that is to have and intermediate table that links all your fact tables with your dimensions (technically speaking is like having a synthetic key).

In general Link tables may be easier to understand, however with big volumes of data, you'll see that concatenated Fact tables are faster that link tables.

You may find several posts here regarding this topic,

regards

[#6]

Thanks for the reply. I did read various examples, but not able to successfully come up with a solution.

Looking for some help with some ideas with the data so i can take it over from there.

[jaimeaguilar]

Are you sure that the model shown in your picture doesn't give you correct results?

[#8]

I tried, it did, but with syn keys i was able to be successful. I am trying to avoid syn keys and loops for a better data model. 

The problem i am having is that i have multiple fact tables with common fields.

One is with date which i resolved with a link table, now how do i do it with second key which is SKUID.

[jaimeaguilar]

What I mean is that for example VIEWDATE and POSTDATE are directly related because they're in the same table. Also these fields are the link between USAGE TRACKER AND CONTENT, so within the front-end of qlikview you will be able to make some graphs or any object combining data from any of these fact tables. So I think your model could work as it is shown in the picture of your initial post (there's no need to renaming fields)

Again. I'm just imaging without seeing your actual data. If you could upload a sample it could be easier to help you

[#10]

Thank you so much for the reply. I will upload some dummy data to get you a sample .. Thank you.