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_Year_Week as Year_Week,
facts_ETK as ETK,
facts_Agent as Agent
left join (facts_link_temp)
load distinct ETK, PRODUCTCATEGORYID
load Distinct productcatname, productcategoryid
load distinct *, Year_Week&'-'&ETK&'-'&Agent as factskey
DROP table facts_link_temp;
plans_categoryname as productcatname,
plans_agent as Agent,
plans_year_week as Year_Week,
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;