Ho to make data source web method execute once per a report?

My report works with XMLDP data provider and obtains source data as a

result of a web method call. This memthod returns all data necessary

for the report. Thus, all of about 20 report datasets query the same

web method with the same parameters and then select appropriate data

with different ElementPath expressions. It's so inefficient and

time-consuming! I cant have respective web method for every dataset.

How can I configure SSRS to call web method only once while the report

is executing? Or how can I leverage the performance at all?I seem to have founed the solution. I've configured report datasource to execute in single transaction and extra marked the source web method with [WebMethod(CashDuration=%TIME)] attribute. Hence, the report datasets call the web method one after another and the method is actually executed only once if %TIME value is more than report processing time. That's what I need!

Ho to make data source web method execute once per a report?

My report works with XMLDP data provider and obtains source data as a

result of a web method call. This memthod returns all data necessary

for the report. Thus, all of about 20 report datasets query the same

web method with the same parameters and then select appropriate data

with different ElementPath expressions. It's so inefficient and

time-consuming! I cant have respective web method for every dataset.

How can I configure SSRS to call web method only once while the report

is executing? Or how can I leverage the performance at all?I seem to have founed the solution. I've configured report datasource to execute in single transaction and extra marked the source web method with [WebMethod(CashDuration=%TIME)] attribute. Hence, the report datasets call the web method one after another and the method is actually executed only once if %TIME value is more than report processing time. That's what I need!

Ho can I deploy my whole site with the SQL data base attached.

Help me in deploying a web development project with the SQL data base attached to it.

And it get auto configured...

You can connect the master database on your server and you can restore the database using TSQL using RESTORE DATABASE using File & File Group

See more details on books online regarding Restore Database..

|||

Depending upon the Host providers security issues, you may not be allowed to simply 'Restore' a database.

However, the Database Publishing Wizard was created for just this type of situation.

Database Publishing Wizard
http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

High usage of RAM on SQL2k

Hi

I have an sql server with very high usage. The web which is connected to the sql server has approx. 1000 unique visitors every time.
The database is optimized and the site goes really fast now.

But a problem we've had since the beginning is that SQL Server constantly uses 1.8 GB of RAM. Never less, never more.

Do you have any idea on how to decrease the usage of RAM. I am sure that this high usage is necessary.check your logs,if they are more truncate them,
may be it helps you.|||But arent the log files truncated during the backup?|||Logs?

run sp_configure and tell me what the results are for min server memory.|||min server memory (MB) 0 2147483647 0 0

High CPU utilization on Merge Replication with SQL 2005 Mobile

I have a question for anyone who mas some tips/pointers for optimizing SQL merge replication publications.

The front end web server is running IIS 6.0 on Windows 2003 x86 Server Standard (Server A). The back end database server is running SQL 2000 Standard on Windows 2003 x86 Standard (Server B). The merge replication clients connect via HTTPS over the Internet from a custom C#.NET 2005 application using SQL 2005 Mobile running on Windows Mobile 5.0 (Client).

The publication itself has several filters on it. The entry point uses the user's Windows username to start the filter. Based on the user, it then filters the records in multiple tables. There are 68 articles and 44 filter statements. The filters extend multiple layers deep, in other words they are not all filtering off the HOST_NAME() variable, some tables filter from records in tables that filter from the HOST_NAME() variable. The publication is set to minimize data sent to the clients, and considers a subscription out of date if it has not synced in the last 4 days. All the rowguids are indexed as well.

There are approximately 35 clients actively using the application at any given time. On average, a client will initiate a merge replication 3-4 times per hour from 8am-5pm. Generally, a sync will take between 10 seconds and 2 minutes to complete, with most of them being around 30 seconds on average.

When a client starts a sync, there is a spike to about 50% on the server's CPU graph. If multiple clients attempt to sync at the same time the CPU utilization can be pushed to 100% for extended periods (more than 30 seconds).

I recently completed a project to increase the bandwidth available to the clients, and plan to reduce the number of filters significantly (although this will obviously increase the amount of data going to the clients and the storage needs on the individual devices). I also plan on changing the setting to not minimize the amount of data sent to the clients.

Having said all that, does anyone have any information about how to further optimize merge publications to mobile clients? The next publication will be on SQL 2005 x64 Standard if I can solve the issues in the text environment. I would like to enhance the publication as much as possible to make the end user experience better than it currently is.

Thanks!

You're talking about CPU usage at the publisher, correct?

Can you double check that all columns involved in the merge join filters are indexed as well? If the columns are not indexed, this leads to table scans during syncs which can result in high CPU usage.

Are you also getting conflicts? There's a performance issue (which will be fixed in SP1) that can slow things down due to missing indexes on some conflict tables, but this shouldn't be an issue unless you're getting hundreds and hundreds of conflicts.

ALso, how "deep" are your filters? Do the merge join filters have 1 to many relationships, or many to many (see the @.join_unique_key parameter). The more levels deep you are, or any level that contains a @.join_unique_key = 0, can negatively affect performance.

Regardless, you can always run profiler at the publisher and trace a single subscriber to see which procs are consuming the most time. You can start with RPC:completed or SP:completed, and just grab the duration. You'll quickly see which procs are the problematic one. From there, you can then enable SP:StatmentEnded and enable ExecutionPlan to see exactly what statement and why it's slow.

|||

Indexes were definetly a piece of the puzzle. The @.join_unique_key was also in play, so thanks for putting me on to that one. For anyone else who is using Merge Replication with SQL Mobile, there are 2 very useful articles:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_replmergepartitioned.asp

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

High CPU utilization on Merge Replication with SQL 2005 Mobile

I have a question for anyone who mas some tips/pointers for optimizing SQL merge replication publications.

The front end web server is running IIS 6.0 on Windows 2003 x86 Server Standard (Server A). The back end database server is running SQL 2000 Standard on Windows 2003 x86 Standard (Server B). The merge replication clients connect via HTTPS over the Internet from a custom C#.NET 2005 application using SQL 2005 Mobile running on Windows Mobile 5.0 (Client).

The publication itself has several filters on it. The entry point uses the user's Windows username to start the filter. Based on the user, it then filters the records in multiple tables. There are 68 articles and 44 filter statements. The filters extend multiple layers deep, in other words they are not all filtering off the HOST_NAME() variable, some tables filter from records in tables that filter from the HOST_NAME() variable. The publication is set to minimize data sent to the clients, and considers a subscription out of date if it has not synced in the last 4 days. All the rowguids are indexed as well.

There are approximately 35 clients actively using the application at any given time. On average, a client will initiate a merge replication 3-4 times per hour from 8am-5pm. Generally, a sync will take between 10 seconds and 2 minutes to complete, with most of them being around 30 seconds on average.

When a client starts a sync, there is a spike to about 50% on the server's CPU graph. If multiple clients attempt to sync at the same time the CPU utilization can be pushed to 100% for extended periods (more than 30 seconds).

I recently completed a project to increase the bandwidth available to the clients, and plan to reduce the number of filters significantly (although this will obviously increase the amount of data going to the clients and the storage needs on the individual devices). I also plan on changing the setting to not minimize the amount of data sent to the clients.

Having said all that, does anyone have any information about how to further optimize merge publications to mobile clients? The next publication will be on SQL 2005 x64 Standard if I can solve the issues in the text environment. I would like to enhance the publication as much as possible to make the end user experience better than it currently is.

Thanks!

You're talking about CPU usage at the publisher, correct?

Can you double check that all columns involved in the merge join filters are indexed as well? If the columns are not indexed, this leads to table scans during syncs which can result in high CPU usage.

Are you also getting conflicts? There's a performance issue (which will be fixed in SP1) that can slow things down due to missing indexes on some conflict tables, but this shouldn't be an issue unless you're getting hundreds and hundreds of conflicts.

ALso, how "deep" are your filters? Do the merge join filters have 1 to many relationships, or many to many (see the @.join_unique_key parameter). The more levels deep you are, or any level that contains a @.join_unique_key = 0, can negatively affect performance.

Regardless, you can always run profiler at the publisher and trace a single subscriber to see which procs are consuming the most time. You can start with RPC:completed or SP:completed, and just grab the duration. You'll quickly see which procs are the problematic one. From there, you can then enable SP:StatmentEnded and enable ExecutionPlan to see exactly what statement and why it's slow.

|||

Indexes were definetly a piece of the puzzle. The @.join_unique_key was also in play, so thanks for putting me on to that one. For anyone else who is using Merge Replication with SQL Mobile, there are 2 very useful articles:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_replmergepartitioned.asp

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

high Availability cubes: During Cube metadata updates + ProClarity

Hi,

This is a kind of solution design issue.

Scenario: We have a web page that displays information from a Oracle data warehouse. We collect the required information in the page and have used AMO to create cubes and other related objects from this information. The source for the cubes in SSAS 2005 would point to Oracle. This is modelled as a web service and used within the web page. Once the Cube gets created in SSAS, we model ProClarity graphs on them.

The problem: The users can invoke the AMO web service from the web page, to add a new MeasureGroup to an existing cube. Once this is done the Cube becomes non-browsable. because we are adding a new MeasureGroup and thus modifying the cube metadata.

In detail, If the new MeasureGroup that is added, uses dimensions that are already cube-dimensions then- only the newly added MeasureGroup will be UnProcessed and the exsiting MeasureGroups will be processed. (cube is UnProcessed state).

otherwise, if the new MeasureGroup that is added, adds a new cube-dimension to the cube then- all the MeasureGroups will be in the UnProcessed state including the newly added MeasureGroup.

Now, until the user invokes a 'process' request, for the cube that is modified and the processing is complete, that paticular cube is not accesible. All the Proclarity graphs that are linked to that cube, show error messages!

How can we keep the Proclarity graphs / users pointed to a old copy of the cube, at least until the newly modified cube is processed and online?

If not, are there any other approaches to get high availability for the cubes or for the ProClarity Graphs?

Can we create a offline cube that can be used temporarily? if so how to dynamically switch between the offline cube and the online cube?

Regards

If you do all the AMO operations including creating and processing the cube within transaction, then the previous version of the cube will be available until transaction commits. After transaction commited, the new version of the cube will be immediatelly avaiable.|||

Hi Mosha,

Thanks for the tip.

Since this operation is used in a J2EE web site using a AMO web service.

If I include the processing task with it then it would take a huge time for large cubes.

Currently we are using the 'Ascmd' to do the processing. This is scheduled using a scheduler.

We planned to somehow call Ascmd Asynchronously from the web page and intimate users using an email after the processing completes.

can we include the processing also in the previous transaction while using Ascmd?

Apart from this, any other mechanism that we can use like Raid/mirroring, Failover clustering. etc?

Or temporary local cubes?

:-) Just a wild shot: Probably the next version of SSAS needs to include something similary "shadow-copy-cube".

Thanks and Regards

|||

Just a wild shot: Probably the next version of SSAS needs to include something similary "shadow-copy-cube".

No need to wait for the next version. SSAS2005 already supports Sync functionality, where you can build and process cube on one server and then sync it to another server.

|||

You might consider using AS 2005's "synchronize database" capability. This would require (I think) two servers. The first would be the "modify and process" server (call it ServerA) while the second would be the "presentation" server (call it ServerB).

In your application, you would have the users always browsing the cube(s) on ServerB. As they make changes to the design of the cube (adding measure groups, cube dimensions, etc.), you'd direct those changes to the cube(s) on ServerA. You could then asynchronously use ascmd to reprocess part or all of a modified cube on ServerA (while they continue to use the cube on ServerB). Once the processing is complete, you could execute an XMLA script on ServerB, requesting that the database on ServerB be sychronized from ServerA. The nice thing about this is that the synchronization occurs within a transaction, so the cube on ServerB is still usable while the sychronization occurs and the updated version of the cube is then available to users immediately once it is complete.

Take a look at the following link in BOL for the XMLA element for the synchronize command:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/xmla9/html/9401323c-feff-409a-a9da-94aee47e0563.htm

HTH,

Dave Fackler

|||

Hi,

Wow!

Thanks a lot for that.

I am going to implement the "Sync Database", This will solve my problem!

although I would take some time to complete this , I will let you know when I start.

Regards

high Availability cubes: During Cube metadata updates + ProClarity

Hi,

This is a kind of solution design issue.

Scenario: We have a web page that displays information from a Oracle data warehouse. We collect the required information in the page and have used AMO to create cubes and other related objects from this information. The source for the cubes in SSAS 2005 would point to Oracle. This is modelled as a web service and used within the web page. Once the Cube gets created in SSAS, we model ProClarity graphs on them.

The problem: The users can invoke the AMO web service from the web page, to add a new MeasureGroup to an existing cube. Once this is done the Cube becomes non-browsable. because we are adding a new MeasureGroup and thus modifying the cube metadata.

In detail, If the new MeasureGroup that is added, uses dimensions that are already cube-dimensions then- only the newly added MeasureGroup will be UnProcessed and the exsiting MeasureGroups will be processed. (cube is UnProcessed state).

otherwise, if the new MeasureGroup that is added, adds a new cube-dimension to the cube then- all the MeasureGroups will be in the UnProcessed state including the newly added MeasureGroup.

Now, until the user invokes a 'process' request, for the cube that is modified and the processing is complete, that paticular cube is not accesible. All the Proclarity graphs that are linked to that cube, show error messages!

How can we keep the Proclarity graphs / users pointed to a old copy of the cube, at least until the newly modified cube is processed and online?

If not, are there any other approaches to get high availability for the cubes or for the ProClarity Graphs?

Can we create a offline cube that can be used temporarily? if so how to dynamically switch between the offline cube and the online cube?

Regards

If you do all the AMO operations including creating and processing the cube within transaction, then the previous version of the cube will be available until transaction commits. After transaction commited, the new version of the cube will be immediatelly avaiable.|||

Hi Mosha,

Thanks for the tip.

Since this operation is used in a J2EE web site using a AMO web service.

If I include the processing task with it then it would take a huge time for large cubes.

Currently we are using the 'Ascmd' to do the processing. This is scheduled using a scheduler.

We planned to somehow call Ascmd Asynchronously from the web page and intimate users using an email after the processing completes.

can we include the processing also in the previous transaction while using Ascmd?

Apart from this, any other mechanism that we can use like Raid/mirroring, Failover clustering. etc?

Or temporary local cubes?

:-) Just a wild shot: Probably the next version of SSAS needs to include something similary "shadow-copy-cube".

Thanks and Regards

|||

Just a wild shot: Probably the next version of SSAS needs to include something similary "shadow-copy-cube".

No need to wait for the next version. SSAS2005 already supports Sync functionality, where you can build and process cube on one server and then sync it to another server.

|||

You might consider using AS 2005's "synchronize database" capability. This would require (I think) two servers. The first would be the "modify and process" server (call it ServerA) while the second would be the "presentation" server (call it ServerB).

In your application, you would have the users always browsing the cube(s) on ServerB. As they make changes to the design of the cube (adding measure groups, cube dimensions, etc.), you'd direct those changes to the cube(s) on ServerA. You could then asynchronously use ascmd to reprocess part or all of a modified cube on ServerA (while they continue to use the cube on ServerB). Once the processing is complete, you could execute an XMLA script on ServerB, requesting that the database on ServerB be sychronized from ServerA. The nice thing about this is that the synchronization occurs within a transaction, so the cube on ServerB is still usable while the sychronization occurs and the updated version of the cube is then available to users immediately once it is complete.

Take a look at the following link in BOL for the XMLA element for the synchronize command:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/xmla9/html/9401323c-feff-409a-a9da-94aee47e0563.htm

HTH,

Dave Fackler

|||

Hi,

Wow!

Thanks a lot for that.

I am going to implement the "Sync Database", This will solve my problem!

although I would take some time to complete this , I will let you know when I start.

Regards

Hiding without grouping

hi,
I'm using a matrix to try and display web traffic. For the rows I
can drill down by date. For the columns I am trying to make them be
able to expand based on categories that I define. For example, I would
like to be able to expand/collapse all columns that have to do with
traffic from google. However, I can't seem to check the toggle box. I'm
assuming it's because the columns are not grouped by the item that
toggles them, the way dates are grouped by year. The item that toggles
their visibility is a static text box. The closest I can get is to
toggle the visibility of the column names. Any suggestions?Nevermind, I was just using the wrong table. turns out you can hide
entire columns in the tabular table based on arbitrary toggle items.
You just have to do it in the property window on the side, rather than
right clicking. Has anyone noticed that there's extra unsupported chart
types there? What's a polar graph?|||"What's a polar graph?"
A circular plot showing amplitudes as a function of angle.
http://www.dplot.com/polar.htm

Hiding URL from User

I have a web application written in ASP, not ASP.Net. We set up a page with an iframe that we populate the SSRS reports into. The url of the report is visible through a right click/properties. Is there a way to either disable the right click or hide the url?

you can disable the right click by adding oncontextmenu="return false" in the body tag:

<body oncontextmenu="return false">

Hiding SOME parameters in the parameter toolbar

I am using the ReportViewer.dll in a ASP.NET web application. The report
being displayed takes 2 parameters. One of these is programmatically set. I
would like the user to select values only for the other parameter. So when
displaying the report within the control, can I just show the second
parameter on the toolbar and hide the first one?
The Parameters toolbar property in the ReportViewer control corresponds to
the 'Parameters' URL access parameter, which hides/displays the entire
parameter toolbar
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_urlaccess_959e.asp). Umm, is that bad news?OK I solved the problem...You just type in a blank string for the Prompt
value...
"Aparna" wrote:
> I am using the ReportViewer.dll in a ASP.NET web application. The report
> being displayed takes 2 parameters. One of these is programmatically set. I
> would like the user to select values only for the other parameter. So when
> displaying the report within the control, can I just show the second
> parameter on the toolbar and hide the first one?
> The Parameters toolbar property in the ReportViewer control corresponds to
> the 'Parameters' URL access parameter, which hides/displays the entire
> parameter toolbar
> (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_urlaccess_959e.asp). Umm, is that bad news?|||On Wed, 26 Jan 2005 12:21:03 -0800, Aparna
<Aparna@.discussions.microsoft.com> wrote:
>OK I solved the problem...You just type in a blank string for the Prompt
>value...
>
How exactly does one "type in" a blank string? :)))
>"Aparna" wrote:
>> I am using the ReportViewer.dll in a ASP.NET web application. The report
>> being displayed takes 2 parameters. One of these is programmatically set. I
>> would like the user to select values only for the other parameter. So when
>> displaying the report within the control, can I just show the second
>> parameter on the toolbar and hide the first one?
>> The Parameters toolbar property in the ReportViewer control corresponds to
>> the 'Parameters' URL access parameter, which hides/displays the entire
>> parameter toolbar
>> (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_urlaccess_959e.asp). Umm, is that bad news?|||haha...Not quite sure...However, it turns out my excitement was in vain...If
the prompt value is not specified, then the paratmeter becomes Read-only..So,
if you programatically want to assign values to it, you are out of luck! The
only way to set the value of that parameter is via the parameter property
page. So my orginal problem still stands...SOMEBODY HELLLP...
"Usenet User" wrote:
> On Wed, 26 Jan 2005 12:21:03 -0800, Aparna
> <Aparna@.discussions.microsoft.com> wrote:
> >OK I solved the problem...You just type in a blank string for the Prompt
> >value...
> >
> How exactly does one "type in" a blank string? :)))
>
> >"Aparna" wrote:
> >
> >> I am using the ReportViewer.dll in a ASP.NET web application. The report
> >> being displayed takes 2 parameters. One of these is programmatically set. I
> >> would like the user to select values only for the other parameter. So when
> >> displaying the report within the control, can I just show the second
> >> parameter on the toolbar and hide the first one?
> >>
> >> The Parameters toolbar property in the ReportViewer control corresponds to
> >> the 'Parameters' URL access parameter, which hides/displays the entire
> >> parameter toolbar
> >> (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_urlaccess_959e.asp). Umm, is that bad news?
>