Help me joining data and lower performance needs

Wed, 14 Aug 2013 06:44:52 GMT

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

topic Help me joining data and lower performance needs in QlikView

Help me joining data and lower performance needs