Another Deployment Question

The deployment of reports and datasources via the Deploy function in vs2005
works fine. However we often have to deploy to clients where there is a
firewall with no ports open. We do have Remote Desktop Connection access
and need to deploy reports and datasources by transfering files. (restoring
the entire ReportService database is not granular enough).
We can export and import .rdl files but we cannot find a way to do the same
with Datasources. I know that a datasource .rds file is a brief xml file
that looks like this...
<?xml version="1.0" encoding="utf-8"?>
<RptDataSource xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Name>DataSource1</Name>
<DataSourceID>6aac99b7-1658-479f-be09-fbcb874dbe04</DataSourceID>
<ConnectionProperties>
<Extension>SQL</Extension>
<ConnectString>Data Source=(local);Initial
Catalog=MyApplication</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</RptDataSource>
So it's basically just a connection string with a GUID in it.
How can we deploy Datasources via file transfer? Or is there a better way
in which to deploy reports and datasources to other ReportServer instances
that are behind firewalls.
Thanks,
GaryHello Gary,
As for the ReportServer, currently it can only import report through file
uploading (by provide the report's rdl file). DataSource doesn't support
such importing. And the "rds" file is only used in development environment
(the BI studio or Visual Studio). However, we can use the "rs.exe" utility
to execute script to deploy SSRS report or datasource. The following BOL
reference has provided description and sample on scripting SSRS report and
datasource...
#Scripting Deployment and Administrative Tasks
http://msdn2.microsoft.com/en-us/library/ms159720.aspx
#Script Samples (Reporting Services)
http://msdn2.microsoft.com/en-us/library/ms160854.aspx
The following script block is the one used for creating datasource in the
sample script
=========Public Sub CreateSampleDataSource(name As String, extension As String,
connectionString As String)
'Define the data source definition.
Dim definition As New DataSourceDefinition()
definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
definition.ConnectString = connectionString
definition.Enabled = True
definition.EnabledSpecified = True
definition.Extension = extension
definition.ImpersonateUser = False
definition.ImpersonateUserSpecified = True
'Use the default prompt string.
definition.Prompt = Nothing
definition.WindowsCredentials = False
Try
rs.CreateDataSource(name, parentPath, False, definition, Nothing)
Console.WriteLine("Data source {0} created successfully", name)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub
===========
Hope this helps.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Steven
Now that I understand thet the "rds" is only used in the dev environment it
looks like export/import is the way to go when we are limited to file
transfer.
--
Regards,
Gary Blakely
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:tHvKqMmqGHA.5740@.TK2MSFTNGXA01.phx.gbl...
> Hello Gary,
> As for the ReportServer, currently it can only import report through file
> uploading (by provide the report's rdl file). DataSource doesn't support
> such importing. And the "rds" file is only used in development environment
> (the BI studio or Visual Studio). However, we can use the "rs.exe"
> utility
> to execute script to deploy SSRS report or datasource. The following BOL
> reference has provided description and sample on scripting SSRS report and
> datasource...
>
> #Scripting Deployment and Administrative Tasks
> http://msdn2.microsoft.com/en-us/library/ms159720.aspx
> #Script Samples (Reporting Services)
> http://msdn2.microsoft.com/en-us/library/ms160854.aspx
> The following script block is the one used for creating datasource in the
> sample script
> =========> Public Sub CreateSampleDataSource(name As String, extension As String,
> connectionString As String)
> 'Define the data source definition.
> Dim definition As New DataSourceDefinition()
> definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
> definition.ConnectString = connectionString
> definition.Enabled = True
> definition.EnabledSpecified = True
> definition.Extension = extension
> definition.ImpersonateUser = False
> definition.ImpersonateUserSpecified = True
> 'Use the default prompt string.
> definition.Prompt = Nothing
> definition.WindowsCredentials = False
> Try
> rs.CreateDataSource(name, parentPath, False, definition, Nothing)
> Console.WriteLine("Data source {0} created successfully", name)
> Catch e As Exception
> Console.WriteLine(e.Message)
> End Try
>
> End Sub
> ===========> Hope this helps.
> Sincerely,
> Steven Cheng
> Microsoft MSDN Online Support Lead
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Thanks for your response Gary,
If you have any further question later, please feel free to post in the
newsgroup.
Have a good day!
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.

Analyzing a Query in SQL Server

I have to duplicate a bunch of reports that were produced by a VB6 app. Now I'm using C#, ASP.net and Crystal Reports via VS2003. Each executes a stored procedure in SQL Server (before and now).

For each report I have a Stored Procedure and a View. The View's SQL code is identical to the Stored Procedure except that the two input parameters (startDate and endDate) are removed because Views don't allow parameters.

Some of the reports work perfectly right off the bat. But others are timing out. My initial test of the timing out is to just display the View. If it fails then I know that the the report engine will fail too.

So now I'm trying out the SQL Query Analyzer tool to execute the code in one of the Views. It's now at 29 minutes and still going - at least it hasn't timed out!

My question is this: Is there a way I could examine what's going on with the query to see why it's taking so long?

Robert WernerHave a look at the query execution plan in the query analyzer, do you see any bottlenecks there ? During execution you might encounter a deadlock. looking in the appropiate node in SQL Server Enterprise Manager (Current Activity) you might be able to find the blocking process.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Jens,

Thank you for your suggestion. I've never used that Execution Plan before so just tried it out on one of the queries that's timing out. I took the liberty of taking a screen capture and posting it here:

http://pocketpollster.com/downloads/public/WBMCST11_Execution_Plan.jpg

Does this communicate anything to you?

Robert Werner
Vancouver, BC|||THe information which is sensitive while moving over the individual objects is missing for us, so its hard to tell. This is the estimated execution plan not the actual executed one, but as a rule of thumb you should have a look on the highest cost in the plan as well as actions like table scans. They are often costly if made for huge tables but are on the opposite cheaper then using indexes in smaller tables. There are some other thing you could check like setting the STATISTICS_IO to on or the SHOWPLAN to make the execution plan shippable to other peers with more information.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Analysis Svc security problem

Hi All
Whether I set roles or non at all to my cubes ANY user can
connect and read data from them via excel.
Is this a known issue? Anyone else having this problem or
am I doing something wrong?
Thanks
JHi,
The Users can access the Cubes by any means, only if they have enough access
rights to the Cube.
Check whether you have added "Everyone" in the Cube Roles mistakenly or whet
her "Everyone" has been added to the System Administrator group or OLAP Admi
nistrator group.
Cheers,
Sanka
-- J wrote: --
Hi All
Whether I set roles or non at all to my cubes ANY user can
connect and read data from them via excel.
Is this a known issue? Anyone else having this problem or
am I doing something wrong?
Thanks
J

Analysis Services with Oracle

Does anyone know if using Analysis Services with Oralce via OLEDB adversly affects performance compared to using it with a SQL Server Database?I use Oracle 8.1.7 with Analysis Services. I can't really compare between the performance with SQL Server, but performance is OK with Oracle. We use views as the sources, so those needed to be tuned.
The processing of a cube gives you the exact SQL statement it performs, so you can tune these. These will eat up considerable space in the Oracle temp tablespace, because of the sorts.
Another factor is of course the network speed between the database server and the Analysis Services server.

BTW: Because we encountered 'Data Source errors' we now use (after some hours searching for a solution) the Oracle provided OLE DB provider for defining the cubes and dimensions and the 'Microsoft OLE DB provider for Oracle' for processing those. Don't ask me what the difference exactly is, but this way it works fine for me.

Good luck, Edwin

Analysis Services Role created via AMO has unknown problem.

Hi,

This is a wierd problem so I hope I can explain the situation clearly

I'm creating a single role via AMO for an Analysis Services database and adding domain users to the role just fine. When our 3rd party reporting tool tries to create a report an error gets generated and they bubble back the error shown below.

Now here is the wierd part...if I simply view the Role in SQL Server Managment Management Studio and click OK the problem goes away. If I click Cancel when viewing the Role the problem persists. Obviously when I click "OK" in the Role dialog some sort of save routine is going on and fixes whatever problem is present.

I'm creating the role using standard MSDN examples and everything looks in order. Any suggestions on how I can better track down what the problem is?

System.Exception: Exception has been thrown by the target of an invocation. at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) Thanks

Could you post the code you use to generate the role? I'll compare it to some code we have that is working. My first guess is there is probably something not quite right in the permissions.

Bryan

|||

To see what is different (between the role create with AMO and the role fixed by the dialog), you can do this:

- re-create the role with AMO

- connect with 'SQL Management Studio' and script the Create or Alter operation of the role (right click on the role and there should be the 'Script' option)

- run the role dialog, press 'OK'

- script again and compare with the previous script

Adrian Dumitrascu

Analysis Services Cube Browser utility opening extremely slow

I have a relatively large cube that when I try to open it up via the cube browser utility in SQL Server Management Studio, it takes approximately 25 minutes for the cube browser utility to open up. If I want to query the database via MDX, the MDX writing utility (right-click on the database->New Query->MDX) comes back really fast (within a second).

What does the browser utility do differently that requires it to take so long versus the query utility?

Is there a way to speed it up?

Thanks

Hi there:

This is happening because the default query bein generated by the browser is taking a huge time to execute. Try using a simpler (read:less calculation intensive) measure as your cube default and watch the browser fly.

Hope that helps.

Cheers.

Suranjan Som

Senior BI Architect

Information Managment Group

|||

Unfortunately, this does not solve the problem. I turned on SQL Server Profiler and noticed that for each measure group in the cube (this particular cube has 12) it has to read in each partition. 6 of these measure groups have 48 combined partitions. Why does it need to read in each measure group? Unless there is a setting that I am missing, this is going to be an issue for anybody that has a large cube (billion plus rows) with multiple partitions.

Analysis Services connexion problem using AMO via ASP.NET 2

Hi,

I've got a strange problem.
I'm developing an ASP.NET application connecting to an Analysis
Services 2005 database.

On my developpement PC I can run the web site virtually and my
connexion is ok. I can see all the databases running under my server.
BUT, when I deploy the site on the server, even if the connexion is a
success, I can't see any of my databases.

Concretly, the code looks like :

Dim oServeur As Microsoft.AnalysisServices.Server
oServeur = New Microsoft.AnalysisServices.Server
oServeur.Connect("ServerName") ' I do not type "(local)"

This part of the code is ok both on developpement and server side.

But If I add this :

My.Response.Write(oServeur.DataBases.Count)

The response is "2" on application developpement side and
"0" on the server side ...

Can someone help me please ?

This very much looks like the permissions problem. Check the security under which your ASP application is running. Check the security on the virtual directory for your application.

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

Analysis Services connexion problem using AMO via ASP.NET 2

Hi,

I've got a strange problem.
I'm developing an ASP.NET application connecting to an Analysis
Services 2005 database.

On my developpement PC I can run the web site virtually and my
connexion is ok. I can see all the databases running under my server.
BUT, when I deploy the site on the server, even if the connexion is a
success, I can't see any of my databases.

Concretly, the code looks like :

Dim oServeur As Microsoft.AnalysisServices.Server
oServeur = New Microsoft.AnalysisServices.Server
oServeur.Connect("ServerName") ' I do not type "(local)"

This part of the code is ok both on developpement and server side.

But If I add this :

My.Response.Write(oServeur.DataBases.Count)

The response is "2" on application developpement side and
"0" on the server side ...

Can someone help me please ?

This very much looks like the permissions problem. Check the security under which your ASP application is running. Check the security on the virtual directory for your application.

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

Analysis Services connection using AMO

Hi,

I have AS2005 running on a development machine, and i am trying to connect to it via my application running on another machine.

The code is using the Analysis Mangement Object (AMO) API.

Currently, my application code only tries to connect and disconnect from the server.

Server svr = new Server();

svr.Connect(connectionString); < Exception thrown here

svr.Disconnect();

The connectionString simply contains the remote server's name. I have also tried different connection strings but it has not helped.

I have also enabled remote connections on AS, and opened the default port number on the server's firewall.

Every time it tries to connect the exception thrown is:

"Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host."

Can anybody help me with this problem?

I'm not sure if this will help, but I was getting a similar error with

the BI Development Studio. In that case it was a security

issue. Basically, becasue the machine was on a different domain,

I had to create a user on that machine with the same username /

password as the one that I log into my machine with. Have you

tried connecting in any other way?|||

Is the "other machine" an AS2K5 instance?

_-_-_ Dave