Guest User

Mondrian Tables

a guest
Apr 1st, 2016
37
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Dimensions TABLE
  2. CREATE TABLE AGENTS
  3. (    A_ID               BIGINT
  4.     ,A_NAME             VARCHAR(100)
  5.     ,A_ROLE             VARCHAR(100)
  6.     ,A_DEPARTMENT       VARCHAR(100)
  7.     ,CONSTRAINT A_ID_PK PRIMARY KEY(A_ID)
  8. );
  9.  
  10. Fact TABLE
  11.  
  12. CREATE TABLE TRANSACTIONS (
  13. AGENT_ID BIGINT
  14. ,A_NAME VARCHAR
  15. ,A_ROLE VARCHAR
  16. ,TERRITORY_ID BIGINT
  17. ,T_ZIPCODE BIGINT
  18. ,T_PLACE_NAME VARCHAR
  19. ,T_STATE VARCHAR
  20. ,T_COUNTRY VARCHAR
  21. ,PRODUCT_ID BIGINT NOT NULL
  22. ,P_NAME VARCHAR
  23. ,P_CATEGORY VARCHAR
  24. ,CHANNEL_ID BIGINT
  25. ,CH_NAME VARCHAR
  26. ,CH_TYPE VARCHAR
  27. ,CUSTOMER_ID BIGINT NOT NULL
  28. ,CS_NAME VARCHAR
  29. ,CS_TYPE VARCHAR
  30. ,IS_NEW_CUSTOMER BOOLEAN
  31. ,CLOSE_DATE DATE
  32. ,DAY_CNT_SPENT INTEGER
  33. ,TOTAL_EXPENSES BIGINT
  34. ,FORCASTED_SALES BIGINT
  35. ,UNIT_CNT_SOLD BIGINT
  36. ,PRICE_PER_UNIT BIGINT
  37. ,DISCOUNT_PERCENT BIGINT
  38. ,TOTAL_SALES BIGINT
  39. ,CONSTRAINT pk PRIMARY KEY (PRODUCT_ID, CUSTOMER_ID)
  40. );
  41.  
  42. Pre-aggregated TABLE - AG_TER_PRO
  43. AGENT_ID,
  44. TERRITORY_ID,
  45. PRODUCT_ID,
  46. SUM_TOTAL_SALES,
  47. SUM_TOTAL_EXPENSES,
RAW Paste Data