data model design - facts / dimension - best practices

[#1]

hello qlik-community,

i want to set up a very simple star-scheme with qlik sense.

given the following query, how would you setup the script ?

btw. is there any chance to give the user an possibility to enter a date, which will be used by the query (USER_INPUT) ?

thanks in advance!

SELECT
SUM(
   CASE WHEN dim.type in ('a','b','c') and dim.state in ('0','1') and dim.date <= USER_INPUT
              THEN fact.revenue
    CASE  WHEN dim.type in ('d','e','f') and dim.state in ('0','1') and dim.date <= USER_INPUT
and dim.flag ='N'
              THEN fact.revenue*(-1)
   ELSE 0
 END
),
dim.company_name
FROM DIMENSION dim, FACTS fact
WHERE dim.ID = fact=ID

[ogster1974]

Looks like you are modeling facts revenue about a company

There are lots of ways you could do this and it depends what you are lookng to achieve.

Start by seperating your data into dimensions and facts.

In the fact table you might have

%CompanyKey

Type ='Revenue'

Date

Value

adding a fact type will allow you to grown your model as you bring in more facts about your company.

In the Company table you might have

%CompanyKey

Company Name

Company Type

Company State

State, Type and flag im unsure if these are related to the company or the revenue you might discover other dimensions you need to report on as you investigate further.

For the user manipulation let Qlik create a calendar on your revenue date and the users can fliter to their hearts content on months, quarters, years etc... manipulating the date load is the wrong place for users to get invlovd unless you have a data size/business constraint that means you can only report x number of years of revenue.

If you need to control the user interaction more then your getting into variables and extensions but problably not need yet.

Hope this helps.

Andy

[#3]

Hello Andy,

Thanks for your quick response.

My query reflects an already implemented data model.

One major table holds the facts and is connected via ID to the dimension table.

Some measures need a special treatment, which is done via several CASE-WHEN constructs,

where attributes from the dimension table have to be used.

I really don't want to put the logic into the application - it may confuse user and the maintenance is too difficult.

So, what are your recommended steps to proceed ?

Thanks in advance!

[mayuresh_d]

hi,

best way to achieve is to use input box extension which u can find in community.  use that extension in that declare variable which will have the value we insert use that variable in ur expression.

And for the data model  there is multiple ways to achieve just separate fact table and dimension table. create key between them which will give unique solution use master calendar for trends.

hope this will help

[#5]

hello!

thanks for your hint.

in your solution a user has to define the measure every time by himself, which is very dangerous.

Therefore I thought to put the calculation into the load-script.

But if you know, how to get a kind of semantic-layer, which can be used for several apps, that would be great.

i could not find the extension in the community, can you provide a link ?

there are already separate tables available - and i linked them via ID.

don't know where to start...

[mayuresh_d · Accepted solution]

hi clark,

i have attached the attachment. i it is qlikview app but qliksense app work same way.

use this app for reference. as u mentioned. i done calculation in load script. just after every new input u need to have to reload app. for refreshed data.

hope this will solve ur problem,

and for extension visit  following link

GitHub - erikwett/qsVariable: Variable extension for Qlik Sense

[#7]

Thx mayuresh dabhekar - i think i got the idea.