Because I have a very large subscriber dimension and the oracle temp space of the underlying Oracle db is limited, I want to do a processadd on a dimension.
The dimension is defined in a named query. In the original Datasource view, the where clause limits the result set to 'NL'.
I created a new DS view with only the subscriber named query in it. I copied this code in the xmla script and I changed the DS name to the original one. In this script, I also specified the where clause to take the 'UK' records.
Allthough the xmla script is being executed without error messages, it seems to ignore the ds view part and always queries on 'NL' instead of 'UK'.
Below, you can find the xmla source. I cannot figure out what is wrong here!
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Object>
<DatabaseID>Orange2</DatabaseID>
<DimensionID>Dim Subscriber</DimensionID>
</Object>
<Type>ProcessAdd</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
<DataSourceView xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0" dwd:design-time-name="4f20b272-3779-49b9-a59a-c461931f7a7b" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ID>Coyote</ID>
<Name>Coyote</Name>
<Annotations>
<Annotation>
<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:ShowFriendlyNames</Name>
<Value>true</Value>
</Annotation>
<Annotation>
<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:CurrentLayout</Name>
<Value>_ALL_TABLES_</Value>
</Annotation>
<Annotation>
<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:SchemaRestriction</Name>
<Value></Value>
</Annotation>
<Annotation>
<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:RetrieveRelationships</Name>
<Value>true</Value>
</Annotation>
<Annotation>
<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:Layouts</Name>
<Value>
<Layouts xmlns="">
<Diagram>
<Name>_ALL_TABLES_</Name>
<DiagramLayout>
<dds>
<diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="MSDDS.Rectilinear" defaultlineroute="MSDDS.Rectilinear" version="7" nextobject="2" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="1706" scrolltop="1297" gridx="150" gridy="150" marginx="5000" marginy="5000" zoom="100" x="19209" y="11933" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="0" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="1" backpictureclsid="{00000000-0000-0000-0000-000000000000}">
<font>
<ddsxmlobjectstreamwrapper binary="01000000900144420100065461686f6d61" />
</font>
<mouseicon>
<ddsxmlobjectstreamwrapper binary="6c74000000000000" />
</mouseicon>
</diagram>
<layoutmanager>
<ddsxmlobj />
</layoutmanager>
<ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="DimSubscriber" left="9048" top="4842" logicalid="1" controlid="1" masterid="0" hint1="0" hint2="0" width="4524" height="4842" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0">
<control>
<ddsxmlobjectstreaminitwrapper binary="00080000ac110000ea120000" />
</control>
<layoutobject>
<ddsxmlobj>
<property name="LogicalObject" value="DimSubscriber" vartype="8" />
</ddsxmlobj>
</layoutobject>
<shape groupshapeid="0" groupnode="0" />
</ddscontrol>
</dds>
</DiagramLayout>
<ShowRelationshipNames>False</ShowRelationshipNames>
<UseDiagramDefaultLayout>True</UseDiagramDefaultLayout>
<DiagramViewPortLeft>1706</DiagramViewPortLeft>
<DiagramViewPortTop>1297</DiagramViewPortTop>
<DiagramBoundingLeft>0</DiagramBoundingLeft>
<DiagramBoundingTop>0</DiagramBoundingTop>
<DiagramZoom>100</DiagramZoom>
</Diagram>
</Layouts>
</Value>
</Annotation>
</Annotations>
<DataSourceID>CCDM</DataSourceID>
<Schema>
<xs:schema id="CoyoteTemp" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:msprop="urn:schemas-microsoft-com:xml-msprop">
<xs:element name="CoyoteTemp" msdata:IsDataSet="true" msdata:UseCurrentLocale="true" msprop:design-time-name="f3afc85c-878d-40d3-ae96-29ac12d2bfdb">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="DimSubscriber" msprop:QueryDefinition="SELECT s.SUBS_GID, s.MCO_CODE, a.COUNTRY_CODE, a.PROVINCE, a.CITY, s.CONTRACT_START_DATE, c.SALUTATION, c.GENDER_CODE, 
 c.YEAROFBIRTH
FROM SUBS_RECENT s INNER JOIN
 (SELECT DISTINCT MCO_CODE
 FROM SEL_MCO_MONTHS m) m_1 ON m_1.MCO_CODE = s.MCO_CODE LEFT OUTER JOIN
 ADDR_RECENT a ON a.ADDRESS_GID = s.ADDRESS_GID_PYR AND a.MCO_CODE = s.MCO_CODE INNER JOIN
 CUST_RECENT c ON c.CUSTOMER_GID = s.CUSTOMER_GID_PYR AND c.MCO_CODE = s.MCO_CODE
WHERE (to_char(s.DG_END_DATE, 'YYYY') > 2004) AND (s.MCO_CODE = 'UK')" msprop:DbTableName="DimSubscriber" msprop:QueryBuilder="SpecificQueryBuilder" msprop:IsLogical="True" msprop:FriendlyName="DimSubscriber" msprop:design-time-name="11c74f2d-620c-476e-94cc-8b4159fba22c" msprop:TableType="View" msprop:Description="">
<xs:complexType>
<xs:sequence>
<xs:element name="SUBS_GID" msprop:FriendlyName="SUBS_GID" msprop:DbColumnName="SUBS_GID" msprop:design-time-name="5861229e-88ff-410f-b460-5661599c8fa3" type="xs:long" minOccurs="0" />
<xs:element name="MCO_CODE" msprop:FriendlyName="MCO_CODE" msprop:DbColumnName="MCO_CODE" msprop:design-time-name="8400aa15-e5b7-4877-9a98-11c750e6581d" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="2" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="COUNTRY_CODE" msprop:FriendlyName="COUNTRY_CODE" msprop:DbColumnName="COUNTRY_CODE" msprop:design-time-name="56311eac-82ab-4413-adf8-0cb68b9feb4f" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="2" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="PROVINCE" msprop:FriendlyName="PROVINCE" msprop:DbColumnName="PROVINCE" msprop:design-time-name="4281784a-6adb-42d4-9e2a-f671dcb38719" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="40" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="CITY" msprop:FriendlyName="CITY" msprop:DbColumnName="CITY" msprop:design-time-name="c118bb15-ff58-4656-be7d-1de0dbe7189c" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="40" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="CONTRACT_START_DATE" msprop:FriendlyName="CONTRACT_START_DATE" msprop:DbColumnName="CONTRACT_START_DATE" msprop:design-time-name="9027e7db-2f14-49f8-88a3-34cb3134173c" type="xs:dateTime" minOccurs="0" />
<xs:element name="SALUTATION" msprop:FriendlyName="SALUTATION" msprop:DbColumnName="SALUTATION" msprop:design-time-name="ab503dd5-a86f-403a-896d-f9b8960b62d6" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="20" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="GENDER_CODE" msprop:FriendlyName="GENDER_CODE" msprop:DbColumnName="GENDER_CODE" msprop:design-time-name="76a0a28d-bbf8-45e3-bebc-3df3e9141b21" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="1" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="YEAROFBIRTH" msprop:FriendlyName="YEAROFBIRTH" msprop:DbColumnName="YEAROFBIRTH" msprop:design-time-name="1bdd4306-c13f-41ee-826a-b0202d10ecf4" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="4" />
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1" />
</Schema>
</DataSourceView>
</Process>
</Parallel>
</Batch>
I have not used ProcessAdd myself, but the documentation says that the <DataSourceView> tags can be under either Process or Batch, but seems to imply that if the Process is part of a Batch that the DataSourceView should be defined at the Batch level.
So you could try removing the <Batch> and <Parallel> tags if you are just processing the one dimension or you could try moving the DataSourceView up to the Batch level.
|||I have had the same frustration with ProcessAdd trying to figure out all the XMLA syntax. I finally got it to work and have blogged about it in addition to some performance tests. Hope this helps.
Some full working examples of ProcessAdd:
http://www.artisconsulting.com/Blogs/tabid/94/EntryID/2/Default.aspx
And some performance tests showing the performance of ProcessAdd on large dimensions:
http://www.artisconsulting.com/Blogs/tabid/94/EntryID/3/Default.aspx
No comments:
Post a Comment