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

Processing XML with defined namespaces

I'm trying use the SSIS XML Task to process some XML files that have namespaces and schemas defined . One of the things I am doing is trying to perform an XPATH query on the files. However, I'm unable to provide either a namespace manager or XsltContext to the XML Task and that causes the XML Task to not understand my XPATH queries.

For example, if my XML is:

<o:object id="12345" xmlns:o="urn:some-urn"/>

and my desired XPATH is "/o:object/@.id", I get the following error:

Error: 0xC002F304 at Query, XML Task: An error occurred with the following error message: "Namespace Manager or XsltContext needed. This query has a prefix, variable, or user-defined function.".

Of course, the XPATH "/object/@.id" returns nothing as expected.

Is there a way around this?

I'm having the same problem with .NET "System.Xml" have you found a solution to this?|||

I'm getting the same (Xml Task) error. I need my XPath to be /SOAP-ENV:Envelope/SOAP-ENV:Body.

The only way I could work around it was to write the XPath without using the namespace prefix like this:

/*[local-name() = 'Envelope']/*[local-name() = 'Body']

|||

A Barber wrote:

I'm having the same problem with .NET "System.Xml" have you found a solution to this?

what exactly is the problem that you're experiencing with system.xml?

Processing XML with defined namespaces

I'm trying use the SSIS XML Task to process some XML files that have namespaces and schemas defined . One of the things I am doing is trying to perform an XPATH query on the files. However, I'm unable to provide either a namespace manager or XsltContext to the XML Task and that causes the XML Task to not understand my XPATH queries.

For example, if my XML is:

<o:object id="12345" xmlns:o="urn:some-urn"/>

and my desired XPATH is "/o:object/@.id", I get the following error:

Error: 0xC002F304 at Query, XML Task: An error occurred with the following error message: "Namespace Manager or XsltContext needed. This query has a prefix, variable, or user-defined function.".

Of course, the XPATH "/object/@.id" returns nothing as expected.

Is there a way around this?

I'm having the same problem with .NET "System.Xml" have you found a solution to this?|||

I'm getting the same (Xml Task) error. I need my XPath to be /SOAP-ENV:Envelope/SOAP-ENV:Body.

The only way I could work around it was to write the XPath without using the namespace prefix like this:

/*[local-name() = 'Envelope']/*[local-name() = 'Body']

|||

A Barber wrote:

I'm having the same problem with .NET "System.Xml" have you found a solution to this?

what exactly is the problem that you're experiencing with system.xml?

Processing XML with defined namespaces

I'm trying use the SSIS XML Task to process some XML files that have namespaces and schemas defined . One of the things I am doing is trying to perform an XPATH query on the files. However, I'm unable to provide either a namespace manager or XsltContext to the XML Task and that causes the XML Task to not understand my XPATH queries.

For example, if my XML is:

<o:object id="12345" xmlns:o="urn:some-urn"/>

and my desired XPATH is "/o:object/@.id", I get the following error:

Error: 0xC002F304 at Query, XML Task: An error occurred with the following error message: "Namespace Manager or XsltContext needed. This query has a prefix, variable, or user-defined function.".

Of course, the XPATH "/object/@.id" returns nothing as expected.

Is there a way around this?

I'm having the same problem with .NET "System.Xml" have you found a solution to this?|||

I'm getting the same (Xml Task) error. I need my XPath to be /SOAP-ENV:Envelope/SOAP-ENV:Body.

The only way I could work around it was to write the XPath without using the namespace prefix like this:

/*[local-name() = 'Envelope']/*[local-name() = 'Body']

|||

A Barber wrote:

I'm having the same problem with .NET "System.Xml" have you found a solution to this?

what exactly is the problem that you're experiencing with system.xml?

Processing XML data in a SQL Server field

Has anyone cracked this? I want to process xml data which is contained within
a SQL server field. I'm using other fields in the table in the normal way.
The xml field actually contains numerous data values which I want to pass
into the the chart object.
So far there seems to be two options to me:
1) Export the entire row of the table to xml and then use a custom data
extension which knows about xml to read it. This route is described in Teos'
book.
2) Call out to a custom assembly and have it process the xml.
My views on the above are:
1) Seems clunky. Already have the data and now I need to export it to a file
and then read it back in?
2) Seems ok but how do I handle multiple items,, such as the data points?
Anyone have any other ideas before I wade in with my boots?!using a custom assembly works like a charm - We have entire XML docs stored
in blob fields and parse them on the fly to get the information we need form
them.

Processing XML data from a table

We have an XML column in a SQL Server 2005 table. Each row of this table contains one XML document.

I want to shred values from the XML documents and process these within a Data Flow. I want the Data Flow to execute once across a record set comprised of all of the XML documents.

I can shred the XML using a For-Each loop and XML Task. I'm kinda stuck on how I then get the data from variables into a Recordset or similar so that I can process this within single iteration of a Data Flow.

Or - is my approach incorrect? I seem to be building a verbose and clunky solution to this problem. I know I could accomplish the same in a pretty simple SQL statement using .value on the XML column... am I missing something? Is a SQL query just better suited to this problem?

Any help much appreciated.

James

" how I then get the data from variables into a Recordset" ... the script source component.

Processing User Activity Table

Hello,

I have an application that will be logging to a SQL Server 2000
database user user activity from several Windows 2003 terminal
servers. This information will be retrieved by monitoring the
Security logs of these servers (this part I know how to accomplish
already).

A table in the database, tblLogEntries, will contain the following
fields:
- ID = autoincrementing int
- LogTime = Date/Time the user activity was recorded in the security
log
- Username = User's login ID that the activity was recorded with
- Type = int, referencing a lookup table with the values of Logon,
Logoff, and possible other future items
- Server = The name of the server the activity was recorded on.

The only question I have is, can you offer a way to process the total
user login time during a given range using T-SQL.

For Example...

Given the table data:
ID LogTime Username Type Server
1 10-10-2003 8:30:00 Tom Logon SERVER-A
2 10-10-2003 8:45:00 Sarah Logon SERVER-A
3 10-10-2003 16:45:00 Tom Logoff SERVER-A
4 10-10-2003 17:00:00 Sarah Logoff SERVER-A
5 10-11-2003 8:30:00 Tom Logon SERVER-A
6 10-11-2003 8:45:00 Sarah Logon SERVER-A
7 10-11-2003 16:30:00 Sarah Logoff SERVER-A
8 10-11-2003 17:15:00 Tom Logoff SERVER-A

How would you receive the output:
User Logon Total Time for SERVER-A
Tom 17.0 hrs
Sarah 16.0 hrs

I know I can handle this type of processing on my ASP.NET front-end,
but I'm curious as to how easily it can be done by the database,
itself.

Thanks in advance for your assistance.Do:

SELECT UserName,
SUM( DATEDIFF( hour, Login, COALESCE( Logoff, Login ) ) )
FROM ( SELECT t1.Username, t1.LogTime,
( SELECT MIN( t2.LogTime )
FROM tbl t2
WHERE t2.Server = t1.Server
AND t2.UserName = t1.Username
AND t2.Type = 'LogOff'
AND t2.LogTime > t1.LogTime )
FROM tbl t1
WHERE t1.Type = 'Logon'
AND t1.Server = 'SERVER-A' ) D ( UserName, Login, Logoff )
GROUP BY UserName ;

--
- Anith
( Please reply to newsgroups only )|||Thanks for the reply.. the query looks good, but I'm having a little
trouble following it, and, as a result, cannot get it to work.

Would you mind explaining it a bit or point me to a reference for this
type of processing?

I'm not sure what that "D" operator is for, and I'll read up on the
COALESCE in BOL.

------------
http://members.tripod.com/kcourville0/

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||D is an alias used for a derived table used in the query; when you use a
subquery construct directly in the FROM clause of an SQL statement with an
alias, in t-SQL, it is called a derived table. Pl. refer to SQL Server Books
Online for syntax and more details on this construct.

The logic is simple:

1. Retrieve the list of users with their login times and the subsequent
logout time (this is achieved using a subquery with MIN function). You can
run the derived table by itself to further understand how it is evaluated.

2. With the data in the derived table, the outer query, get the time
difference between the login time and the logout times and then find the
total using the SUM function.

--
- Anith
( Please reply to newsgroups only )|||Cool... I'll have to learn more of derived tables.

Thanks again for your response.

------------
http://members.tripod.com/kcourville0/

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Processing Time Perfomance

First, a general question. What is a "reasonable" time to expect the
ProcessingTime to take. Now I know that is pretty vague and largely depends
on the grouping/filtering defined in the RDL.
Our problem. We have a several main reports that each embed 7-10 subreports
from a pool of 15 or so defined subreports. This was primarily designed this
way for the flexibility and ease of maintenance of selecting/moving around
the subreports in each main report.
I understand that a main report executing subreports cannot take advantage
of multi-threading the queries and instead run each sequentially, correct?
Even so, we have tuned the database very well and the data retrieval (from
execution log) is taking about 2 seconds for about 15 queries in tables with
millions of rows. Incidentally we are using a custom DPE.
Highlevel details of each subreport. I can provide the RDLs, if necessary:
-Each subreport is one page (8.5x11) long and contains a pie chart and bar
chart data region and a table data region.
-The dataset for the table, pie and bar are the same, but the table and pie
do an aditional grouping on a string value on an average of 10-100 rows.
-Two other data regions of about 10 rows exist, but do not do any grouping.
-One of the subreports does do filtering on a range of 10 to potentially
~5000 rows, but is slow in processing even with 10 rows.
-Last, in the table dataregion (which is never bigger than 15 rows after
grouping), an external assembly is called to do some minor formatting for
each row (font, navigation, etc.)
The problem is that the processing time is taking near a minute -- which is
unacceptable given our application is online and the business expectation is
to take about 5 seconds, no more than 10. Cached reports are super-fast, but
the reports are such that it is unlikely that a cache would be used.
So, what can we do to improve perfomance? It does not appear that moving
the subreports into one report will gain us much, even with mult-threaded
queries, as our database is tuned to the hilt already.
-We tried disabling the caching (thinking perhaps the time to write the
cache files was expensive), but no. By the way, does the time to write the
cache fall into "processing time"?
-We could potentially break the data in two (one for the pie and table and
one for the bar) and omit the grouping.
-We could also return two datasets for the subreport that is doing
filtering.
-Our ReportServer and SQLReportCatalog are on the same server (SQL Standard)
and the server is pretty beefy (2 proc 2GHz). I assume nothing in the
processing part is multi-threaded, so one one proc gets used, right? Are
there any config settings to improve performance?
I want to avoid changing around the RDL to remove all filtering and grouping
if it will only shave a few seconds of the minute anyhow.
Thoughts, suggestions requested. Thanks, DavidMy suggestion is to run each subreport individually and see what time you
get for them. See if a particular subreport is giving you the problem. Since
your data is not large for each subreport (ignoring the one that could have
5,000 since you said having smaller data is still a problem) it seems to me
that you should be doing substantially better here than you are.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"David Swanson" <DavidSwansonSDG@.newsgroup.nospam> wrote in message
news:4511C346-8482-42D1-9192-1BE85B9089B5@.microsoft.com...
> First, a general question. What is a "reasonable" time to expect the
> ProcessingTime to take. Now I know that is pretty vague and largely
depends
> on the grouping/filtering defined in the RDL.
> Our problem. We have a several main reports that each embed 7-10
subreports
> from a pool of 15 or so defined subreports. This was primarily designed
this
> way for the flexibility and ease of maintenance of selecting/moving around
> the subreports in each main report.
> I understand that a main report executing subreports cannot take advantage
> of multi-threading the queries and instead run each sequentially, correct?
> Even so, we have tuned the database very well and the data retrieval (from
> execution log) is taking about 2 seconds for about 15 queries in tables
with
> millions of rows. Incidentally we are using a custom DPE.
> Highlevel details of each subreport. I can provide the RDLs, if
necessary:
> -Each subreport is one page (8.5x11) long and contains a pie chart and bar
> chart data region and a table data region.
> -The dataset for the table, pie and bar are the same, but the table and
pie
> do an aditional grouping on a string value on an average of 10-100 rows.
> -Two other data regions of about 10 rows exist, but do not do any
grouping.
> -One of the subreports does do filtering on a range of 10 to potentially
> ~5000 rows, but is slow in processing even with 10 rows.
> -Last, in the table dataregion (which is never bigger than 15 rows after
> grouping), an external assembly is called to do some minor formatting for
> each row (font, navigation, etc.)
> The problem is that the processing time is taking near a minute -- which
is
> unacceptable given our application is online and the business expectation
is
> to take about 5 seconds, no more than 10. Cached reports are super-fast,
but
> the reports are such that it is unlikely that a cache would be used.
> So, what can we do to improve perfomance? It does not appear that moving
> the subreports into one report will gain us much, even with mult-threaded
> queries, as our database is tuned to the hilt already.
> -We tried disabling the caching (thinking perhaps the time to write the
> cache files was expensive), but no. By the way, does the time to write
the
> cache fall into "processing time"?
> -We could potentially break the data in two (one for the pie and table and
> one for the bar) and omit the grouping.
> -We could also return two datasets for the subreport that is doing
> filtering.
> -Our ReportServer and SQLReportCatalog are on the same server (SQL
Standard)
> and the server is pretty beefy (2 proc 2GHz). I assume nothing in the
> processing part is multi-threaded, so one one proc gets used, right? Are
> there any config settings to improve performance?
> I want to avoid changing around the RDL to remove all filtering and
grouping
> if it will only shave a few seconds of the minute anyhow.
> Thoughts, suggestions requested. Thanks, David

processing time issue

Hi all,

I have a quick question regarding the processing time.

I created view in db for the count purposes with huge fact table. ( I'm using dimension table which

joins the 1 fact and 1 dimension to count the key for the fast data retrieval)

If I write the query for the count in management studio and it gives a results within 5 sec.

But, if I add this view in DSV and process the cubes than it takes around 50 minutes.

This causes due to the Fact table? or what else?

I don't know what to check and please give me some comments.

Thanks in advance.

Have you run a profiler trace against the server. You could probably trace either SQL or SSAS to capture the exact SQL statement that is being executed. If this view is being used as a fact table SSAS will do a full scan of it and do a look up for each row to each of the associated dimensions. Doing a trace against SSAS might give you some more hints on what is going on. There are also a number of SSAS specific counters in Performance Monitor that might give some insight into what is going on.|||

Hi Darren,

Thanks for your good tips.

As you mentioned above, the view looks up for each row of associated dimension (fact table). In the Fact, total number of rows is around 153,000,000 and it makes cube processing very slow now.

So to improve better performance and faster processing what would you recommend in this situation? I only need the count of store number not that huge fact table.

Please give me some comments.

Thanks.

|||

There would be a couple of possible approaches to improving the processing performance.

One would be to partition the fact table so that you only process recent records and not the full 153 million.

Another might be to look at doing incremental processing. If you can keep track of which records are new since you last processed, you can just load those.

Finally you could possibly do a "group by" in your view to reduce the granularity, but often reducing the granularity reduces the flexibilty of your design and would generally be a last resort.

Processing Time

Hi,

I am verifying my reports processing time. I get the information from the Reporting Service DB - [ExecutionLogs] table. I have the following information:

[TimeEnd] – time that reports generation ends.

[TimeStart] - time that reports generation starts.

[TimeDataRetrieval] - amount of time spent running the data sources.

[TimeProcessing] - time spent processing the report.

[TimeRendering] - time spent generating the output format.

If this information is correct the following statement should be true:

([TimeEnd] - [TimeStart]) = ([TimeDataRetrieval] + [TimeProcessing] + [TimeRendering])

But it isn't, ([TimeEnd] - [TimeStart]) is always bigger then ([TimeDataRetrieval] + [TimeProcessing] + [TimeRendering]).

Why does this happen?

Regards,

Rodrigo

That's interesting. I've never looked to check that. Are you seeing a big difference? how much is it a % of total execution time?

I suspect that TimeDataRetrieval, TimeProcessing, TimeRendering are each recorded by the subsystem resposible for each of those. I would expect there to be a small amount of overhead for the engine to do it's stuff and transition between the various phases between start and end.

|||

I have the following results for different reports:

End-Start

(ms)

Sum

(ms)

%

5030 3717 73,8966203 17437 12009 68,870792 5047 3374 66,851595 4733 3142 66,3849567 23170 15348 66,2408287 4780 3134 65,5648536 15750 10129 64,3111111 13017 7849 60,2980718 11153 6548 58,7106608 13170 7660 58,1624905 8830 5119 57,9728199

The first column is ([TimeEnd] - [TimeStart])

The second column is ([TimeDataRetrieval] + [TimeProcessing] + [TimeRendering])

How can i find the cause of this difference ?

Regards,

Rodrigo

|||

The difference is consistently large, too large. From your observations i.e. looking at the screen with a stopwatch, which is the "more correct" value?

|||

Do the reports have multiple datasets?

If multiple datasets are present and transactions are not used, SSRS will execute dataset queries in parallel. However, since there is only one data retrieval value in the execution log, it will pick the time of the longest data retrieval thread.

In addition, time data retrieval + processing + rendering does not cover all the time between the start and the end time of the request. Generally however, the other operations performed should just be small fraction compared to ([TimeDataRetrieval] + [TimeProcessing] + [TimeRendering]).

-- Robert

Processing the TEXT datatype with TSQL

Hi;

I have a table with a TEXT datatype.
Its a comment field.

Right now the users who put in singlequotes are killing the web front
end.

The programmer responsible is fixing this issue but it might be a few
weeks until we get the patch.

I would like to write a trigger that whenever this field is updated it
will scan the text for single quotes ( and hard returns \r ) and
extract them.

I found some nice string functions in HELP.

Will these string functions work with the TEXT datatype in a TSQL
script/trigger?

Thanks in advance

SteveI handle quotes with the REPLACE function. All languages that I work with
has it.

Two single quotes in a row signify an escape sequence from the normal
interpretation of the single quote character. When two single quotes appear
together, they are interpreted by SQL as one literal single quote. All we
need do, then, is replace any single quote with two single quotes in strings
that we want interpreted literally by SQL.

This won't work on a Text datatype, however it does work on varchars and
stuff. Check your max len() on that field and see if it actually is using
more than the capacity of other datatypes and see about changing it to
varchar or something. This t-sql replaces one quote with two and would save
your web person endless hours of javascript'ing validation code!!

Select REPLACE(testColumn, char(39), char(39) + char(39)) as texta from
myTable

After all, quotes are valid characters too!!!

Good luck!

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz

"Steve" <stevesusenet@.yahoo.com> wrote in message
news:6f8cb8c9.0311140657.59346a15@.posting.google.c om...
> Hi;
> I have a table with a TEXT datatype.
> Its a comment field.
> Right now the users who put in singlequotes are killing the web front
> end.
> The programmer responsible is fixing this issue but it might be a few
> weeks until we get the patch.
> I would like to write a trigger that whenever this field is updated it
> will scan the text for single quotes ( and hard returns \r ) and
> extract them.
> I found some nice string functions in HELP.
> Will these string functions work with the TEXT datatype in a TSQL
> script/trigger?
> Thanks in advance
> Steve

Processing the resultset of another proc from a proc

Is it possible to retrieve the resultset of a stored procedure from another procedure in sql server 2000.
Basically I am calling proc2 from the inside of proc1.
proc2 returns 2 resultsets. I want to process these two resultsets
from within proc1.

If its possible , please provide sample code.

thanks in advance,
Alok.Yes, with temporary tables for instance. But it will be easier if you can convert proc2 to two UDF table functions. Your code will be much clearer and simpler to maintain.

Processing SSAS2005 from DTS

Hi,

I have a requirement to process SSAS2005 cubes from DTS. Anyone know how to do this or does anyone have any recommendations?

I've done a search on this forum and came up with teh following which was no help at all unfortunately: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=198374&SiteID=1

-Jamie

Hi Jamie,

Will the ASCMD command-line utility (available under SQL Server Analysis Services Samples) serve your purpose (invoked within a DTS "Execute Process" task, of course)?

http://msdn2.microsoft.com/en-us/ms365187.aspx

>>

SQL Server 2005 Books Online

Readme For Ascmd Command-line Utility Sample

...

Processing a Partition from a Third-Party Tool

A database administrator must process partitions and dimensions as part of a nightly extract, transform, and load (ETL) process. The ETL tool is not a SQL Server tool, and so the database administrator cannot use SQL Server Agent’s built-in support of XMLA scripts and cannot run a SQL Server 2005 Integration Services (SSIS) package. The database administrator wants an automated solution that utilizes the third-party tool. This means a command-line utility to run the XMLA script that can be called from the third-party tool. The database administrator downloads and compiles the ascmd command-line utility sample. The administrator can then use the ascmd command-line utility to process partitions and dimensions.

>>

|||

Deepak,

I'm certainly hoping so. Thanks for the reply.

-Jamie

|||

It works a treat. Thanks again!

XMLA - loving it!

cheers

-Jamie

|||

Did you download ASCMD.exe from this link or was it already on installed for you in the location specified in the ReadMe?

I've installed the developer edition on my machine and can't find the file in the location specified.

|||

We have not yet upgraded to 2005. Is there a similar tool for MSAS2000?

Thanks

|||http://msdn2.microsoft.com/en-us/library/ms133828.aspx|||

Momo:

ascmd was added to the SQL Server samples in SP1, you can download the latest samples from here http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

|||Tried that. Contains sample code but not the actual exe?|||That's right, ascmd was only released as source code.|||

How can I get the XMLA script to process a cube in SSAS 2000. If I can get this I would think I could make a SOAP call from my program to execute the XMLA. This would be great for me. I would have everything in one program.

|||

Unfortunately I am pretty sure AS 2000 does not support the XMLA process command.

The best work around I can think of in order to process AS 2000 objects using SOAP would be to write your own web service that used DSO to process the cube. You would need to have the web service running under a domain account that was a member of the OLAP Administrators group to get this working.

|||

Hi,

I tried to install the SSAS Samples but the SqlServerSamples.msi file available in the following link is corrupted and I can′t find another place to obtain it.

http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

|||I just tried the download and it appears to work OK. Give the download another try, maybe there were some transient network issues.

Processing SSAS2005 from DTS

Hi,

I have a requirement to process SSAS2005 cubes from DTS. Anyone know how to do this or does anyone have any recommendations?

I've done a search on this forum and came up with teh following which was no help at all unfortunately: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=198374&SiteID=1

-Jamie

Hi Jamie,

Will the ASCMD command-line utility (available under SQL Server Analysis Services Samples) serve your purpose (invoked within a DTS "Execute Process" task, of course)?

http://msdn2.microsoft.com/en-us/ms365187.aspx

>>

SQL Server 2005 Books Online

Readme For Ascmd Command-line Utility Sample

...

Processing a Partition from a Third-Party Tool

A database administrator must process partitions and dimensions as part of a nightly extract, transform, and load (ETL) process. The ETL tool is not a SQL Server tool, and so the database administrator cannot use SQL Server Agent’s built-in support of XMLA scripts and cannot run a SQL Server 2005 Integration Services (SSIS) package. The database administrator wants an automated solution that utilizes the third-party tool. This means a command-line utility to run the XMLA script that can be called from the third-party tool. The database administrator downloads and compiles the ascmd command-line utility sample. The administrator can then use the ascmd command-line utility to process partitions and dimensions.

>>

|||

Deepak,

I'm certainly hoping so. Thanks for the reply.

-Jamie

|||

It works a treat. Thanks again!

XMLA - loving it!

cheers

-Jamie

|||

Did you download ASCMD.exe from this link or was it already on installed for you in the location specified in the ReadMe?

I've installed the developer edition on my machine and can't find the file in the location specified.

|||

We have not yet upgraded to 2005. Is there a similar tool for MSAS2000?

Thanks

|||http://msdn2.microsoft.com/en-us/library/ms133828.aspx|||

Momo:

ascmd was added to the SQL Server samples in SP1, you can download the latest samples from here http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

|||Tried that. Contains sample code but not the actual exe?|||That's right, ascmd was only released as source code.|||

How can I get the XMLA script to process a cube in SSAS 2000. If I can get this I would think I could make a SOAP call from my program to execute the XMLA. This would be great for me. I would have everything in one program.

|||

Unfortunately I am pretty sure AS 2000 does not support the XMLA process command.

The best work around I can think of in order to process AS 2000 objects using SOAP would be to write your own web service that used DSO to process the cube. You would need to have the web service running under a domain account that was a member of the OLAP Administrators group to get this working.

|||

Hi,

I tried to install the SSAS Samples but the SqlServerSamples.msi file available in the following link is corrupted and I can′t find another place to obtain it.

http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

|||I just tried the download and it appears to work OK. Give the download another try, maybe there were some transient network issues.

Processing SSAS 2005 Database Hangs

Hi,

After making changes to our development SSAS 2005 Database, I try to reporcess but it just seems to hang. Rebooting the server seems to fix the issue because once the server comes back up, I'm able to reprocess and see the progress immediately.

This server has SP2 installed.

Has anyone else run across this?

Thanks,

Brian

Maybe the was an lock on the database by you or an other user.

If there is an lock on the database this is the intended behavior.

In the analysis services samples there is an activity viewer where you could check for locks and sessions on the server.

Best Regards, HANNES

|||Thank you for the reply. Can you explain more on where to find the activity viewer that displays locks and instances?|||

It is a C# Sample Code Program you have to manually setup with SQL Server 2005 Samples.

By default the samples install to C:\Program Files\Microsoft SQL Server\90\Samples with the special location of \Analysis Services\Administrator\ActivityViewer for the analysis activitiy viewer.

HANNES

processing speed, optimizations for AMD Opteron

Does anyone know if Analysis Services has binaries optimized for AMD Opteron? The reason I ask is that I am seeing a dramatic performance difference between the 2 systems:

System: Dual Xeon Irwingdale 3.4 GHz, Chipset E7525, 4GB Ram
OS: Windows XP Pro SP2
Fact Rows: 20,000,000
Aggregations: 1300
Processing Time: 3 Hrs

System: Dual AMD Opteron 246 (2.0 GHz), AMD Chipset 8000 (MB Tyan S2885). 4 GB RAM
OS: Windows 2003 Enterprise (SP1)
Fact Rows: 20,000,000 (same data set as 1st case)
Aggregations: 1300
Processing Time: 6-7 Hrs

Both test environments have the:
Same RAM speed
Same model HDDs

Is this difference due to any lack of AMD-specific optimizations in SSAS binaries? or some other reason?

Also is there any way to reduce the thread priority on SSAS binary during optimization? It seems to almost completely hose the machine (windows 2003 ent.) during processing. When attempted to change processing priority through task manager I got access denier error (logged in as admin)Did you test under the same OS and 100% equial another options?

Such difference as you have acounted seems to be too screwy.|||

Vladimir Chtepa wrote:

Did you test under the same OS and 100% equial another options?

Such difference as you have acounted seems to be too screwy.

The operating systems are different - Windows XP SP2 vs. Windows 2003 SP1.

SSAS options are all same.

Also when its processing on Windows 2003 SP1/AMD Opteron test environment it is much harder to use any other applications (open windows, task manager, etc) than on the Windows XP. The AMD/Windows2003 gets very hosed/hung up when processing at 100% CPU.

On other hand the IntelXeon/Win XP case it still responsive even when processing faster and at 100% CPU for hours.

Processing SCDs in bulk

I read another post. I'm hoping I'm just doing something wrong, and that the SSIS team wouldn't have done this:

I read in a batch of records that should cause changes in an SCD in some cases. The table is empty originally. In the wizard, its handling changing attibutes ,fixed and historical.

3 records in ( all with the same business key) = 3 records out?

In this case, it should only produce 1 record.

Are you kidding me? There must be a way to set the batch size to 1, right?

Are they all going down the "new" output?

I see your point. Not all of them are new are they and I assume they all valid to be in the pipeline. It'd be nice if the LOOKUP cahce (for that is what it is under the covers) could be updated as a row comes in - a dynamic cache if you will. I haven't much (any in fact) experience with the SCD component.

It could be that this is a hole in the product. Can you post a repro?

-Jamie

|||

For better or worse, this is by design. The data in the pipeline does not update the lookup table when the lookup is performed. Our data pipeline is buffered not single row so the initial row doesn't make it to the destination before the next row is compared and the SCD doesn't have a dynamic cache. If your data is like this then you would need to aggregate it for your initial insert case and then run it through again for your update cases.

Thanks,

Matt

|||thanks for responding. I found a work around by having a for each loop container that holds the dataflow component, and then passing through the set that way in some fashion, its just more complicated.|||

HI, I am very interested in this. Can you tell me what have been your solution? Something like doing inserts first (1st pass) and thn processing updates in another pass.

I need to imlement something like this in my project and it is the only way I found I could do it. If your solution is better that what I just described, can you share it please?

Thank you very much,

Ccote

|||

I put the dataflow inside a for each loop container.

In my case, I can identify ordered subsets of the original set, which I use as my pass criteria

In my case, we're processing policy transactions

e.g.

Policy# Transaction#

ABC 1

ABC 2

XYZ 1

XYZ 2

I want to process all the 1s first, then all the 2s, etc. so that if any SCD in the transaction occurs, it will be reflected.

For each transaction # , I make a pass through the dataflow, so I send through sets, and force the SCD to work. The beginning on my dataflow has a stored procedure call. I pass in the parameter of the transaction # I want it to process.

My SCD still isn't working, but its not related to the above.

hope that helps. good luck.

Processing SCDs in bulk

I read another post. I'm hoping I'm just doing something wrong, and that the SSIS team wouldn't have done this:

I read in a batch of records that should cause changes in an SCD in some cases. The table is empty originally. In the wizard, its handling changing attibutes ,fixed and historical.

3 records in ( all with the same business key) = 3 records out?

In this case, it should only produce 1 record.

Are you kidding me? There must be a way to set the batch size to 1, right?

Are they all going down the "new" output?

I see your point. Not all of them are new are they and I assume they all valid to be in the pipeline. It'd be nice if the LOOKUP cahce (for that is what it is under the covers) could be updated as a row comes in - a dynamic cache if you will. I haven't much (any in fact) experience with the SCD component.

It could be that this is a hole in the product. Can you post a repro?

-Jamie

|||

For better or worse, this is by design. The data in the pipeline does not update the lookup table when the lookup is performed. Our data pipeline is buffered not single row so the initial row doesn't make it to the destination before the next row is compared and the SCD doesn't have a dynamic cache. If your data is like this then you would need to aggregate it for your initial insert case and then run it through again for your update cases.

Thanks,

Matt

|||thanks for responding. I found a work around by having a for each loop container that holds the dataflow component, and then passing through the set that way in some fashion, its just more complicated.|||

HI, I am very interested in this. Can you tell me what have been your solution? Something like doing inserts first (1st pass) and thn processing updates in another pass.

I need to imlement something like this in my project and it is the only way I found I could do it. If your solution is better that what I just described, can you share it please?

Thank you very much,

Ccote

|||

I put the dataflow inside a for each loop container.

In my case, I can identify ordered subsets of the original set, which I use as my pass criteria

In my case, we're processing policy transactions

e.g.

Policy# Transaction#

ABC 1

ABC 2

XYZ 1

XYZ 2

I want to process all the 1s first, then all the 2s, etc. so that if any SCD in the transaction occurs, it will be reflected.

For each transaction # , I make a pass through the dataflow, so I send through sets, and force the SCD to work. The beginning on my dataflow has a stored procedure call. I pass in the parameter of the transaction # I want it to process.

My SCD still isn't working, but its not related to the above.

hope that helps. good luck.

Processing responsibilities - RS services vs. repository

Hi - Can someone please explain the split of processing that occurs (i.e.
what processing occurs where) in terms of between the Reporting Services
server itself vs. on the repository server (if different)? Specifically, we
see that there are many scheduled jobs in the SQL Agent on the repository
server, which potentially look related to scheduled/subscription jobs in RS,
but not sure. We'd like to understand this split of work from a workload
planning point of view.
thanksSomeone at MSFT will add more detail to this or correct me, but essentially
what I think happens is that when you schedule a job like a subscription, SQL
Server Agent adds an event to a table at the appointed times. [Look at the
job step details and you will see that they are all running a stored
procedure called AddEvent]. The table is constantly being polled by the
Report Server Windows Service. When there is an event that has been added,
Report Server then sees it and gets the job done, I think by making the
appropriate calls to the Report Server web service. The Report Server web
service and the Report Server Windows service are two separate things.
The Report Server obviously has the responsibility of performing the job,
and this might involve sending a query to the catalog to get the relevant
information. So really SQL Server Agent just adds an event to a table so that
the Windows service sees it.
Take a look also at the performance objects RS Web Service and RS Windows
Service in System Monitor or Performance Monitor.
Cheers
Charles Kangai, MCT, MCDBA
"ISGADMIN" wrote:
> Hi - Can someone please explain the split of processing that occurs (i.e.
> what processing occurs where) in terms of between the Reporting Services
> server itself vs. on the repository server (if different)? Specifically, we
> see that there are many scheduled jobs in the SQL Agent on the repository
> server, which potentially look related to scheduled/subscription jobs in RS,
> but not sure. We'd like to understand this split of work from a workload
> planning point of view.
> thanks|||Hi Charles thanks for the info. Do you know if the repository is also used
during other non-subscription operations like normal report rendering?
Clearly there must be an initial lookup in order to get report params,
security, etc. and anything else stored in the repository, but from a
capacity planning point of view I am interested to know how to plan for the
server holding the repository as a function of the load on my RS server
itself.
regards
"Charles Kangai" wrote:
> Someone at MSFT will add more detail to this or correct me, but essentially
> what I think happens is that when you schedule a job like a subscription, SQL
> Server Agent adds an event to a table at the appointed times. [Look at the
> job step details and you will see that they are all running a stored
> procedure called AddEvent]. The table is constantly being polled by the
> Report Server Windows Service. When there is an event that has been added,
> Report Server then sees it and gets the job done, I think by making the
> appropriate calls to the Report Server web service. The Report Server web
> service and the Report Server Windows service are two separate things.
> The Report Server obviously has the responsibility of performing the job,
> and this might involve sending a query to the catalog to get the relevant
> information. So really SQL Server Agent just adds an event to a table so that
> the Windows service sees it.
> Take a look also at the performance objects RS Web Service and RS Windows
> Service in System Monitor or Performance Monitor.
> Cheers
> Charles Kangai, MCT, MCDBA
> "ISGADMIN" wrote:
> > Hi - Can someone please explain the split of processing that occurs (i.e.
> > what processing occurs where) in terms of between the Reporting Services
> > server itself vs. on the repository server (if different)? Specifically, we
> > see that there are many scheduled jobs in the SQL Agent on the repository
> > server, which potentially look related to scheduled/subscription jobs in RS,
> > but not sure. We'd like to understand this split of work from a workload
> > planning point of view.
> >
> > thanks|||The ReportServer catalog is certainly queried during normal operations like
rendering, because that is where the report definitions are stored. Once you
deploy your reports or support files, they are stored in the Report Server
catalog. The Report Server catalog is Report Server database. Open your
ReportServer database using Enterprise Manager and take a look at some of the
tables. You will see your reports in the Catalog table. Whenever reports run
the reports also get temporarily cached in unrendered format in the databases
(there is another database used called ReportServerTempDB).
Check this information out in Books Online. It is all there.
cheers
Charles Kangai, MCT, MCDBA
"ISGADMIN" wrote:
> Hi Charles thanks for the info. Do you know if the repository is also used
> during other non-subscription operations like normal report rendering?
> Clearly there must be an initial lookup in order to get report params,
> security, etc. and anything else stored in the repository, but from a
> capacity planning point of view I am interested to know how to plan for the
> server holding the repository as a function of the load on my RS server
> itself.
> regards
> "Charles Kangai" wrote:
> > Someone at MSFT will add more detail to this or correct me, but essentially
> > what I think happens is that when you schedule a job like a subscription, SQL
> > Server Agent adds an event to a table at the appointed times. [Look at the
> > job step details and you will see that they are all running a stored
> > procedure called AddEvent]. The table is constantly being polled by the
> > Report Server Windows Service. When there is an event that has been added,
> > Report Server then sees it and gets the job done, I think by making the
> > appropriate calls to the Report Server web service. The Report Server web
> > service and the Report Server Windows service are two separate things.
> >
> > The Report Server obviously has the responsibility of performing the job,
> > and this might involve sending a query to the catalog to get the relevant
> > information. So really SQL Server Agent just adds an event to a table so that
> > the Windows service sees it.
> >
> > Take a look also at the performance objects RS Web Service and RS Windows
> > Service in System Monitor or Performance Monitor.
> >
> > Cheers
> >
> > Charles Kangai, MCT, MCDBA
> >
> > "ISGADMIN" wrote:
> >
> > > Hi - Can someone please explain the split of processing that occurs (i.e.
> > > what processing occurs where) in terms of between the Reporting Services
> > > server itself vs. on the repository server (if different)? Specifically, we
> > > see that there are many scheduled jobs in the SQL Agent on the repository
> > > server, which potentially look related to scheduled/subscription jobs in RS,
> > > but not sure. We'd like to understand this split of work from a workload
> > > planning point of view.
> > >
> > > thanks

Processing report

Hello,
In my MS report I have 4 textbox parameter and One dropdown parameter
two of the textboxes and the dropdown has queried parameter. All of them
have default parameter.
The issue I am facing is that When ever I click on the preview Report on the
dev machine, My report starts to do "Processing Report" with the "View
Rreport" button disabled. The processing report keeps on going although the
query runs fine in QA in 2-3 minutes.
I want the preview report should start processing only when I click "View
Report" so that I can drill down to real problem.
I have tried removing one of the default parameter so that it waits for
parameter but it does not helps..
Any suggestions will be highly appreciated...
Cheers,
siajYou said: "All of them have default parameter."
If all parameters have a valid default value, the report will run
immediately. This behavior cannot be changed in report manager. You could
remove the default value e.g. from the last report parameter which will
prevent running the report immediately.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"siaj" <siaj@.discussions.microsoft.com> wrote in message
news:4AB4CCCF-4485-4380-9918-57F6DFF80B10@.microsoft.com...
> Hello,
> In my MS report I have 4 textbox parameter and One dropdown parameter
> two of the textboxes and the dropdown has queried parameter. All of them
> have default parameter.
> The issue I am facing is that When ever I click on the preview Report on
> the
> dev machine, My report starts to do "Processing Report" with the "View
> Rreport" button disabled. The processing report keeps on going although
> the
> query runs fine in QA in 2-3 minutes.
> I want the preview report should start processing only when I click "View
> Report" so that I can drill down to real problem.
> I have tried removing one of the default parameter so that it waits for
> parameter but it does not helps..
> Any suggestions will be highly appreciated...
> Cheers,
> siaj
>