Showing posts with label ssas. Show all posts
Showing posts with label ssas. Show all posts

Sunday, February 19, 2012

Analyzing MDX query evaluation

Are there any tools or tricks for looking at how an MDX query is evaluated in SSAS 2005? Something that indicates how aggregations are being used, for example - sort of like a SHOWPLAN for MDX.
As you might know, AS 2005 MDX query execution events are now captured in SQL Server Profiler. What's not as clear from the docs (that I've come across, anyway) is the aggregation usage info available via SQL Profiler. This was well explained at the recent SQL PASS Conference, in Session 108: "Understanding Analysis Services 2005 Aggregations from Every Angle", by Dave Wickert and Len Wyatt:

>>
SQL Profiler

Will tell you when:
an aggregation is used for a query
the partition data is used for a query

It won’t tell you why it decides to use or not use a particular aggregation

§>>

|||Deepak,
Thanks, that sounds very promising. Do you know where I can find more information about using Profiler for this purpose? That SQL PASS session doesn't appear to be downloadable (that I can find, at least), and the documentation isn't much help here (as you point out).
Thanks,
Kevin
|||Hi Kevin,

If you're a PASS member, then the presentation slides should be downloadable, or you can order the physical media with MP3 sound. Now maybe Dave and Len are making their presentation (or equivalent info) available elsewhere - don't know!

http://www.softconference.com/250927
>>

CO1: Compilation CD-ROM set with full desktop capture - MP3 audio including live demos and PowerPoints (for attendees of the 2005 PASS Summit) CDROM (Attendees only) $ 195.00 Add to shopping cart CO2: Compilation CD-ROM set with full desktop capture - MP3 audio including live demos and PowerPoints (for members of PASS who did not attend the 2005 Summit) CDRom (Members only) $ 395.00 Add to shopping cart CO3: Compilation CD-ROM set with full desktop capture - MP3 audio including live demos and PowerPoints (for non-attendees, non-members of PASS) CDRom $ 595.00


>>

Thursday, February 16, 2012

Analysis Services service won't start

Can anyone point me in the right direction?

I haven't used SSAS in a few months and I went to start the service in SQL Server Configuration Manager and I get an error pop-up message:

The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details.

However the event log has no useful info - just says the service started, then it stopped - no error related info. There are no logs generated in ..\MSSQL.1\. Every other service starts - database, SSIS, SSRS, Agent, Browser and FullText.

I'm running Sql Server 2005 (with SP1) on Win XP Pro (SP2). Any help appreciated!

Do you see anything in the System Event log?

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

|||

There were a few Information type events that said that Analysis Services started and it stopped, but no error or warning events.

I got impatient and uninstalled SSAS and re-installed and everything works now.

Thursday, February 9, 2012

Analysis Services and Proclarity

We have following environment setup: SQL Server 2005, SSAS 2005 and Proclarity working together to provide some business intelligence.

I have login problems with Proclarity Dashboar server.

What happens is after user logs in into the dashboard server and leaves the browser (IE6) idle for some period of time, the user gets logged out (due to session time out). Once logged out, the user can't log back in as themselves. Now if the user logins with some other userid and logs out properly, then he/she can log back in again as themselves.

Dashboard uses the windows userid/password for logins.

Anybodys help would be greatly appriciated.

Thanks

Giri

Looks like you are using basic authentication for the ProClarity Dashboard server? You have the option to use windows integrated security!

My advice is to contact http://www.proclarity.com/services/support.asp about this issue. You will need a valid account for this. As a customer you will also have access to information on two ProClarity communities. I have seen a lot of discussions and recommendations of how to set up windows integrated security(Active Directory) with ProClarity Dashboard Server 6.2

It is impossible to write all the details you will have to know here.

HTH

Thomas Ivarsson

Analysis Services Access Database

Hello,

I am trying to generate a cube in SSAS 2005 with an Access database as the data source. I can create the Data Source and successfully test the connection. Here is my connection string:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\DB\LSAM.mdb

I can also successfully create my Data Source View and I can return data by right clicking on any of my table objects and selecting 'Explore Data'

However, when I try to generate my cube, I receive the following error message:

OLE DB error: OLE DB or ODBC error: 'E:\DB\LSAM.mdb' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.; 3044.

Any assistance would be GREATLY appreciated.

Thank You,

Joshua Shull.

Try and import your Access database into SQL Server and build your cube from there.

Later you can try and change datasource to point to the access file and process it .

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

Analysis Services 2005 OLE DB Provider Error

I try to open a SSAS Cube but I get a OLE DB error message below.

The same cube I can browse with the SQL Server Business Intelligence Development Studio.

Error message:

Microsoft OLE DB Provider for Analysis Services 2005 Microsoft OLE DB Provider for Analysis Services 2005 reported:

Errors in the OLE DB provider. Could not connect to the redirector. Ensure that the SQLBrowser service is running on the '<MY Server Name>' server. Errors in the OLE DB provider. An error occurred while named instance information was being retrieved from the SQLBrowser service on the '<MY Server Name>' server.

The SQL Browser is used when you are attempting to communicate with a named instance. What you are seeing is a known problem, fixed in SP1, where the SQL Browser needs communication with a domain controller to validate your access rights. This happens to me if I disconnect my laptop from the network and try an connect to a named instance on it. I've also seen it if the SQL Browser is running under a service account that isn't a machine administrator -- and third I've seen this if you are running under XP SP2 and the firewall is stopping your connectivity to the SQL Browser.

You have a few options: 1) make it the default instance -- that works without any problems, 2) always make sure a DC is available, 3) make sure the XP firewall is disabled on the SQL Browser port# (and the various instance port#s), 4) attempt to connect specifically to the port # for the instance (which bypasses the redirector) -- try using <server>:<port#> syntax for the server name.

Hope that helps.

_-_-_ Dave

|||

Thanks Dave,

I have check the options:

1. I have installed sql2005 with the default instance <MSSQLSERVER> that is not the problem

2. I'm connected to the DC

3. I have disabled the firewall, and that has changed the error message!!

The new message is:

Error reported by OLAP server.

Additional information:

Microsoft? OLE DB Provider for Analysis Services reported:

Cannot connect to the server '<MYSERVER>. The server is either not started or too busy.

But I can work with the SQL Server Business Intelligence Development Studio without any problem!!

|||

I have found the solution for the reported error message.

After reinstalling the "Workstation components" I can connect to the cubes in SSAS for a client Olap tool.

Thanks Dave for your support.

Regards,

Peter

|||I got the same error with the same symptoms but resolved the OLAP connection problem simply by reinstalling the OLAP provider for SQL Server 2005. The file name is SQLServer2005_ASOLEDB9.msi and it can be found on the microsoft downloads site.|||

Hi Dave,

Some questions for you about your options:

1. How do you make SQL Server 2005 the default instance if SQL Server 2000 is also installed on the same machine.
SQL Server 2000 can be removed if this is an option.
2. How can you confirm that the DC is available besides when you log into the server which is on the domain?
3. The sql server is on a windows 2003 server. The AD server is in another state and is accessed via a VPN. Could this be a problem?
4. How do you connect to the port # since when I use the Data Connection Wizard and use 10.1.1.1:1384 it comes back with the error:
"Transport Layer error."
If I use the instance name syntax -> 10.1.1.1\InstanceName - it works but then it uses the redirector which causes the main error.

So a couple of your solutions have some problems for me... is there any other way around them?

Thanks,
Andrew.

|||

>>1. How do you make SQL Server 2005 the default instance if SQL Server 2000 is also installed on the same machine.

There could be only a single default instance on the machine. And Analysis Services 2000 does not support named instances, so for AS 2005 be default instance you need to remove AS2000.


>>2. How can you confirm that the DC is available besides when you log into the server which is on the domain?

Not sure about this. You can try posting on windows networing newsgroup.


>>4. How do you connect to the port # since when I use the Data Connection Wizard and use 10.1.1.1:1384 it comes back with the error:
"Transport Layer error."
Make sure you've got port numeber correctly. By default named instance picks up a port number dynamically on every restart. You can force it to use fixed port by specifying Port server configuration property.

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

Analysis Services 2005 OLE DB Provider Error

I try to open a SSAS Cube but I get a OLE DB error message below.

The same cube I can browse with the SQL Server Business Intelligence Development Studio.

Error message:

Microsoft OLE DB Provider for Analysis Services 2005 Microsoft OLE DB Provider for Analysis Services 2005 reported:

Errors in the OLE DB provider. Could not connect to the redirector. Ensure that the SQLBrowser service is running on the '<MY Server Name>' server. Errors in the OLE DB provider. An error occurred while named instance information was being retrieved from the SQLBrowser service on the '<MY Server Name>' server.

The SQL Browser is used when you are attempting to communicate with a named instance. What you are seeing is a known problem, fixed in SP1, where the SQL Browser needs communication with a domain controller to validate your access rights. This happens to me if I disconnect my laptop from the network and try an connect to a named instance on it. I've also seen it if the SQL Browser is running under a service account that isn't a machine administrator -- and third I've seen this if you are running under XP SP2 and the firewall is stopping your connectivity to the SQL Browser.

You have a few options: 1) make it the default instance -- that works without any problems, 2) always make sure a DC is available, 3) make sure the XP firewall is disabled on the SQL Browser port# (and the various instance port#s), 4) attempt to connect specifically to the port # for the instance (which bypasses the redirector) -- try using <server>:<port#> syntax for the server name.

Hope that helps.

_-_-_ Dave

|||

Thanks Dave,

I have check the options:

1. I have installed sql2005 with the default instance <MSSQLSERVER> that is not the problem

2. I'm connected to the DC

3. I have disabled the firewall, and that has changed the error message!!

The new message is:

Error reported by OLAP server.

Additional information:

Microsoft? OLE DB Provider for Analysis Services reported:

Cannot connect to the server '<MYSERVER>. The server is either not started or too busy.

But I can work with the SQL Server Business Intelligence Development Studio without any problem!!

|||

I have found the solution for the reported error message.

After reinstalling the "Workstation components" I can connect to the cubes in SSAS for a client Olap tool.

Thanks Dave for your support.

Regards,

Peter

|||I got the same error with the same symptoms but resolved the OLAP connection problem simply by reinstalling the OLAP provider for SQL Server 2005. The file name is SQLServer2005_ASOLEDB9.msi and it can be found on the microsoft downloads site.|||

Hi Dave,

Some questions for you about your options:

1. How do you make SQL Server 2005 the default instance if SQL Server 2000 is also installed on the same machine.
SQL Server 2000 can be removed if this is an option.
2. How can you confirm that the DC is available besides when you log into the server which is on the domain?
3. The sql server is on a windows 2003 server. The AD server is in another state and is accessed via a VPN. Could this be a problem?
4. How do you connect to the port # since when I use the Data Connection Wizard and use 10.1.1.1:1384 it comes back with the error:
"Transport Layer error."
If I use the instance name syntax -> 10.1.1.1\InstanceName - it works but then it uses the redirector which causes the main error.

So a couple of your solutions have some problems for me... is there any other way around them?

Thanks,
Andrew.

|||

>>1. How do you make SQL Server 2005 the default instance if SQL Server 2000 is also installed on the same machine.

There could be only a single default instance on the machine. And Analysis Services 2000 does not support named instances, so for AS 2005 be default instance you need to remove AS2000.


>>2. How can you confirm that the DC is available besides when you log into the server which is on the domain?

Not sure about this. You can try posting on windows networing newsgroup.


>>4. How do you connect to the port # since when I use the Data Connection Wizard and use 10.1.1.1:1384 it comes back with the error:
"Transport Layer error."
Make sure you've got port numeber correctly. By default named instance picks up a port number dynamically on every restart. You can force it to use fixed port by specifying Port server configuration property.

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

Analysis Services 2005 OLE DB Provider Error

I try to open a SSAS Cube but I get a OLE DB error message below.

The same cube I can browse with the SQL Server Business Intelligence Development Studio.

Error message:

Microsoft OLE DB Provider for Analysis Services 2005 Microsoft OLE DB Provider for Analysis Services 2005 reported:

Errors in the OLE DB provider. Could not connect to the redirector. Ensure that the SQLBrowser service is running on the '<MY Server Name>' server. Errors in the OLE DB provider. An error occurred while named instance information was being retrieved from the SQLBrowser service on the '<MY Server Name>' server.

The SQL Browser is used when you are attempting to communicate with a named instance. What you are seeing is a known problem, fixed in SP1, where the SQL Browser needs communication with a domain controller to validate your access rights. This happens to me if I disconnect my laptop from the network and try an connect to a named instance on it. I've also seen it if the SQL Browser is running under a service account that isn't a machine administrator -- and third I've seen this if you are running under XP SP2 and the firewall is stopping your connectivity to the SQL Browser.

You have a few options: 1) make it the default instance -- that works without any problems, 2) always make sure a DC is available, 3) make sure the XP firewall is disabled on the SQL Browser port# (and the various instance port#s), 4) attempt to connect specifically to the port # for the instance (which bypasses the redirector) -- try using <server>:<port#> syntax for the server name.

Hope that helps.

_-_-_ Dave

|||

Thanks Dave,

I have check the options:

1. I have installed sql2005 with the default instance <MSSQLSERVER> that is not the problem

2. I'm connected to the DC

3. I have disabled the firewall, and that has changed the error message!!

The new message is:

Error reported by OLAP server.

Additional information:

Microsoft? OLE DB Provider for Analysis Services reported:

Cannot connect to the server '<MYSERVER>. The server is either not started or too busy.

But I can work with the SQL Server Business Intelligence Development Studio without any problem!!

|||

I have found the solution for the reported error message.

After reinstalling the "Workstation components" I can connect to the cubes in SSAS for a client Olap tool.

Thanks Dave for your support.

Regards,

Peter

|||I got the same error with the same symptoms but resolved the OLAP connection problem simply by reinstalling the OLAP provider for SQL Server 2005. The file name is SQLServer2005_ASOLEDB9.msi and it can be found on the microsoft downloads site.|||

Hi Dave,

Some questions for you about your options:

1. How do you make SQL Server 2005 the default instance if SQL Server 2000 is also installed on the same machine.
SQL Server 2000 can be removed if this is an option.
2. How can you confirm that the DC is available besides when you log into the server which is on the domain?
3. The sql server is on a windows 2003 server. The AD server is in another state and is accessed via a VPN. Could this be a problem?
4. How do you connect to the port # since when I use the Data Connection Wizard and use 10.1.1.1:1384 it comes back with the error:
"Transport Layer error."
If I use the instance name syntax -> 10.1.1.1\InstanceName - it works but then it uses the redirector which causes the main error.

So a couple of your solutions have some problems for me... is there any other way around them?

Thanks,
Andrew.

|||

>>1. How do you make SQL Server 2005 the default instance if SQL Server 2000 is also installed on the same machine.

There could be only a single default instance on the machine. And Analysis Services 2000 does not support named instances, so for AS 2005 be default instance you need to remove AS2000.


>>2. How can you confirm that the DC is available besides when you log into the server which is on the domain?

Not sure about this. You can try posting on windows networing newsgroup.


>>4. How do you connect to the port # since when I use the Data Connection Wizard and use 10.1.1.1:1384 it comes back with the error:
"Transport Layer error."
Make sure you've got port numeber correctly. By default named instance picks up a port number dynamically on every restart. You can force it to use fixed port by specifying Port server configuration property.

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

Analysis Services 2005 Design Challenge

Hi to all, I’m designing a SSAS 2005 cube based on an Oracle data source

We have a classical star schema with several millions of records into the fact table.

Now everything goes fine since dimensions are quite small (the greatest one has 100000 records), but users are asking me the possibility of view also more detailed data such order_number, client fiscal code, etc…and this data it has been stored into the fact table as a fact degenerate dimension.

Now I don’t want to load into the cube this degenerate dimension because it contains as I said too many records.

I was thinking to drill-through functionality (achieved setting the storage mode of the degenerate dimension to ROLAP) but after several tests it seems very very slow (it launches queries that incredibly big grouping all the dimensions and all the fields of the fact table or other times launches several heavy queries catching all the distinct values of order_number, client fiscal code, etc…) and some times the client itself (OWC) hangs and I have to kill it.

Note that these tests are done on the development environment where the fact table contains more or less only 70000 records!

I tried also to query directly this degenerate dimension (without drill-through) but the results are the same.

Any suggestions?

Thanks.

Alberto

Try using MOLAP dimensions. They should scale pretty well in AS2005.

You should be able to handle several milion members with MOLAP dimensions.

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

Analysis Services 2005 Derived Member

Hi,

I can't seem to find any information about creating a derived member in SSAS 2005. I have found a bunch of stuff for 2000. I have some Calculated members that are giving me some performance pains that I was hoping to solve by creating derived members.

Any info would be greatly appreciated.

S

WHat is "derived member" ? Neither AS2000 nor AS2005 have such a concept. Can you point out to the information you found, so it will be easier for us to understand what you mean. Or, perhaps, you can describe your scenario in detail, and then this forum might be able to help.|||

I apologize. It was Derived Member.

Here is a link: http://www.databasejournal.com/features/mssql/article.php/3394681

|||

In this case, the "derived member" discussion in that link is the equivalent of adding named calculations within the DSV of an SSAS 2005 solution.

A named calculation is added to a table (or view) that has been included within a DSV. The named calculation takes an expression (in the SQL dialect of the underlying RDBMS involved) and provides a name for the resulting value. This then becomes another available column within the table from the perspective of the rest of the SSAS solution. Thus, a measure could be built using the named calculation (if it is in a fact table) or an attribute could be created using it (if it is in a dimension table).

HTH,

Dave Fackler

Analysis Services 2005 Deployment Wizard doubts

As I understand it SSAS encrypts its data by default; however, I'm looking for strategies on encrypting data in the underlying datawarehouse. If you have a column encrypted in the datawarehouse, what are the options to expose that data, selectively of course, through Analysis Services? The only solution I've found is to bind a dimension to a view in the datasource and have that view decrypt the column. The dimension attribute could then be selectively exposed based on the role(s) the user has access to.

Is this the BEST way to do it? Are there other options and considerations? Are there any great whitepapers on this subject? I haven't found any myself.

Thanks in advance,

Terry

Havent seen any whitepapers on the subject.

Another idea for you to consider is to create a named query in Analysis Services DSV. See if you can use that to skip creating a view in relational database.

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

|||I am working with SQL server 2005 analysis and reporting services. I am

instructed to create a cube for a database using analysis services and
then replicate it so as to produce reports online by reporting services

when requested by clients. I am able to create the cube and also deploy

the report made, in HTTP separately. But the following doubts arise
during the Cube deployment:

The Cube was created as per the requirements by my team lead. Then I
used the deployment wizard in the analysis services 2005 to convert it
into XML script. Using the SQL Management Studio I opened it as
Analysis Server scripts and used the local host as the system in the
connections windows then loaded the XML script into the Queries window
as a XMLA query and executed it. What has to be done after this in
order to use it as a production server?

Once the production server is setup and the connection is made with the

staging server, How can we set the timings to when the updating of the
production server has to be set in terms of hours, days or weeks?

Hoping these doubts would be clarified as early as possible.