0 Replies Latest reply: Aug 14, 2013 2:44 AM by Levente Botha RSS

    Help me joining data and lower performance needs

      Hi all!

       

      I have a looks like easy but its not, problem.

      I have about 2.5m records in my facts table. Those are rows about sold products (name, etk, price etc).

      Those products are part of multiple product categories. We have agents who sell those.

      So we want to have product category, agent plans for every week. These plans for maximum 30 categories and about 15 agents. We made a link table, a record for all of the categories and all of the agents which has 25million rows. Its a little bit large (all agents(~30), all categories(~200)). Do you have any idea how could we make this better ?

       

      I have ETK, name, week, year, agent in facts.

      I have Product category, ETK in product categories.

      I have Agent, Product Category, Week, Year in plans table.

       

      we did the following:


      facts_link_temp:

      load distinct

           facts_Year_Week as Year_Week,

           facts_ETK as ETK,

           facts_Agent as Agent

      resident facts;

       

      left join (facts_link_temp)

           load distinct ETK, PRODUCTCATEGORYID

      resident product_categories_link;

       

      left_join(facts_link_temp)

           load Distinct productcatname, productcategoryid

      resident product_categories;

       

      facs_link:

           load distinct *, Year_Week&'-'&ETK&'-'&Agent as factskey

      resident facts_link_temp;

       

      DROP table facts_link_temp;

       

      outer join(facts_link)

      load distinct

           plans_categoryname as productcatname,

           plans_agent as Agent,

           plans_year_week as Year_Week,

           plans_key

      Resident plans;

       

      drop table product_categories_link;

      drop table product_categories;

       

      drop fields facts_ETK, facts_Year_Week, facts_Agent, plans_categoryname, plans_Agent, plans_year_week;

       

       

      Thank you in advance!

       

      Levi