I have a parent-child product dimension.
It is parent-child because it is unbalanced - some products have more
levels than others.
This dimension has the product members for the entire Group.
However, because different products have different relevant dimensions
I have a number of cubes - i.e. I don't want to put all measures & all
dimensions of all products into just one cube.
If I were to use this product dimension in all cubes I have two
problems:
1. The end user sees numerous 'irrelevant' product members when
exploring the dimension
2. More seriously, because the seperate cubes will only have facts for
the appropriate products the aggregation up to 'All' (Group) will be
misleading.
It would seem that I should have a centrally maintained product table &
from this create a number of subset product dimensions.
Is this the best approach?
Many thanks.Why don't try perspective for your first requirement to show different
product for different user.
I don't think No.2 is a real issue, for fact records are linked to their
product in the star schema tables.
"Statsman" wrote:
> I have a parent-child product dimension.
> It is parent-child because it is unbalanced - some products have more
> levels than others.
> This dimension has the product members for the entire Group.
> However, because different products have different relevant dimensions
> I have a number of cubes - i.e. I don't want to put all measures & all
> dimensions of all products into just one cube.
> If I were to use this product dimension in all cubes I have two
> problems:
> 1. The end user sees numerous 'irrelevant' product members when
> exploring the dimension
> 2. More seriously, because the seperate cubes will only have facts for
> the appropriate products the aggregation up to 'All' (Group) will be
> misleading.
> It would seem that I should have a centrally maintained product table &
> from this create a number of subset product dimensions.
>
> Is this the best approach?
>
> Many thanks.
>|||Why don't try perspective for your first requirement to show different
product for different user.
I don't think No.2 is a real issue, for fact records are linked to their
product in the star schema tables.
"Statsman" wrote:
> I have a parent-child product dimension.
> It is parent-child because it is unbalanced - some products have more
> levels than others.
> This dimension has the product members for the entire Group.
> However, because different products have different relevant dimensions
> I have a number of cubes - i.e. I don't want to put all measures & all
> dimensions of all products into just one cube.
> If I were to use this product dimension in all cubes I have two
> problems:
> 1. The end user sees numerous 'irrelevant' product members when
> exploring the dimension
> 2. More seriously, because the seperate cubes will only have facts for
> the appropriate products the aggregation up to 'All' (Group) will be
> misleading.
> It would seem that I should have a centrally maintained product table &
> from this create a number of subset product dimensions.
>
> Is this the best approach?
>
> Many thanks.
>
Showing posts with label dimension. Show all posts
Showing posts with label dimension. Show all posts
Monday, March 26, 2012
Tuesday, March 20, 2012
ProcessingPriority
The documentation for the ProcessingPriority property of a dimension says:
Determines the processing priority of the dimension during background operations such as lazy aggregation, indexing, or clustering.
So if I run an XMLA batch which says:
Code Snippet
<Parallel>
<Process>
<Object>
<DatabaseID>myDB</DatabaseID>
<DimensionID>myDim1</DimensionID>
</Object>
<Type>ProcessFull</Type>
</Process>
<Process>
<Object>
<DatabaseID>myDB</DatabaseID>
<DimensionID>myDim2</DimensionID>
</Object>
<Type>ProcessFull</Type>
</Process>
</Parallel>
Will ProcessingPriority on one of those dimensions have any impact on which order they get processed in for the main part of the processing (the part that fires off SQL queries, not the part that happens after all the SQL queries are done)?
I have one dimension which has lots of rows and lots of attributes and it's very flat in terms of not being able to have indirect attribute relationships. I'm wanting to get that dimension to process first so that there's not as much competition for memory. We tend to run out of memory on our 32-bit dev box when that dimension and others are processing simultaneously. I'm wondering if ProcessingPriority can help me or if I need to manually control the order of dimension processing in my XMLA script.
For ProcessingPriority, a higher number means higher priority, right? Can you use negative numbers? How does zero compare to a high number? I assume zero means low priority, but I thought I'd ask.
If you run these in parallel, I believe the order of the SQL statement and result set processing will be in parellel. One dimension may gain priority over another for memory or other resources for the operations you describe, but that's not the same as running in a serial mode. If you need one to run before the other, remove the <parallel> instruction and the batch will default to serial mode.
Good luck,
Bryan
Monday, March 12, 2012
processing dimension does nothing for 12 hours then continues suddenly
Processing our large dimensions on our production 64-bit hardware has worked fine. Our dev environment is 32-bit and has SQL, SSRS, SSIS, and SSAS on the same box. The /3GB switch seems to make lots of things unstable so we're only dealing with 2GB that SSAS could possibly use, and half of that is consumed by other processes. So I'm having trouble getting the cube to process due to low memory availability and large dimensions. I've changed MaxParallel to 1 so that it won't do multiple dimensions or partitions in parallel. That usually prevents it from running out of memory and failing. I'm still having sporatic problems.
Specifically, last night we saw processing hang for over 12 hours right in the middle of processing one of the largest dimensions then suddenly pick up and continue on. There were no other workloads going on that box, so I'm looking for an explanation of what SSAS was doing all that time? Before it kicks off the next bit of processing, I know it looks to see if there are enough resources. If there are not enough resources, what does it do? Does it sleep then check again after a while? What's it waiting for, and what can I do to make it happy so it continues when it gets into a wait state like that?
Here are my log files from this oddity last night:
6/4/2007 6:39:50 PM (ProgressReportBegin, WriteDecode): Started processing the 'Account Number' hierarchy.
6/4/2007 6:39:50 PM (ProgressReportCurrent, WriteDecode, MYSERVERNAME.MYCUBE.Account.Account Number): 0
6/4/2007 6:40:14 PM (ProgressReportCurrent, WriteDecode, MYSERVERNAME.MYCUBE.Account.Account Number): 1
6/4/2007 6:40:14 PM (ProgressReportEnd, WriteDecode): Finished processing the 'Account Number' hierarchy.
6/5/2007 8:54:31 AM (ProgressReportBegin, WriteDecode): Started processing the 'First Name' hierarchy.
6/5/2007 8:54:31 AM (ProgressReportCurrent, WriteDecode, MYSERVERNAME.MYCUBE.Account.First Name): 0
6/5/2007 8:54:32 AM (ProgressReportCurrent, WriteDecode, MYSERVERNAME.MYCUBE.Account.First Name): 1
6/5/2007 8:54:32 AM (ProgressReportEnd, WriteDecode): Finished processing the 'First Name' hierarchy.
Hi Greg,
I had some problems with SSAS 2005 getting stuck during processing. For me it was allways during cube processing (vs dimension processing). We had one suggestion from Microsoft, that actually sometimes worked. We were told to execute simple MDX query to SSAS to wake it up. Unfortunatelly that did not work consistently.
What worked for us is changing SSAS server parameter ThreadPool\Process\MaxThreads from default value of 64 to 150.
As in your case your dimension started around 9:00am (somebody in the office), there is very good chance that somebody submitted some query to server and wake it up.
More information about problem like mine is here:
http://blogs.msdn.com/psssql/archive/2007/01/16/processing-appears-to-stall-or-become-sluggish-on-multi-processor-machines-running-analysis-services-2005.aspx
Regards,
Vidas Matelis
|||I'll try that tip about submitting an MDX query and report back if that works. I had read Wayne's post but had completely forgotten about the "submit a simple MDX query" tip. Thanks Vidas.
As for raising the # of threads, I'll play with that, too. Since we've got MaxParallel=1 to prevent it from doing so much in parallel it runs out of memory (whether that's the best idea, I don't know) I certainly don't think it's short on threads. But I can easily check in perfmon.
|||The suggestion of "run an MDX query and that will wake up the processing" does seem to do the trick. One problem, though, is when your cube isn't processed, you can't run an MDX query to wake it up. The best solution I've found is to run a Clear Cache statement:
<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Adventure Works DW</DatabaseID>
</Object>
</ClearCache>
processing dimension does nothing for 12 hours then continues suddenly
Processing our large dimensions on our production 64-bit hardware has worked fine. Our dev environment is 32-bit and has SQL, SSRS, SSIS, and SSAS on the same box. The /3GB switch seems to make lots of things unstable so we're only dealing with 2GB that SSAS could possibly use, and half of that is consumed by other processes. So I'm having trouble getting the cube to process due to low memory availability and large dimensions. I've changed MaxParallel to 1 so that it won't do multiple dimensions or partitions in parallel. That usually prevents it from running out of memory and failing. I'm still having sporatic problems.
Specifically, last night we saw processing hang for over 12 hours right in the middle of processing one of the largest dimensions then suddenly pick up and continue on. There were no other workloads going on that box, so I'm looking for an explanation of what SSAS was doing all that time? Before it kicks off the next bit of processing, I know it looks to see if there are enough resources. If there are not enough resources, what does it do? Does it sleep then check again after a while? What's it waiting for, and what can I do to make it happy so it continues when it gets into a wait state like that?
Here are my log files from this oddity last night:
6/4/2007 6:39:50 PM (ProgressReportBegin, WriteDecode): Started processing the 'Account Number' hierarchy.
6/4/2007 6:39:50 PM (ProgressReportCurrent, WriteDecode, MYSERVERNAME.MYCUBE.Account.Account Number): 0
6/4/2007 6:40:14 PM (ProgressReportCurrent, WriteDecode, MYSERVERNAME.MYCUBE.Account.Account Number): 1
6/4/2007 6:40:14 PM (ProgressReportEnd, WriteDecode): Finished processing the 'Account Number' hierarchy.
6/5/2007 8:54:31 AM (ProgressReportBegin, WriteDecode): Started processing the 'First Name' hierarchy.
6/5/2007 8:54:31 AM (ProgressReportCurrent, WriteDecode, MYSERVERNAME.MYCUBE.Account.First Name): 0
6/5/2007 8:54:32 AM (ProgressReportCurrent, WriteDecode, MYSERVERNAME.MYCUBE.Account.First Name): 1
6/5/2007 8:54:32 AM (ProgressReportEnd, WriteDecode): Finished processing the 'First Name' hierarchy.
Hi Greg,
I had some problems with SSAS 2005 getting stuck during processing. For me it was allways during cube processing (vs dimension processing). We had one suggestion from Microsoft, that actually sometimes worked. We were told to execute simple MDX query to SSAS to wake it up. Unfortunatelly that did not work consistently.
What worked for us is changing SSAS server parameter ThreadPool\Process\MaxThreads from default value of 64 to 150.
As in your case your dimension started around 9:00am (somebody in the office), there is very good chance that somebody submitted some query to server and wake it up.
More information about problem like mine is here:
http://blogs.msdn.com/psssql/archive/2007/01/16/processing-appears-to-stall-or-become-sluggish-on-multi-processor-machines-running-analysis-services-2005.aspx
Regards,
Vidas Matelis
|||I'll try that tip about submitting an MDX query and report back if that works. I had read Wayne's post but had completely forgotten about the "submit a simple MDX query" tip. Thanks Vidas.
As for raising the # of threads, I'll play with that, too. Since we've got MaxParallel=1 to prevent it from doing so much in parallel it runs out of memory (whether that's the best idea, I don't know) I certainly don't think it's short on threads. But I can easily check in perfmon.
|||The suggestion of "run an MDX query and that will wake up the processing" does seem to do the trick. One problem, though, is when your cube isn't processed, you can't run an MDX query to wake it up. The best solution I've found is to run a Clear Cache statement:
<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Adventure Works DW</DatabaseID>
</Object>
</ClearCache>
processing dimension does nothing for 12 hours then continues suddenly
Processing our large dimensions on our production 64-bit hardware has worked fine. Our dev environment is 32-bit and has SQL, SSRS, SSIS, and SSAS on the same box. The /3GB switch seems to make lots of things unstable so we're only dealing with 2GB that SSAS could possibly use, and half of that is consumed by other processes. So I'm having trouble getting the cube to process due to low memory availability and large dimensions. I've changed MaxParallel to 1 so that it won't do multiple dimensions or partitions in parallel. That usually prevents it from running out of memory and failing. I'm still having sporatic problems.
Specifically, last night we saw processing hang for over 12 hours right in the middle of processing one of the largest dimensions then suddenly pick up and continue on. There were no other workloads going on that box, so I'm looking for an explanation of what SSAS was doing all that time? Before it kicks off the next bit of processing, I know it looks to see if there are enough resources. If there are not enough resources, what does it do? Does it sleep then check again after a while? What's it waiting for, and what can I do to make it happy so it continues when it gets into a wait state like that?
Here are my log files from this oddity last night:
6/4/2007 6:39:50 PM (ProgressReportBegin, WriteDecode): Started processing the 'Account Number' hierarchy.
6/4/2007 6:39:50 PM (ProgressReportCurrent, WriteDecode, MYSERVERNAME.MYCUBE.Account.Account Number): 0
6/4/2007 6:40:14 PM (ProgressReportCurrent, WriteDecode, MYSERVERNAME.MYCUBE.Account.Account Number): 1
6/4/2007 6:40:14 PM (ProgressReportEnd, WriteDecode): Finished processing the 'Account Number' hierarchy.
6/5/2007 8:54:31 AM (ProgressReportBegin, WriteDecode): Started processing the 'First Name' hierarchy.
6/5/2007 8:54:31 AM (ProgressReportCurrent, WriteDecode, MYSERVERNAME.MYCUBE.Account.First Name): 0
6/5/2007 8:54:32 AM (ProgressReportCurrent, WriteDecode, MYSERVERNAME.MYCUBE.Account.First Name): 1
6/5/2007 8:54:32 AM (ProgressReportEnd, WriteDecode): Finished processing the 'First Name' hierarchy.
Hi Greg,
I had some problems with SSAS 2005 getting stuck during processing. For me it was allways during cube processing (vs dimension processing). We had one suggestion from Microsoft, that actually sometimes worked. We were told to execute simple MDX query to SSAS to wake it up. Unfortunatelly that did not work consistently.
What worked for us is changing SSAS server parameter ThreadPool\Process\MaxThreads from default value of 64 to 150.
As in your case your dimension started around 9:00am (somebody in the office), there is very good chance that somebody submitted some query to server and wake it up.
More information about problem like mine is here:
http://blogs.msdn.com/psssql/archive/2007/01/16/processing-appears-to-stall-or-become-sluggish-on-multi-processor-machines-running-analysis-services-2005.aspx
Regards,
Vidas Matelis
|||I'll try that tip about submitting an MDX query and report back if that works. I had read Wayne's post but had completely forgotten about the "submit a simple MDX query" tip. Thanks Vidas.
As for raising the # of threads, I'll play with that, too. Since we've got MaxParallel=1 to prevent it from doing so much in parallel it runs out of memory (whether that's the best idea, I don't know) I certainly don't think it's short on threads. But I can easily check in perfmon.
|||The suggestion of "run an MDX query and that will wake up the processing" does seem to do the trick. One problem, though, is when your cube isn't processed, you can't run an MDX query to wake it up. The best solution I've found is to run a Clear Cache statement:
<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Adventure Works DW</DatabaseID>
</Object>
</ClearCache>
Processing cube from SSAS - OLE DB error
I have made a new dimension and setup the reference in dimension usage. Every time I process the cube i got the following message
Error 1 OLE DB error: OLE DB or ODBC error: Invalid column name 'PReference'.; 42S22. 0 0
The value in this column is 0,1 or 2. I have set the value in all rows to 1 and still get the same error.
I have tried to create a new column - updated value to 1 and refreshed the datasource and changed the dimension and dimension usage and I still got the same error processing the cube.
I have tried to use another integer field in dimension usage and this works fine.
Maybe it is something on the table - i have tried almost all but I can't solve this problem.
Leo Pedersen
The error message "Invalid column name 'PReference'" is saying that an invalid column name was specified in the SQL query that AS generated. Run Profiler on the AS server and capture the SQL query. It should point you to the problem.Processing AS 2005 dimension with Oracle data source
Hi everyone -
I have a problem with an AS 2005 Data Source View created with an Oracle 10g data source. The view creates fine, and the dimension also creates with no problems. I click "explore data" from the right-click menu on the dimension's source table and the table data is returned with no problem. However, when I process the dimension, I get an immediate error "Referenced account is locked out...". The Impersonation Information is the Oracle userid/password. I am not locked out of the Oracle database, so I can't figure this one out. Have found nothing about this error during my searches. Any help would be appreciated.
Thanks
In the Impersonation Information , pick 'Use the service account'.|||Thanks for the help. I always used "Use the service account" for SQL Server data sources, but figured it would need my Oracle login to connect to an Oracle source. How the service account knows my Oracle login is a mystery, but as long as it works, I won't complain....Processing a dimension using AMO does not work if the connection to the “DataSource” used is “SQ
Processing a dimension using AMO does not work if the connection to the “DataSource” used is “SQL Server Authentication”
amoDimension.Process(ProcessType.ProcessFull)
The following exception is returned:
- ex {"OLE DB error: OLE DB or ODBC error: Login failed for user ' MyUserId '.; 42000.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'MyDataBase', Name of ' MyDataBase'.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Products', Name of 'ProductDim' was being processed.
Errors in the OLAP storage engine: An error occurred while the 'Products' attribute of the 'ProductDim' dimension from the ' MyDataBase SQL SERVER' database was being processed.
"} System.Exception
The connection string to the “DataSource” is:
ConnectionString "Provider=SQLOLEDB.1;Data Source=MyServer\SQLSERVER2005;User ID=MyUserId;Initial Catalog= MyDataBase " String
ConnectionStringSecurity PasswordRemoved {1} Microsoft.AnalysisServices.ConnectionStringSecurity
Everything work properly is if I switch the connection type to “Windows Authentication”
Thanks,
Yones
Do you have a password in the connection string ? If you do, have you called dataSource.Update() or database.Update(UpdateOptions.ExpandFull) before the dimension.Process() ? Because the server will not return the passwords from connection strings to the client (AMO included). Instead, the server removes the password and sets the ConnectionStringSecurity property (of DataSource object) to PasswordRemoved. And the Update method will save the connection string with no password. The work-around is to avoid calling Update or to re-specify the password.
Adrian Dumitrascu
|||Thank you very much Adrian.
It seems like the problem is happening because of database.Update(UpdateOptions.ExpandFull) but I need to called this method because I am adding new attributes to some dimensions.
Could you please tell me how can I re-specify the password?
Thanks,
Yones|||> Could you please tell me how can I re-specify the password?
Set the DataSource.ConnectionString before the database.Update, specifying the password (and this is a problem because you don't want to put the password in clear text in the source code; is it acceptable to invoke UI to ask for the password ? or read it from a secure place). As a reminder, you need to worry about the password only if DataSource.ConnectionStringSecurity == PasswordRemoved.
Adrian Dumitrascu
|||I am not sure if the password is removed just after calling dataSource.Update() or database.Update(UpdateOptions.ExpandFull) because I just did the following two lines of code and when I checked the password, it was already removed.
m_amoServer.Connect(i_strDataSource)
m_amoDataBase = m_amoServer.Databases.Item(i_strDatabase)
The result:
m_amoDataBase.DataSources (0)
ConnectionString"Provider=SQLOLEDB.1;Data Source=MyServer\SQLSERVER2005;User ID=MyUserId;Initial Catalog= MyDataBase "String
ConnectionStringSecurityPasswordRemoved {1}
Thanks Again,
Yones
|||The server doesn't provide the password to AMO (for security - a server admin can setup the connection strings, while database admins can do other tasks without needing to know the passwords). The server keeps the password internally (so you can re-process objects), but it doesn't give it to clients. Because of this, the Update method would send back to the server an incomplete connection string, overwriting the existing full connection string.
Adrian Dumitrascu
Processing a dimension using AMO does not work if the connection to the “DataSource” used is
Processing a dimension using AMO does not work if the connection to the “DataSource” used is “SQL Server Authentication”
amoDimension.Process(ProcessType.ProcessFull)
The following exception is returned:
- ex {"OLE DB error: OLE DB or ODBC error: Login failed for user ' MyUserId '.; 42000.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'MyDataBase', Name of ' MyDataBase'.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Products', Name of 'ProductDim' was being processed.
Errors in the OLAP storage engine: An error occurred while the 'Products' attribute of the 'ProductDim' dimension from the ' MyDataBase SQL SERVER' database was being processed.
"} System.Exception
The connection string to the “DataSource” is:
ConnectionString "Provider=SQLOLEDB.1;Data Source=MyServer\SQLSERVER2005;User ID=MyUserId;Initial Catalog= MyDataBase " String
ConnectionStringSecurity PasswordRemoved {1} Microsoft.AnalysisServices.ConnectionStringSecurity
Everything work properly is if I switch the connection type to “Windows Authentication”
Thanks,
Yones
Do you have a password in the connection string ? If you do, have you called dataSource.Update() or database.Update(UpdateOptions.ExpandFull) before the dimension.Process() ? Because the server will not return the passwords from connection strings to the client (AMO included). Instead, the server removes the password and sets the ConnectionStringSecurity property (of DataSource object) to PasswordRemoved. And the Update method will save the connection string with no password. The work-around is to avoid calling Update or to re-specify the password.
Adrian Dumitrascu
|||Thank you very much Adrian.
It seems like the problem is happening because of database.Update(UpdateOptions.ExpandFull) but I need to called this method because I am adding new attributes to some dimensions.
Could you please tell me how can I re-specify the password?
Thanks,
Yones|||> Could you please tell me how can I re-specify the password?
Set the DataSource.ConnectionString before the database.Update, specifying the password (and this is a problem because you don't want to put the password in clear text in the source code; is it acceptable to invoke UI to ask for the password ? or read it from a secure place). As a reminder, you need to worry about the password only if DataSource.ConnectionStringSecurity == PasswordRemoved.
Adrian Dumitrascu
|||I am not sure if the password is removed just after calling dataSource.Update() or database.Update(UpdateOptions.ExpandFull) because I just did the following two lines of code and when I checked the password, it was already removed.
m_amoServer.Connect(i_strDataSource)
m_amoDataBase = m_amoServer.Databases.Item(i_strDatabase)
The result:
m_amoDataBase.DataSources (0)
ConnectionString"Provider=SQLOLEDB.1;Data Source=MyServer\SQLSERVER2005;User ID=MyUserId;Initial Catalog= MyDataBase "String
ConnectionStringSecurityPasswordRemoved {1}
Thanks Again,
Yones
|||The server doesn't provide the password to AMO (for security - a server admin can setup the connection strings, while database admins can do other tasks without needing to know the passwords). The server keeps the password internally (so you can re-process objects), but it doesn't give it to clients. Because of this, the Update method would send back to the server an incomplete connection string, overwriting the existing full connection string.
Adrian Dumitrascu
Processing a cube dimension
I am trying to process a time dimension on a cube and get the following error. Can some one please help. How can I fix this error?
Error 1 Errors related to feature availability and configuration: Exceeded the maximum number of partitions in a measure group. The installed server editions allows not more than 3 partitions and not more than 1 writeback partition. 0 0
That is the error if you try to use Standard Edition and partitioning.
Partitioning is an EE-only feature.
_-_-_ Dave
Friday, March 9, 2012
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:
>dimension you process using ProcessAdd command.
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.
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.
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.
>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" xmlns
<ID>DV_Test</ID>
<Name>DV_Test</Name>
<DataSourceID>DS_Test</DataSourceID>
<Schema>
<xs
<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'"
msprop
msprop:IsLogical="True"
msprop:TableType="View">
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs
</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=""PHABRK_CODE" + '#&@.' + "SHORT_DESC") 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" xmlns
<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>
<xs
<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" msprop
<xs:complexType>
<xs
<xs:element name="PHABRK_CODE" type="xs
<xs:element name="Column1" msdata:ReadOnly="true" msprop:ComputedColumnExpression=""PHABRK_CODE" + '#&@.' + "SHORT_DESC"" msprop:IsLogical="True" minOccurs="0">
<xs
<xs:restriction base="xs
<xs:maxLength value="255" />
</xs:restriction>
</xs
</xs:element>
<xs:element name="PARENT_CODE" type="xs
<xs:element name="customrollup" msprop
<xs
<xs:restriction base="xs
<xs:maxLength value="500" />
</xs:restriction>
</xs
</xs:element>
</xs
</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?
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:
>dimension you process using ProcessAdd command.
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.
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.
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.
>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" xmlns
<ID>DV_Test</ID>
<Name>DV_Test</Name>
<DataSourceID>DS_Test</DataSourceID>
<Schema>
<xs
<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'"
msprop
msprop:IsLogical="True"
msprop:TableType="View">
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs
</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=""PHABRK_CODE" + '#&@.' + "SHORT_DESC") 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" xmlns
<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>
<xs
<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" msprop
<xs:complexType>
<xs
<xs:element name="PHABRK_CODE" type="xs
<xs:element name="Column1" msdata:ReadOnly="true" msprop:ComputedColumnExpression=""PHABRK_CODE" + '#&@.' + "SHORT_DESC"" msprop:IsLogical="True" minOccurs="0">
<xs
<xs:restriction base="xs
<xs:maxLength value="255" />
</xs:restriction>
</xs
</xs:element>
<xs:element name="PARENT_CODE" type="xs
<xs:element name="customrollup" msprop
<xs
<xs:restriction base="xs
<xs:maxLength value="500" />
</xs:restriction>
</xs
</xs:element>
</xs
</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?
Subscribe to:
Posts (Atom)