Excel Data Mining Add in: "Order By" not working

Hello,

I am trying insert the "Order by" clause into DMX but nothing is working.

INSERT INTO MINING STRUCTURE [ARS] (
[OrderID],
[Product_Table](SKIP, [Product])
)
SHAPE {
OPENQUERY ([dwMDA on PSD_TEST_TEST], 'Select Top 30000 "OrderID"
From "dwMDA"."dbo"."vDetail" Order By "OrderID"')
} APPEND
( {
OPENQUERY ([dwMDA on PSD_TEST_TEST], 'Select Top 30000 "OrderID","Product"
From "dwMDA"."dbo"."vDetail" Order By "OrderID"')} RELATE [OrderID] TO [OrderID]
) AS T

This is the DMX for market basket analysis.

I am also unable to use "order by" when i pull data into excel.

here's an example of something that doesn't work:

Select Top 20000 "OrderID","ProductGroupDescription"
From "dwMDA"."dbo"."vDetail"

Order By "OrderID"

Can someone show me the exact working SQL commands they are using for the "Order By" clause when they import data from sql into an excel spreadsheet?

Even better would be if someone

1. Open excel, and go to the data mining tab

2. Click on the Cluster Button

3. Select Analysis Service Data Source

4. Input a command the includes "Order By"

5. Run the model

6. Show me the tracer.

Thanks

Davy

Below is the trace for the Cluster operation you requested (on top of the Anventure Works DW sample database).

Does your query work in SQL Server Management Studio? The whole query string (the argument for OpenQuery) is sent directly to SQL Server. What do you mean "nothing is working"? Do you get an error? Or is it that processing takes a very long time?

--
CALL SystemOpenSchema([Adventure Works DW], 'TABLES')

--
CALL SystemOpenSchema([Adventure Works DW], 'TABLECOLUMNS',,'dbo','vTargetMail')

===BeginTransaction===
--
<Create xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ParentObject>
<DatabaseID>PMMLTestDB</DatabaseID>
</ParentObject>
<ObjectDefinition>
<MiningStructure>
<ID>Adventure Works DW Structure</ID>
<Name>Adventure Works DW Structure</Name>
<Description>Structure for the Adventure Works DW server data source</Description>
<Columns>
<Column xsi:type="ScalarMiningStructureColumn">
<Name>BirthDate</Name>
<ID>BirthDate</ID>
<Type>Date</Type>
<IsKey>false</IsKey>
<Content>Continuous</Content>
</Column>
<Column xsi:type="ScalarMiningStructureColumn">
<Name>MaritalStatus</Name>
<ID>MaritalStatus</ID>
<Type>Text</Type>
<IsKey>false</IsKey>
<Content>Discrete</Content>
</Column>
<Column xsi:type="ScalarMiningStructureColumn">
<Name>Gender</Name>
<ID>Gender</ID>
<Type>Text</Type>
<IsKey>false</IsKey>
<Content>Discrete</Content>
</Column>
<Column xsi:type="ScalarMiningStructureColumn">
<Name>YearlyIncome</Name>
<ID>YearlyIncome</ID>
<Type>Double</Type>
<IsKey>false</IsKey>
<Content>Continuous</Content>
</Column>
<Column xsi:type="ScalarMiningStructureColumn">
<Name>TotalChildren</Name>
<ID>TotalChildren</ID>
<Type>Text</Type>
<IsKey>false</IsKey>
<Content>Discrete</Content>
</Column>
<Column xsi:type="ScalarMiningStructureColumn">
<Name>NumberChildrenAtHome</Name>
<ID>NumberChildrenAtHome</ID>
<Type>Text</Type>
<IsKey>false</IsKey>
<Content>Discrete</Content>
</Column>
<Column xsi:type="ScalarMiningStructureColumn">
<Name>EnglishEducation</Name>
<ID>EnglishEducation</ID>
<Type>Text</Type>
<IsKey>false</IsKey>
<Content>Discrete</Content>
</Column>
<Column xsi:type="ScalarMiningStructureColumn">
<Name>__RowIndex</Name>
<ID>__RowIndex</ID>
<Type>Long</Type>
<IsKey>true</IsKey>
<Content>Key</Content>
</Column>
</Columns>
<ErrorConfiguration>
<KeyErrorLimit>-1</KeyErrorLimit>
<KeyErrorAction>DiscardRecord</KeyErrorAction>
<KeyDuplicate>IgnoreError</KeyDuplicate>
<KeyNotFound>IgnoreError</KeyNotFound>
<NullKeyConvertedToUnknown>IgnoreError</NullKeyConvertedToUnknown>
</ErrorConfiguration>
<MiningModels>
<MiningModel>
<ID>Adventure Works DW - Clustering</ID>
<Name>Adventure Works DW - Clustering</Name>
<Description>Microsoft_Clustering model built on the Adventure Works DW server data source</Description>
<Algorithm>Microsoft_Clustering</Algorithm>
<AlgorithmParameters>
<AlgorithmParameter>
<Name>CLUSTER_COUNT</Name>
<Value>0</Value>
</AlgorithmParameter>
</AlgorithmParameters>
<Columns>
<Column>
<Name>BirthDate</Name>
<ID>BirthDate</ID>
<SourceColumnID>BirthDate</SourceColumnID>
<Usage>Input</Usage>
<ModelingFlags />
</Column>
<Column>
<Name>MaritalStatus</Name>
<ID>MaritalStatus</ID>
<SourceColumnID>MaritalStatus</SourceColumnID>
<Usage>Input</Usage>
<ModelingFlags />
</Column>
<Column>
<Name>Gender</Name>
<ID>Gender</ID>
<SourceColumnID>Gender</SourceColumnID>
<Usage>Input</Usage>
<ModelingFlags />
</Column>
<Column>
<Name>YearlyIncome</Name>
<ID>YearlyIncome</ID>
<SourceColumnID>YearlyIncome</SourceColumnID>
<Usage>Input</Usage>
<ModelingFlags />
</Column>
<Column>
<Name>TotalChildren</Name>
<ID>TotalChildren</ID>
<SourceColumnID>TotalChildren</SourceColumnID>
<Usage>Input</Usage>
<ModelingFlags />
</Column>
<Column>
<Name>NumberChildrenAtHome</Name>
<ID>NumberChildrenAtHome</ID>
<SourceColumnID>NumberChildrenAtHome</SourceColumnID>
<Usage>Input</Usage>
<ModelingFlags />
</Column>
<Column>
<Name>EnglishEducation</Name>
<ID>EnglishEducation</ID>
<SourceColumnID>EnglishEducation</SourceColumnID>
<Usage>Input</Usage>
<ModelingFlags />
</Column>
<Column>
<Name>__RowIndex</Name>
<ID>__RowIndex</ID>
<SourceColumnID>__RowIndex</SourceColumnID>
<Usage>Key</Usage>
<ModelingFlags />
</Column>
</Columns>
</MiningModel>
</MiningModels>
</MiningStructure>
</ObjectDefinition>
</Create>

--
INSERT INTO MINING STRUCTURE [Adventure Works DW Structure] ( SKIP,
[BirthDate],
[MaritalStatus],
[Gender],
[YearlyIncome],
[TotalChildren],
[NumberChildrenAtHome],
[EnglishEducation]) OPENQUERY ([Adventure Works DW], 'SELECT TOP 100 "CustomerKey", "BirthDate", "MaritalStatus", "Gender", "YearlyIncome", "TotalChildren", "NumberChildrenAtHome", "EnglishEducation"
FROM "dbo"."vTargetMail" ORDER BY CustomerKey')

===CommitTransaction===