ProcessAdd for dimensions

I'm poking at some code that sends uses an XML/A ProcessAdd command to incrementally process a dimension. The command contains the out-of-line binding which specifies the query for the new rows. However, when the command gets submitted, I don't see anything that looks like the specified query in the profiler log. As I figure it, either:

a) The command isn't constructed correctly. SSAS is claiming success, and the dimension is processed at the end, so if the command is wrong, then I don't know what SSAS would be doing instead (a full process? a process update?).

b) My expectation of seeing that query, or something like it, in the profiler log, is wrong. We also have ProcessAdd commands for partitions, and those queries DO end up being displayed in Profiler. But maybe dimensions are different in some way.

The ProcessAdd stuff is very poorly documented, so it's entirely possible we did something wrong in implementing it. On the whole of the internet, I found exactly one complete sample of a ProcessAdd for a dimension, posted by EdwardM on the OLAP newsgroup. I tried plucking that out and using it as a sanity check, and I see very similar behavior to what I see with our own dimension. However, that sample references a dimension (NQ Customer) that doesn't exist in my AdventureWorks cube, so I have no idea if I hacked it correctly to match the Customer dimension in my AW.

I beleive the behavior is that if you dont get it right, Analysis Server will start using the original binding for dimension you process using ProcessAdd command.

Important is to move <DataSource> and <DataSourceView> sections from each individual <Process> section to <Batch> section before the empty <Bindings> element.

So it would look as

<Batch>
<Parallel>
<Process ...
</Parallel>
<DataSource ...
<DataSouceView ...
<Bindnings/>
</Batch>

There isnt much value of using ProcessAdd for partitions. It is simple wrapper around Create new partitition->ProcessFull->Merge into a original partition.
The main idea behind ProcessAdd that it gives you ability to only add members to dimesion, so partitions wouldnt drop aggregations as it happens during ProcesUpdate.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Thanks Edward, that's definitely something we didn't have right on our own ProcessAdd command.

However, I'm still having no luck getting the query criteria to be used (or, at least, the criteria never show up in any of the SQL statements in the profiler log).

I've gone back to the example you posted here:
http://groups.google.com/group/microsoft.public.sqlserver.olap/browse_frm/thread/59ccd215cd8afbf2/12b7a464419f5304?lnk=st&q=ProcessAdd&rnum=1#12b7a464419f5304
But I'm having no luck with that either. Is there something else I could be missing? Is that samples still good?

Thanks,
Kevin

|||

Yet there is another way you can try.
If you have a dimension based on a single table, you should be able to use what is called QueryBinding.
Check out T.K's whitepaper on the processing architechture http://msdn2.microsoft.com/en-us/library/ms345142.aspx#sqk2k5_asproc_topic7

It has example of QueryBinding used with ProcessAdd.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Hi Edward. I played around a bit with the QueryBinding, but couldn't make it work. Are you sure it's supposed to work with dimensions? According the XML/A docs, the elements of that binding are specific to PartitionBinding.

In any event, I was able to finally get ProcessAdd to work correctly with dimensions using the out-of-line DSV specification. I think the things that were tripping us up were that a) our DSV view specification wasn't quite right, b) the DSV was inside the ProcessAdd (not sure why that isn't supposed to work), c) we didn't have the DataSource, only the DSV, and d) we were missing the trailing empty bindings element (that's the one that really took a while to track down. Boy, the XML/A for ProcessAdd on a heavily snowflaked dimension is BIG and hair.

Thanks for your help.
|||

I hear you Kevin.
This functionality is really useful and it is hard to use.

Can't comment on the plans of upcoming release, but can assure we will try to do our best to make it easier to use it. And post here if you have any quesitons.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||>I beleive the behavior is that if you dont get it right, Analysis Server will start using the original binding for
>dimension you process using ProcessAdd command.

FYI, this doesn't appear to be the case anymore in SP2 - it now generates an internal error during incremental processing. Ultimately this is due to an incorrectly constructed XML/A command, so I don't expect MS to do anything about it, but I wanted to post this info in case anyone else runs into it.
|||

Hi Kevin,

I saw your entry that you have got it working with out of line DSV specification. I'm exactly trying to do the same thing and can't get it working (tried both query binding and the DSV binding). Pl can you share the source code if it is possible (just the part where you define your DSV spec).

Cheers,

Arun

The XML-A command I use :

<Batch Transaction="1" ProcessAffectedObjects="0"

xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"

xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<Parallel>

<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Object>

<DatabaseID>Test</DatabaseID>

<DimensionID>Tbl Currency</DimensionID>

</Object>

<Type>ProcessAdd</Type>

</Process>

</Parallel>

<DataSource xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="RelationalDataSource">

<ID>DS_Test</ID>

<Name>DS_Test</Name>

<ConnectionString>

Provider=SQLNCLI.1;Data Source=<<servername>>;Integrated Security=SSPI;Initial Catalog=<<dbname>>

</ConnectionString>

<Timeout>PT0S</Timeout>

</DataSource>

<DataSourceView xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlnsdl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlnsdl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlnswd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0" dwdesign-time-name="89ed7ba3-4e10-45c7-941f-11bcdda6f791" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<ID>DV_Test</ID>

<Name>DV_Test</Name>

<DataSourceID>DS_Test</DataSourceID>

<Schema>

<xschema id="DV_Test" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urnchemas-microsoft-com:xml-msdata" xmlns:msprop="urnchemas-microsoft-com:xml-msprop">

<xs:element name="dbo_tblCurrency" msdata:IsDataSet="true" msdata:UseCurrentLocale="true" >

<xs:complexType>

<xs:choice minOccurs="0" maxOccurs="unbounded">

<xs:element name="dbo_tblCurrency"

msprop:QueryDefinition="Select * from tblCurrency where CurrencyCode = 'ZZZ'"

mspropbTableName="tblCurrency"

msprop:IsLogical="True"

msprop:TableType="View">

</xs:element>

</xs:choice>

</xs:complexType>

</xs:element>

</xschema>

</Schema>

</DataSourceView>

<Bindings>

</Bindings>

</Batch>

|||

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:
http://www.artisconsulting.com/Blogs/tabid/94/EntryID/3/Default.aspx

|||

can you explain me how to fetch the member name column (msprop:ComputedColumnExpression="&quot;PHABRK_CODE&quot; + '#&amp;@.' + &quot;SHORT_DESC&quot) and to use it in VB.net application

<ObjectDefinition>

<DataSourceView xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlnsdl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlnsdl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">

<ID>ORION D 60500 RPM STL ORIORSAR_15232__20070619</ID>

<Name>ORION D 60500 RPM STL ORIORSAR_15232__20070619</Name>

<DataSourceID>ORION D 60500 RPM STL ORIORSAR_15232__20070619</DataSourceID>

<Schema>

<xschema id="ORION_x0020_D_x0020_60500_x0020_RPM_x0020_STL_x0020_ORIORSAR_15232__20070619" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urnchemas-microsoft-com:xml-msdata" xmlns:msprop="urnchemas-microsoft-com:xml-msprop">

<xs:element name="ORION_x0020_D_x0020_60500_x0020_RPM_x0020_STL_x0020_ORIORSAR_15232__20070619" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">

<xs:complexType>

<xs:choice minOccurs="0" maxOccurs="unbounded">

<xs:element name="D_GEOGRAPHY_GEO_15104" msprop:FriendlyName="D_GEOGRAPHY_GEO_15104" mspropbTableName="D_GEOGRAPHY_GEO_15104" msprop:TableType="Table">

<xs:complexType>

<xsequence>

<xs:element name="PHABRK_CODE" type="xstring" />

<xs:element name="Column1" msdata:ReadOnly="true" msprop:ComputedColumnExpression="&quot;PHABRK_CODE&quot; + '#&amp;@.' + &quot;SHORT_DESC&quot;" msprop:IsLogical="True" minOccurs="0">

<xsimpleType>

<xs:restriction base="xstring">

<xs:maxLength value="255" />

</xs:restriction>

</xsimpleType>

</xs:element>

<xs:element name="PARENT_CODE" type="xstring" minOccurs="0" />

<xs:element name="customrollup" mspropbColumnName="customrollup" msprop:ComputedColumnExpression="(select top 1 customrollup from D_GEOGRAPHY_GEO_15104)" mspropescription="customrollup" msprop:IsLogical="True" minOccurs="0">

<xsimpleType>

<xs:restriction base="xstring">

<xs:maxLength value="500" />

</xs:restriction>

</xsimpleType>

</xs:element>

</xsequence>

</xs:complexType>

</xs:element>

|||

Jothi-

I don't think your question has anything to do with this thread. Why don't you start a new thread and we'll try to respond?