asked
Dimension table in wrong order
Using 2005, I build dimension tables several times a day to reload an essbase cube. There is a column on these table called Seq Number. This column is not indexed and is not an identity column, but the data has to be read by essbase in the sequence nbr order. The data in the table is not consistently loaded in the seq nbr order even though we use a "**Insert** into tablename **Select** col1, col2,.. **from** tablename **Order by** seq number " via an SSIS pkg. When we open the table in SSMS, we can see whether or not the rows are in seq number order. My question is two-fold: a. When a table is open in SSMS does it present in the way it was loaded? Does an Identity column or index determine otherwise? b. Will an identity column or clustered index force the table to retain the order in which it was loaded? Thanks!
a. No. When you open a table in SSMS, you are simply issuing a query to return the data from the table. Without an `order by` clause, you cannot guarantee that the data will always return in the same order, so certainly is not the insert order either. b. An identity column or a clustered index are again no guarantee of keeping the data in some 'loaded' order In order to ensure that your feed to Essbase is in the right order, you should always use and `order by` clause
So probably you will have to specify the order somewhere in the Essbase if it generates the selects dynamicaly (something like in case of SSAS - you define the order in the Dimension definition and SSAS than takes care about correct order.
In that case what makes you think that you have to supply the data in the right order?
Order of records which are output from a select is never guaranteed, unless there is an ORDER BY Clause in the select. Ad a) In case of Serial Plan and table with clustered index or if records are fetched directly from index, the output is mostly ordered in the order of such index, but even in case of the serial plan the order is not guaranteed without the ORDER BY Clause. Ad b) Clustered index defines the logical order of the data and also physical order of the rows on single database page. Hower individual database pages does not need to be allocated in the order of the clustered index. Again if you will query the data from such table, the order of rows is not guaranteed without the ORDER BY clause.
10 People are following this question.
