6 Replies Latest reply: Feb 16, 2017 4:51 AM by clark meyers RSS

    data model design - facts / dimension - best practices

    clark meyers

      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
      
        • Re: data model design - facts / dimension - best practices
          Andy Weir

          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

            • Re: data model design - facts / dimension - best practices
              clark meyers

              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!

            • Re: data model design - facts / dimension - best practices
              mayuresh dabhekar

              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