question

paws27284 avatar image
paws27284 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!
sql-server-2005
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
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
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·
+1 you have been a little bit quicker @Kev.
0 Likes 0 ·
From what I understand there is not "select" that is accessible when Essbase reads the table. so I am not sure how I can add an Order by clause. Any other thoughts? Any essbase fans out there?
0 Likes 0 ·
In that case what makes you think that you have to supply the data in the right order?
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
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 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.