7 Replies Latest reply: Oct 22, 2017 8:01 PM by Luis Madriz RSS

    Qlik Sense and Type 2 slowly changing dimensions

    Francesco Leoni

      Hi there,

       

      I'm trying to implement a Qlik sense dashboard reading data from an historical DW with Type SCD. This means that all the tables have a snapshot of the data at any point in time.

      I attached a sample of the data (example data) with claim, claimant and payment information.

      now, since I have different dates to filter, I was wondering if there is a way of linking correctly the tables.

       

      like I need to be able to see the data at 09/09 (see example data) but if I filter for October, I need to see the status and the payments related to October, which for example is the latest value only (PaymentID 1)

       

      ClaimIDClaimStatusEffectiveDate
      1Created09-Sep
      2Created03-Oct
      1Registered09-Sep
      1Assigned10-Sep

       

      ClaimantIDClaimIDClaimantStatusEffectiveDateClaimantName
      11Created09-SepName 1
      21Payments Made05-OctName 1
      32Final Payment06-OctName 2

       

       

      PaymentIDClaimantIDPayment DateAmount
      1110-Sep10
      1105-Oct15
      2106-Oct5
      3206-Oct10

       

      Thanks in advance,

      Francesco

        • Re: Qlik Sense and Type 2 slowly changing dimensions
          Francesco Leoni
          ClaimIDClaimStatusEffectiveDate
          1Created03-Sep
          2Created03-Oct
          1Registered09-Sep
          1Assigned10-Sep
          • Re: Qlik Sense and Type 2 slowly changing dimensions
            Mark Little

            HI,

             

            Take a look at the link below.

            Why You sometimes should Load a Master Table several times

             

            I know it is Written for QlikView but it will work for QlikSense as well.

             

            Mark

            • Re: Qlik Sense and Type 2 slowly changing dimensions
              Andrea Gigliotti

              you should provide us a sample qvf file to well understand what 's your expected result.

              • Re: Qlik Sense and Type 2 slowly changing dimensions
                Andy Weir

                Looking at what you have id be suggesting a data model with a fact table built around the Claim, Claimant and Payment Dimensions

                 

                Facts I can see are

                 

                Created

                Registered

                Assigned

                Payment

                 

                I assume they'd be cancellations and rejections and escalations to be added to reflect the workflow.

                 

                ClaimantId, ClaimId, PaymentId, Status, Date, Comments, Amount

                 

                Would probably work as a fact table structure.

                 

                Load each state into that fact table populating what you can and then distinct your dimension data into the dimension tables you will then have a star schema you can play with in lots of ways.

                 

                Final or latest states to be stored against each dimension.

                 

                Looks like you may have a missing relationship between your payment and what it is for.  You are authorising a payment for a claim at the moment you have no context for what it is for.  Also id guess the payment may be to a third party beneficary not just a claimant but thats just me over thinking it.

                 

                Anyway hope this helps

                 

                Regards

                 

                 

                Andy

                  • Re: Qlik Sense and Type 2 slowly changing dimensions
                    Francesco Leoni

                    Hi Andy,

                     

                    Thank you for your reply.

                     

                    You may be on the right track but you're missing the point.

                     

                    my problem is not to link those together. is to link the different dates. I'll explain better.

                     

                    in the DW I have the historical data of my client. (insurance) the tables I shared are just example because I cannot share anything for data protection reasons.

                     

                    Let's say that I am at the beginning of November and I need to display quarter end reports. I need to show what happened until the 30th of September. so if my data set for the claim is

                     

                    1Created03-May
                    2Created05-Sept
                    1Registered09-Sep
                    1Assigned10-Oct
                    2Registered25-Oct

                     

                    so I need to show that claim 1 is Registered and claim 2 is created, but if I select the end of October to see the month end reports I need to show that Claim 1 is Assigned and Claim 2 is Registered.

                     

                    At the same time, and this is the issue,

                     

                    I need to show the same logic but on the claimants on the claim date and for the payments on the payment date, to show all the payments made at that point with the correct status.

                     

                    PaymentIDClaimantIDPayment DateAmount
                    1110-Sep10
                    1105-Oct15
                    2106-Sept5
                    3206-Oct10

                     

                    for the quarter end, I need to show Payment 1 with amount of 10 , payment 2 with amount 5 and the total some is 15. while for the October report, Payment 1 is now 15 and I have also payment 3 with amount 10 so the total sum is 30.

                     

                    So, I'm trying to find a way to link everything together.

                     

                    Now what comes in mind is set analysis. but I would prefer another way.

                     

                    Thanks for your help.

                  • Re: Qlik Sense and Type 2 slowly changing dimensions
                    Luis Madriz

                    Hi Francesco,

                     

                    I'd recommend to validate your data against a functional design, meaning comprehend what the actions are and how the data reflect those actions. With a quick look at your data, I would try joining Claims and Payments in a Transaction table and leaving Claimants as the SCD... although I couldn't figure out what was the value that would change for a claimant.

                     

                    For example in Oracle eBusiness (R12) when you create a transaction against an Inventory Item, the system saves in the transaction table the unit of measure and relationship against the base unit of measure. This is because the UoM of an item can be changed at any time but the transactions will remain with the information they need regardless. Oracle could have linked UoMs and transactions by the dates but this would have been costly in terms of performance without adding much value.

                     

                    Going back to your design, or at least to the data you provided, you could attempt to link transactions with Claimant but make sure that each transaction refers to only one dimension (claimant), for which you may need a composite key.

                     

                    Of course, this is just quick recommendation base on a 5-min analysis as I'm not familiar with your business or your data and I agree with you in trying to create a model that will simplify your life in terms of set analysis

                     

                    All the best

                     

                    Luis