SQL Server Performance

Beginner's dim loading question.

Discussion in 'SQL Server 2005 Integration Services' started by johnslg, Aug 1, 2007.

  1. johnslg New Member

    I have a factSales table and a dimCustomer table (among others); all my data is coming from a single OLTP SQL Server db.
    When loading data do I parse through the sales rows in my OLTP and pick out customers and load them into the dimCustomers table
    OR
    Do I just load all the customers from the OLTP Customer table to the OLAP dimCustomer table?
  2. satya Moderator

    Do you need fast method for performance or is it not a factor?
  3. johnslg New Member

    The OLTP db will have several million rows, several hundred thousand new rows a day, so I'm thinking performance is going to be a rather large factor.
  4. satya Moderator

    Refer to this Technet link for Analysis Services performance as your requirement needs to be considered carefully, also refer to the subsections on left hand pane on that article.
  5. ranjitjain New Member

    Hi,
    If you are using ssas 2005 then while creating cube you must have specified your customer dimension to be a named query or directly linked to a table.
    So if it's a table then it will directly load data from table else if a named query then it will load from that result.
    From performance perspective I feel best would be to go with table type link.
  6. dineshasanka Moderator

    I feel, it is better to load customer dimension first by using slowly changing dimension object and then load the fact table
  7. ranjitjain New Member

    hi Dinesh,
    He has to first process dimension and then only he can process fact table otherwise processing will error.
    Relation between dim and fact is like parent and child.
    so if parent is not processed which is dim then fact or measure processing will error which is child.
  8. Raulie New Member

    [quote user="johnslg"]
    I have a factSales table and a dimCustomer table (among others); all my data is coming from a single OLTP SQL Server db.
    When loading data do I parse through the sales rows in my OLTP and pick out customers and load them into the dimCustomers table
    OR
    Do I just load all the customers from the OLTP Customer table to the OLAP dimCustomer table?
    [/quote]
    Ok I know your Database contains several million rows, but what is relevant right now is your Customer Table (OLTP), how big is the customer table? Also your Dimension loading strategy all depends on the requirements you set for each dimension, is the customer dimension going to use a Type 1, 2, or 3 SCD or a combination of all three? If you do not plan to use Type 2 or 3 SCD than overwriting (type 1 SCD) the dimension records with fresh OLTP data is an option.

Share This Page