Showing posts with label olap. Show all posts
Showing posts with label olap. Show all posts

Saturday, February 25, 2012

Anonymous Access from Pivot Table to OLAP 2005

My asp-net web application connect to Analysis Services 2005 through Pivot Table client.

To use anonymous access with AS2000 i used to add this property to the connection string of the pivot: "SSPI=anonymous".

Which property give me the same thing with AS2005?

Thanks!

Hi,

The SSPI connection property is still there, so it should work the same. Are you getting an error?

David Botzenhart

|||

Hey David - good to hear from you !

Yes, you could still use SSPI=anonymous, but I really recommend in AS2005 to use standard OLEDB property for this purpose, i.e.

Protection Level=none

It has slightly better (more standard) behavior.

Mosha (http://www.mosha.com/msolap )

|||

Thanks, it's working.

But, if i set "Impersonation Level" property to Anonymous? Is this the same of SSPI=anonymous?

Thursday, February 16, 2012

Analysis Services SP4 problems

Hello I hope someone can help me.

On a SQL server that has had SP4 recently installed on it for both the SQL Server service and OLAP service, I am unable to browse the data of any of the Shared Dimensions or Cubes of any of the databases when I use the Analysis Manager MMC. At the time I am logged onto the server as an administrator using terminal services. Upon attempting to browse a shared dimension the following error is returned "Unable to browse the dimension <dimension name>, Type mismatch" and when attempting to browse a cube the error is "Unable to browse the cube <cube name>. An unexpected internal error has occured. Please restart the application."

The Cubes can be browsed via Excel though and all Reporting Service mdx queries are functioning correctly.

If I register the same server in Analysis Manager MMC on a different server also with SP4 all Cubes and Dimensions data can be browsed.

I tried archiving the database cubes and restoring them on a server with SP3 and all data can be browsed via the servers own Analysis Manager MMC.

Any ideas?

This can be related to an MDAC version issue if this post is correct: http://www.dbforums.com/archive/index.php/t-357508.html

Regards

Thomas Ivarsson

|||

I suspect you are right. Thank you for the help Thomas.

On the servers that have "MDAC 2.8 on Windows XP SP2" seem to have the same problem. Servers that are using "MDAC 2.8 RTM" are all functioning correctly, (as determined by "Component Checker").

Do you happen to know the link of a webpage that gives a walkthrough on how to perform a MDAC Rollback?

I have not found any information on how to do this correctly.

|||

Sorry, I have no link, except that I think there is a newsgroup for this(http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1).

Kind Regards

Thomas Ivarsson

|||

I have encountered the same situation aftr installing SP4. Please let me know how to fix this apparently MDAC related problem

Thanks

Attila

|||

I am currently administrating the production Analysis Server with Analysis Manager MMC via a remote connection that also uses "MDAC 2.8 on Windows XP SP2". This permits browsing of dimension and cube data as well as virtual cube editing. (An annoying work around).

I have discovered that "MDAC 2.8 RTM" seems to function correctly but I have not yet found the correct procedure for performing a MDAC rollback, which is quite probably quite complicated considering all the dll's and registry settings involved. All I can suggest is to browse the relevant forum as per Thomas's rely above.

Best of luck.

Analysis Services SP4 problems

Hello I hope someone can help me.

On a SQL server that has had SP4 recently installed on it for both the SQL Server service and OLAP service, I am unable to browse the data of any of the Shared Dimensions or Cubes of any of the databases when I use the Analysis Manager MMC. At the time I am logged onto the server as an administrator using terminal services. Upon attempting to browse a shared dimension the following error is returned "Unable to browse the dimension <dimension name>, Type mismatch" and when attempting to browse a cube the error is "Unable to browse the cube <cube name>. An unexpected internal error has occured. Please restart the application."

The Cubes can be browsed via Excel though and all Reporting Service mdx queries are functioning correctly.

If I register the same server in Analysis Manager MMC on a different server also with SP4 all Cubes and Dimensions data can be browsed.

I tried archiving the database cubes and restoring them on a server with SP3 and all data can be browsed via the servers own Analysis Manager MMC.

Any ideas?

This can be related to an MDAC version issue if this post is correct: http://www.dbforums.com/archive/index.php/t-357508.html

Regards

Thomas Ivarsson

|||

I suspect you are right. Thank you for the help Thomas.

On the servers that have "MDAC 2.8 on Windows XP SP2" seem to have the same problem. Servers that are using "MDAC 2.8 RTM" are all functioning correctly, (as determined by "Component Checker").

Do you happen to know the link of a webpage that gives a walkthrough on how to perform a MDAC Rollback?

I have not found any information on how to do this correctly.

|||

Sorry, I have no link, except that I think there is a newsgroup for this(http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1).

Kind Regards

Thomas Ivarsson

|||

I have encountered the same situation aftr installing SP4. Please let me know how to fix this apparently MDAC related problem

Thanks

Attila

|||

I am currently administrating the production Analysis Server with Analysis Manager MMC via a remote connection that also uses "MDAC 2.8 on Windows XP SP2". This permits browsing of dimension and cube data as well as virtual cube editing. (An annoying work around).

I have discovered that "MDAC 2.8 RTM" seems to function correctly but I have not yet found the correct procedure for performing a MDAC rollback, which is quite probably quite complicated considering all the dll's and registry settings involved. All I can suggest is to browse the relevant forum as per Thomas's rely above.

Best of luck.

Analysis Services SP4 problems

Hello I hope someone can help me.

On a SQL server that has had SP4 recently installed on it for both the SQL Server service and OLAP service, I am unable to browse the data of any of the Shared Dimensions or Cubes of any of the databases when I use the Analysis Manager MMC. At the time I am logged onto the server as an administrator using terminal services. Upon attempting to browse a shared dimension the following error is returned "Unable to browse the dimension <dimension name>, Type mismatch" and when attempting to browse a cube the error is "Unable to browse the cube <cube name>. An unexpected internal error has occured. Please restart the application."

The Cubes can be browsed via Excel though and all Reporting Service mdx queries are functioning correctly.

If I register the same server in Analysis Manager MMC on a different server also with SP4 all Cubes and Dimensions data can be browsed.

I tried archiving the database cubes and restoring them on a server with SP3 and all data can be browsed via the servers own Analysis Manager MMC.

Any ideas?

This can be related to an MDAC version issue if this post is correct: http://www.dbforums.com/archive/index.php/t-357508.html

Regards

Thomas Ivarsson

|||

I suspect you are right. Thank you for the help Thomas.

On the servers that have "MDAC 2.8 on Windows XP SP2" seem to have the same problem. Servers that are using "MDAC 2.8 RTM" are all functioning correctly, (as determined by "Component Checker").

Do you happen to know the link of a webpage that gives a walkthrough on how to perform a MDAC Rollback?

I have not found any information on how to do this correctly.

|||

Sorry, I have no link, except that I think there is a newsgroup for this(http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1).

Kind Regards

Thomas Ivarsson

|||

I have encountered the same situation aftr installing SP4. Please let me know how to fix this apparently MDAC related problem

Thanks

Attila

|||

I am currently administrating the production Analysis Server with Analysis Manager MMC via a remote connection that also uses "MDAC 2.8 on Windows XP SP2". This permits browsing of dimension and cube data as well as virtual cube editing. (An annoying work around).

I have discovered that "MDAC 2.8 RTM" seems to function correctly but I have not yet found the correct procedure for performing a MDAC rollback, which is quite probably quite complicated considering all the dll's and registry settings involved. All I can suggest is to browse the relevant forum as per Thomas's rely above.

Best of luck.

Analysis Services Rolling Date reports

I have a Time and Billing OLAP cube which I'm running RS against. I'm
stumped on a date issue.
How can I run a report which selects the last 7 days worth of data and rolls
forward each week? I've tried using an MDX query with the Tail funtion.
This pulls the last date which has data in it. (Effectively yesterday's
date) When I run it in MDX builder it gives me the desired results. However
when I use it in VS.Net to build my report, it pulls the last 7 days over,
but it's static and doesn't roll forward.
I can make a report with a date drop down parameter which is described in
the AS and RS article on Technet, but I'd really like the parameter to be
automatically selected based off of the date when the report is run.
My date formats in my cube are [Year].[Quarter].[Month].[Day]
Can anybody help me with this?
Thanks, MattTry setting defaults for your start and end date parameters. For
example, you can set the end date to
DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy") and the start date to
DateTime.Now.AddDays(-7).ToString("MM/dd/yyyy"). When you see the
parameters in subscription creation, it may look like they are
hard-coded, but they aren't as long as the default checkbox is selected.|||Thanks for the suggestion... I think I'm close...
The problem is that my date needs to be in a [Time].[FY Calendar].[All
Time].[Year].[Quarter].[Month].[Day] format.
As you probably can tell, I'm a newbie to VB Scripting. How can I convert
your suggestion "DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy")" to my cube
format? I think if I can do that, I can get it to work.
Thanks,
Matt
"Kenny" wrote:
> Try setting defaults for your start and end date parameters. For
> example, you can set the end date to
> DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy") and the start date to
> DateTime.Now.AddDays(-7).ToString("MM/dd/yyyy"). When you see the
> parameters in subscription creation, it may look like they are
> hard-coded, but they aren't as long as the default checkbox is selected.
>

Monday, February 13, 2012

Analysis Services Perfomance Issues (when open a cube definition)

Hi

we are dealing whit a perfomance issue in Analysis Server 2005 working with olap cubes. This cubes are part of a solution with a view (91 tables) and every time we try to access some meta data of the cube in the Visual Studio 2005 (i.e.: Calculations, KPIs, etc) it takes a lot of time to read the metadata of the cube. The cubes are not so big, but every time we try to get the cube metadata we have problems.In Example; it takes more than 3 minutes to get the structure of the cube in the browse section; but after this we can easily drop fields to the sections of the cube designer and watch the cube data and calculations.

We have the same problem in Excel 2007 when we try to connect to the Analysis Services database and read some information; when we get the definition of the cubes, then the data takes a normal time to be filled.

Does anybody has some ideas or suggestions ?

Regards

Bruno

Do you have any ROLAP dimensions or a lot of security roles?|||

Hi Jeffrey

no, we currently have 1 one that call an advanced function and uses a .Net Assembly in order to check the security for some objects. I don't know if this may affect the "browser" perfomance.

Regards

Bruno

|||

Do you have dimensions with ragged hierarchies? Are you on SQL Server 2005 RTM, SP1 or SP2?

You can attach the Profiler to the AS Server to identify which queries and/or commands take a long time to complete.

Analysis services part of SQLServer ?

Is Analysis services part of SQLServer ?
Should I install something ?
I need to create OLAP cubes in my SQLServer db.
ThanksThere's a profiler installed by default which helps you analyse how sql performs, what are it's bottlenecks and who/what/which gives it a hardtime. SQLServer keeps all sorts of statistics concerning a.o. table access.

I'm not sure how this translates to olap cubes though.|||According to the Features by Edition (http://www.microsoft.com/sql/evaluation/features/choosing.asp) table, Analysis Services is part of the Enterprise Edition of SQL Server 2000.

-PatP|||I need to create OLAP cubes in my SQLServer db.


Tell me if you are able to do that .. I always used to create stuff like Tables, Stored Procs triggers and functions in my SQL Server DB ...:cool:

and yes ... MS Analysis Server comes with SQL Server 2000 by default ... you just need to install it separately.

Analysis Services Migration Wizard

I backedup an SQL2K OLAP database. Learned that I can not use the cab file to restore it to an SQL2005. So I've restored it to another SQL2K. Now I am trying to migrate this to my 2005. Here are my tests and results.

1. Welcome screen works fine. :)

2. I have severel SQL2K servers here which have their OLAP services running.

a.) SQL 2000 Source Server: xxxxxxxxx (no such server)
SQL 2005 Destination Server: AATIK (right server-local)
Error: Source server: The following system error occurred: .

b.) SQL 2000 Source Server: SABRI (right server)
SQL 2005 Destination Server: AATIK (right server-local)
Error: Source server: The following system error occurred: .

c.) SQL 2000 Source Server: SONAY (another right server)
SQL 2005 Destination Server: AATIK (right server-local)
Error: Source server: ActiveX component can't create object

d.) SQL 2000 Source Server: SONAY (another right server)
SQL 2005 Destination Server: xxxxxxxxx (no such server)
Error: Source server: ActiveX component can't create object

e.) SQL 2000 Source Server: SONAY (another right server)
SQL 2005 Destination Server: (local) (got bored of typing AATIK)
Error: Source server: ActiveX component can't create object

I've also tried "localhost", ".", "aatik", "aatik\aatik" etc. for destination server.

Is it a bug in Turkish for letters I and i ?

By the way SP1 is installed.

Thanks.

You can take destination out of the picture by specifying script file as a destination.

Looks like you are having problem connecting to AS2000 source server.

Take a look if you can access AS2000 sever remotely. Can you access it from Analysis Manager running on another machine?

You also have an option of running AS2000 and AS2005 on the same machine.
You can rename your AS2005 sever to become a named instance. Install AS2000 locally, restore your databases and then migrate a databases.
Uninstall AS2000 and then rename back AS2005 to be a default instance.

To rename AS2005 instance you can use ASInstanceRename.exe located in your C:\Program Files\Microsoft SQL Server\90\Tools\binn\VSShell\Common7\IDE folder.

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

|||

Thanks Edward.

Tried the scriptfile option but again got the same ActiveX error message.

Analysis Services Migration Wizard

I backedup an SQL2K OLAP database. Learned that I can not use the cab file to restore it to an SQL2005. So I've restored it to another SQL2K. Now I am trying to migrate this to my 2005. Here are my tests and results.

1. Welcome screen works fine. :)

2. I have severel SQL2K servers here which have their OLAP services running.

a.) SQL 2000 Source Server: xxxxxxxxx (no such server)
SQL 2005 Destination Server: AATIK (right server-local)
Error: Source server: The following system error occurred: .

b.) SQL 2000 Source Server: SABRI (right server)
SQL 2005 Destination Server: AATIK (right server-local)
Error: Source server: The following system error occurred: .

c.) SQL 2000 Source Server: SONAY (another right server)
SQL 2005 Destination Server: AATIK (right server-local)
Error: Source server: ActiveX component can't create object

d.) SQL 2000 Source Server: SONAY (another right server)
SQL 2005 Destination Server: xxxxxxxxx (no such server)
Error: Source server: ActiveX component can't create object

e.) SQL 2000 Source Server: SONAY (another right server)
SQL 2005 Destination Server: (local) (got bored of typing AATIK)
Error: Source server: ActiveX component can't create object

I've also tried "localhost", ".", "aatik", "aatik\aatik" etc. for destination server.

Is it a bug in Turkish for letters I and i ?

By the way SP1 is installed.

Thanks.

You can take destination out of the picture by specifying script file as a destination.

Looks like you are having problem connecting to AS2000 source server.

Take a look if you can access AS2000 sever remotely. Can you access it from Analysis Manager running on another machine?

You also have an option of running AS2000 and AS2005 on the same machine.
You can rename your AS2005 sever to become a named instance. Install AS2000 locally, restore your databases and then migrate a databases.
Uninstall AS2000 and then rename back AS2005 to be a default instance.

To rename AS2005 instance you can use ASInstanceRename.exe located in your C:\Program Files\Microsoft SQL Server\90\Tools\binn\VSShell\Common7\IDE folder.

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

|||

Thanks Edward.

Tried the scriptfile option but again got the same ActiveX error message.

Sunday, February 12, 2012

Analysis Services Error

Hi,

I am getting the following error when processing the cube through analysis services.

Errors in the OLAP storage engine: The slice specified for the Month of Year attribute is incorrect.' Error Code: -1054932968

Please let me the the possible resolutions if you have encountered the same type of error.

Regards,

Dinesh Reddy N Y

I think you get this error if you specify an invalid member declaration for the Slice property of a partition. Go to the Partitions tab of the cube you are trying to deploy and verify that the Slice property is set to a valid member. Having it not set at all is also an option even though not recommended.

Analysis Services Cube Measures showing #ERR

Hi,

We have a MS OLAP Cube using 10 dimensions and 1 fact table with measures as COUNT and DISTINCT COUNT.

When we processed the cube using Analysis Services, it was done but all the measures are showing #ERR as values. The cube is about 650MB in size and all the joins between Dimension and Fact tables seem to be OK.

What could be the possible reasons of getting #ERR in measures? Do all the datatypes and length of JOIN columns between dimension and fact HAVE TO BE the same??

Please provide any inputs as this is very urgent.
Thanks
Omkarid assume its some division by 0 error

you can deal with this by replacing a Zero with a NULL in whatever field you use as a denominator. (the bottom half of division-- i hope that was the right word)

Thursday, February 9, 2012

Analysis Services Admin access to one database only

On a given Analysis Server the machine level OLAP Administrators group controls which users have admin access to AS Databases and Cubes on that machine. From everything I have read, if you are in the OLAP Administrators group you have full access to administer ALL the databases and cubes.

We have a need to create a OLAP database and grant a few users (a role) full admin access to create and maintain datasouces and cubeswithin that database but NOT allow them admin access to the other existing databases and cubes on the server. This seems like such a common requirement. Has anyones else encountered and resolved this issue.

Thanks,

Tony

How about add the users to 'dbo' role in that OLAP database?|||The concept of "dbo role" does not exist for AS databases