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
