3 Replies Latest reply: Aug 3, 2016 2:17 AM by Andres Prado RSS

    Star schema!

    Kshitija Shinde

      Hello everyone,

       

      Attached is my following data model, which takes a lot of response time .Can anyone suggest how to convert this in to star schema with relevant syntax? Thanks in advance.

       

      data model.png

      Thanks&Regards,

      Kshitija

        • Re: Star schema!
          Phaneendra Kunche

          Here is my approach..But can vary based on your many-many or one-many relations.

           

          1. Add Region, State & City attributes to Location. U can use left join

           

          Location:

          Load ..... from ;

          Left join

          Load .... from city;

          Left join

          Load .... from state;

          Left join

          Load .... from region;

           

           

          2. in ATM and Switch seems there is a Synthetic key.. This will solve 90% of the problem

          Try creating this new columnn both tables and drop SwitchID & MapID from one of those tables. i

          SwitchID & MapID as Key

           

           

          3. Try to merge CALL tables as much as possible. Cant guess on how you can merge as i dont know the granularity.

          • Re: Star schema!
            Karthik S

            Hi Kshitija,

             

            Can you share the existing syntax.

             

            Many Thanks

            Karthik

            • Re: Star schema!
              Andres Prado

              Hi Kshitija, it depends on a lot of factors. But from a quick glance, I could tell you some advises:

               

              1. You could put all the information of the Call into a single fact table (i.e Fact_Call), which means bring "CallForward", "CallLogStatus" and "CallService" to your current "CallLog" table.

              2.  Build a unique key to make the union between the new "Fact_Call" and the "CallClose_ATM" (I couldn't see any evident one right now, but for example something like CallLogId&RowNo() as Key_call_ATM)

              3. Bring "Region", "State", "City" and "Location" to a single table (i.e Master_Location)

               

              After those 3 steps you will have two fact tables, "Fact_Call" and "ATM" and a few dimensions (still not the best but better for Qlik than your current one.

               

              Hope it helps,

              Andrés