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



        • 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



          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 Selvaraj

            Hi Kshitija,


            Can you share the existing syntax.


            Many Thanks


            • 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,