How to change the directories used by the "SQL Server Analysis Services"

I had to change the directories used by the SQL Server Analysis Services. This is what I have done.

I have changed the directories for the SQL Server Analysis Services through the properties window Next I stopped the SQL Server Analysis Services and moved the entire OLAP directory to a different directory on the same drive Then I tried to start the SQL Server Analysis Services which fails

In the Event log I only see the following entry:

Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 8355
Date: 18/04/2007
Time: 13:02:53
User: N/A
Computer: SQLSERVER
Description:
Server-level event notifications can not be delivered. Either Service Broker is disabled in msdb, or msdsb failed to start. Event notifications in other databases could be affected as well. Bring msdb online, or enable Service Broker.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: a3 20 00 00 10 00 00 00 ￡ ......
0008: 0a 00 00 00 49 00 53 00 ....I.S.
0010: 4f 00 41 00 50 00 50 00 O.A.P.P.
0018: 32 00 35 00 34 00 00 00 2.5.4...
0020: 07 00 00 00 6d 00 61 00 ....m.a.
0028: 73 00 74 00 65 00 72 00 s.t.e.r.
0030: 00 00 ..

By changing the directories back to their original values in the msmdsrv.ini file and moving the entire directory back to its original location I was able to start the SQL Server Analysis Services, but I really need to move the directories, any help is appreciated.

As with any application, after it is installed, it is not easy to move it to different location. I would strongly recommend that you consider re-installing SSAS.

But, there is a way for you to move data folder, that is usually the biggest folder.

For that go to SSAS properties, change parameter: DataDir to your desired location, then stop SSAS, move your data folder to new location and start SSAS.

Vidas Matelis

How to change the directories used by the "SQL Server Analysis Services"

I had to change the directories used by the SQL Server Analysis Services. This is what I have done.

I have changed the directories for the SQL Server Analysis Services through the properties window Next I stopped the SQL Server Analysis Services and moved the entire OLAP directory to a different directory on the same drive Then I tried to start the SQL Server Analysis Services which fails

In the Event log I only see the following entry:

Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 8355
Date: 18/04/2007
Time: 13:02:53
User: N/A
Computer: SQLSERVER
Description:
Server-level event notifications can not be delivered. Either Service Broker is disabled in msdb, or msdsb failed to start. Event notifications in other databases could be affected as well. Bring msdb online, or enable Service Broker.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: a3 20 00 00 10 00 00 00 ￡ ......
0008: 0a 00 00 00 49 00 53 00 ....I.S.
0010: 4f 00 41 00 50 00 50 00 O.A.P.P.
0018: 32 00 35 00 34 00 00 00 2.5.4...
0020: 07 00 00 00 6d 00 61 00 ....m.a.
0028: 73 00 74 00 65 00 72 00 s.t.e.r.
0030: 00 00 ..

By changing the directories back to their original values in the msmdsrv.ini file and moving the entire directory back to its original location I was able to start the SQL Server Analysis Services, but I really need to move the directories, any help is appreciated.

As with any application, after it is installed, it is not easy to move it to different location. I would strongly recommend that you consider re-installing SSAS.

But, there is a way for you to move data folder, that is usually the biggest folder.

For that go to SSAS properties, change parameter: DataDir to your desired location, then stop SSAS, move your data folder to new location and start SSAS.

Vidas Matelis

How to call the processing of a cube ?

I need to automatically process a cube. Do I have to use an Analysis Managment Object (AMO) ?

Otherwise, I know that it is possible to create a Sql Agent Job to run periodically, but I don't want to schedule this task. In fact, I need to "call" the processing of my cube, wich will run between other jobs. The "caller" is a Visual Basic 6 program from wich I am able to launch .exe files.

thanks

Hello. I am not sure if you are taking about SSSAS2000 or SSAS2005?

Anyway, you can make a dts-package in SQL Server 2000 or a SSIS-package in BI-Dev Studio that process the dimensions and the cubes.

These packages are possible to call by a VB-application but I think you must use VB6 for dts and VB.Net 2005 for SSIS(Integration services).

HTH

Thomas Ivarsson

|||

Hi. You can also use the command prompt utility "DTEXEC" from VB6 to call a package developed with SSIS if you have to. However, Thomas' suggestion of using VB .NET 2005 is best. Here's more information on the DTEXEC utility.

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

Paul Goldy

|||

Here are a couple of articles I published with step-by-step setup for the DTS / SSIS scenarios involved:

For SSAS 2k (DTS):

http://www.databasejournal.com/features/mssql/article.php/3503201

For SSAS 2k5 (SSIS):

http://www.databasejournal.com/features/mssql/article.php/3584306

Good Luck!

Bill

William E. Pearson III
CPA, CMA, CIA, MCSE, MCDBA
Island Technologies Inc.
931 Monroe Drive
Suite 102-321
Atlanta, GA 30308

404.872.5972 Office
wep3@.islandtechnologies.com
wep3@.msas-architect.com

www.msas-architect.com
-- -- --

Publisher Sites:

http://www.databasejournal.com/article.php/1459531

http://www.sql-server-performance.com/bill_pearson.asp

http://www.informit.com/authors/bio.asp?a=862acd62-4662-49ae-879d-541c8b4d656f

http://www.2000trainers.com/section.aspx?sectionID=17

|||

Thanks ! What I need is in the first article link you posted, Bill. I will create a DTS package.

The problem is, I can't do the step #9 on page 3. I don't have the Analysis Services Processing Task ICON in the TASKS TOOLBAR ... ?

If that can helps, I work with : SQL Server Enterprise Manager version 8.0
Analysis Manager version 5
Visual Basic 6 (the program that calls the DTS package)

So, what do you think ?

PS - I found how to call the package with VB6 on this page :
http://www.sqlservercentral.com/columnists/bknight/executingpackagefromvisualbasic.asp

EDIT : I found on the Microsoft web site that the Analysis Processing task is available only when SQL Server 2000 Analysis Services is installed.
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/dtssql2k.mspx

How to build cubes from Oracle database?

Hi, can anyone show me the steps to use SQL Server

to perform OLAP analysis for data from Oracle relational database?

Thanks,

Chris

Do you already have a dimensional model (fact and dimension tables) set up in your Oracle database?

Bryan

|||

Bryan C. Smith wrote:

Do you already have a dimensional model (fact and dimension tables) set up in your Oracle database?

Bryan

Not yet.

Should I build the dimensional model in SQL Server or Oracle, and how?

Is there any document to show the steps?

Thanks,

Chris

|||

Traditionally, the OLAP database/cube exists as a cached layer on top of a series of fact and dimension tables (referred to as a dimensional model) in a relational database. SSAS 2005 can support dimensional models built in both Oracle and SQL Server (as well as other relational database technologies).

Technically speaking, you can skip the implementation of the dimensional model in the relational database. You do this by assembling sets in the DSV of your cube through named queries. Some folks with smaller data marts have successfully pulled this off, but it is not recommended.

If you are new to dimensional modeling, I highly recommend Ralph Kimball's "The Data Warehouse Toolkit". It will give you a solid foundation in this stuff and SSAS 2005 is aligned with the design principle he expouses.

Good luck,
Bryan

|||I agree that this is a good book to start with. You can also try the tutorial included with SQL Server Anlaysis Services 2005. All the same concepts apply regardless of whether your getting your data from a SQL Server relational database or an Oracle relational database. The only difference will be whithin the Data Source object which abstracts the connection to the source data.

How to build an Analysis Services project

I have have created a analysis services project on my development machine, but I now want to be able to build this type of project on my build machine as part of my automated build process. I have installed the Business Intelligence Development Studio, but Visual Studio does not recognize the project type. What else do I need to install?

Thanks,

Anthony

Hi,

This command line does the build for me:

devenv "Analysis Services Project1.sln" /build Development

Having BI Development Studio installed should be enough, also please make sure that you run the 'devenv.exe' from '%ProgramFiles%\Microsoft Visual Studio 8\Common7\IDE' (in case you have multiple installed).

Adrian Dumitrascu

|||

Yes I am running devenv from the correct location. I have also tried to load the project into Visual Studio and all it does it open it like a text file.

Anthony

|||

In VS -> Help -> About, do you have an entry for 'SQL Server Analysis Services' ?

It seems that the BI Development Studio is not properly installed (or it was broken by something).

Adrian

|||My build problem was do to a bad path. My scripts were still looking at VS2002 instead of VS2005.

I have corrected my script, but I now have a new problem. When running the devenv command I am getting the following error.
Catastrophic failure (Exception from HRESULT: 0x8000FFFF (E_UNEXPECTED))

If I open the project using the same command line, but I exclude the /build option and open it in the IDE, the project builds fine.
Any ideas as to why this project would not build from the command line?

Anthony

How to build a report from a cube in SSRS 2005

Hi, All

I am trying to create a report from analysis service cube but I am facing some problems.

While creating new dataset in report designer, MDX query designer is not displaying while giving Sql Server Analysis Service in Data source.

Please give the details of creating reports using cube.

When you look at the data source, what is it's type? It should be "Microsoft SQL Server Analysis Services" and not "OLEDB" if you want a multidimensional view of the data.