XMLA Processing PushedDataSource et Création de partition, dimension
Bonjour à tous,
Dans ems recherches faisant suite à ce topic je mme retrouve à utiliser du XMLA pour manier mes objets SSAS.
le but étant d'alimenter un cube à partir de données spécifiques, ne provenant pas d'une table, query etc.. Je souhaite décrire les données lignes à lignes.
Je parviens donc à alimenter une partition :
Code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88
| <Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
<Command>
<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<Type>ProcessFull</Type>
<Object xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<DatabaseID>BaseCube</DatabaseID>
<CubeID>FactFinance</CubeID>
<MeasureGroupID>Fact Finance</MeasureGroupID>
<PartitionID>Fact Finance</PartitionID>
</Object>
<Bindings xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<!--Toutes les dimensions-->
<Binding xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<DatabaseID>BaseCube</DatabaseID>
<CubeID>FactFinance</CubeID>
<MeasureGroupID>Fact Finance</MeasureGroupID>
<CubeDimensionID>Scenario</CubeDimensionID>
<AttributeID>Scenario Key</AttributeID>
<KeyColumns>
<KeyColumn>
<Source xsi:type="ColumnBinding">
<TableID/>
<ColumnID>ScenarioKey</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
</Binding>
<!--Toutes les mesures-->
<Binding xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<DatabaseID>BaseCube</DatabaseID>
<CubeID>FactFinance</CubeID>
<MeasureGroupID>Fact Finance</MeasureGroupID>
<MeasureID>Amount</MeasureID>
<Source xsi:type="ColumnBinding">
<TableID/>
<ColumnID>Amount</ColumnID>
</Source>
</Binding>
</Bindings>
<DataSource xsi:type="PushedDataSource">
<root Parameter="InputRowset"/>
<EndOfData Parameter="EndOfInputRowset"/>
</DataSource>
</Process>
</Command>
<Properties>
</Properties>
<Parameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:schemas-microsoft-com:xml-analysis">
<Parameter>
<Name>EndOfInputRowset</Name>
<Value xsi:type="xsd:boolean">true</Value>
</Parameter>
<Parameter>
<Name>InputRowset</Name>
<Value xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:schema targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified">
<xsd:element name="root">
<xsd:complexType>
<xsd:sequence minOccurs="0" maxOccurs="unbounded">
<xsd:element name="row" type="row" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:complexType name="row">
<xsd:sequence>
<xsd:element sql:field="ScenarioKey" name="ScenarioKey" type="xsd:unsignedInt" minOccurs="0" />
<xsd:element sql:field="Amount" name="Amount" type="xsd:double" minOccurs="0" />
</xsd:sequence>
</xsd:complexType>
</xsd:schema>
<row>
<ScenarioKey>1</ScenarioKey>
<Amount>10</Amount>
</row>
<row>
<ScenarioKey>2</ScenarioKey>
<Amount>20</Amount>
</row>
</Value>
</Parameter>
</Parameters>
</Execute> |
Alimenter une dimension :
Code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72
| <Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
<Command>
<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<Type>ProcessAdd</Type>
<Object xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<DatabaseID>BaseCube</DatabaseID>
<DimensionID>Scenario</DimensionID>
</Object>
<Bindings xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Binding xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<DatabaseID>BaseCube</DatabaseID>
<DimensionID>Scenario</DimensionID>
<AttributeID>Scenario Key</AttributeID>
<KeyColumns>
<KeyColumn>
<Source xsi:type="ColumnBinding">
<TableID/>
<ColumnID>ScenarioKey</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<NameColumn>
<Source xsi:type="ColumnBinding">
<TableID/>
<ColumnID>ScenarioName</ColumnID>
</Source>
</NameColumn>
</Binding>
</Bindings>
<DataSource xsi:type="PushedDataSource">
<root Parameter="InputRowset"/>
<EndOfData Parameter="EndOfInputRowset"/>
</DataSource>
</Process>
</Command>
<Properties>
</Properties>
<Parameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:schemas-microsoft-com:xml-analysis">
<Parameter>
<Name>EndOfInputRowset</Name>
<Value xsi:type="xsd:boolean">true</Value>
</Parameter>
<Parameter>
<Name>InputRowset</Name>
<Value xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:schema targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified">
<xsd:element name="root">
<xsd:complexType>
<xsd:sequence minOccurs="0" maxOccurs="unbounded">
<xsd:element name="row" type="row" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:complexType name="row">
<xsd:sequence>
<xsd:element sql:field="ScenarioKey" name="ScenarioKey" type="xsd:unsignedInt" minOccurs="0" />
<xsd:element sql:field="ScenarioName" name="ScenarioName" type="xsd:string" minOccurs="0" />
</xsd:sequence>
</xsd:complexType>
</xsd:schema>
<row>
<ScenarioKey>3</ScenarioKey>
<ScenarioName>Name3</ScenarioName>
</row>
<row>
<ScenarioKey>4</ScenarioKey>
<ScenarioName>Name4</ScenarioName>
</row>
</Value>
</Parameter>
</Parameters>
</Execute> |
Maintenant je cherche, à partir d'un cube qui existe déjà, à créer une nouvelle partition en définissant tous ces champs.
J'ai pour le moment le script pour créer cette partition en mode Query, mais parlant assez mal le XMLA, je en parviens à définir maintenant la liste de mes champs.
Code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
| <Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>BaseCube</DatabaseID>
<CubeID>FactFinance</CubeID>
<MeasureGroupID>Fact Finance</MeasureGroupID>
<PartitionID>Fact Finance 1</PartitionID>
</Object>
<ObjectDefinition>
<Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">
<ID>Fact Finance 1</ID>
<Name>Fact Finance 1</Name>
<Source xsi:type="QueryBinding">
<DataSourceID>AdventureWorksDW2008</DataSourceID>
<QueryDefinition>
SELECT [dbo].[FactFinance].[FinanceKey],[dbo].[FactFinance].[DateKey],[dbo].[FactFinance].[OrganizationKey],[dbo].[FactFinance].[DepartmentGroupKey],[dbo].[FactFinance].[ScenarioKey],[dbo].[FactFinance].[AccountKey],[dbo].[FactFinance].[Amount]
FROM [dbo].[FactFinance]
</QueryDefinition>
</Source>
<StorageMode>Molap</StorageMode>
<ProcessingMode>Regular</ProcessingMode>
</Partition>
</ObjectDefinition>
</Alter> |
Merci pour vos idées :)