OLAP Install - Separate and Distinct from OLTP? 1

[dougcranston]

Stupid User Question....

I have an application that mgt has asked for that from my limited knowledge should be constructed as an OLAP vs OLTP (which all of our apps are on our server right now under SQL Server 2000 on Win2k3).

Long ago in an SQL Server Class I have a vague understanding that OLAP and OLTP don't mix on the same server.

However, I cannot find any reference to either support or counter it either.. Searched all my books.. and the web to no avail.. May be my search technique..

Only thing I found was the following:
"MSSQLServerOLAPService
Unless Analysis Services is installed on a particular server, this service will not be present. It is installed separately of any normal MS SQL Server installs and as a result is not a common sight on most SQL Server installations.

MSSQLServer is not required for Analysis Services, as it by default uses a Microsoft Access (.mdb) database for its Repository, though the Repository can be migrated to SQL Server. With respect to permissions for the service, it is important that the MSSQLServerOLAPService has sufficient permissions to access any and all data sources with which to build and process objects from. It is the service's user account, not that of an Analysis Services administrator, that is used to access the data source."

So on to my questions:
======================
1) Can SQL Server Analysis Services be installed on the same server? App at this time is projected to handle under a 1 gb of data.. Existing OLTP apps are under 100mb at this time. More OLTP apps to come, but no major usage at this time.

2) Is there additional software, and if so what, needed for accessing/development work beyond Enterprise Mgr and Query Analyzer to support OLAP?

Any suggestions on sources or reference that could answer these questions would be greatly appreciated.

Thanks inadvance.

Dougc

 

[RiverGuy]

I would never want to install an OLAP system on the same server as an OLTP system. However, if you split this into two servers, then I would suggest having the dw relational database-star or snowflake schema-existing on the server with the OLAP database, and not on the OLTP server. The last thing you want is for your OLTP application users to experience a slowdown because of heavy analytical queries or processing.

As far as Analysis Services, it can be installed on a server with the relational engine installed. However, to move it to its own server, you will need another license.

All of the development software you need should be on the SQL Server installation CD.

 

[MDXer]

The installation of the OLAP SQL Server components on the same server as your other databases can rely on a number of factors such Server configuration, DB utilization, projects AS usage, projected Cube count and complexity along with cube and dim size.

The server configuration is important as Analysis Services is a resource HOG. it will use as much available resources as it can especially memory up to 3GB.

On the same server as your current DBs not recommended but if the server has the resources and the DBs are of low utilization and the cube(s) are relatively small and not complex then possibly.

The main thing to know about AS is that when AS service starts it reads all dimension members and member properties into memory. If you have huge dimension there goes your memory.

When processing cubes this is resource intense at all levels CPU IO and memory. It is extremely memory intense if you have distinct count aggregates and a high number of aggregations on the cube.

If you have a distinct count aggrgate make sure you follow the proper technique (posted in this forum a number of times).

Cube complexity and design have a large role on query performance. Preaggregations, Partitions, Associating patritions with a dimensional slice. All of these affect how much data is processed, how much data is queried and how the data gets queried.

It is fairly safe to place the AS components on the same server as your DW, again depending on server configurations. If you do this a couple rules to follow are.

1) You AS Databases need to reside on a physical drive set different from you relational DW.
2) AS utilizes temp drive space the same as SQL uses TempDB. So you should allocate a physical drive set Seperate from all SQL server DBs including TempDB and seperate from your AS datadrives.

As RiverGuy stated if you opt to install the AS components on a seperate server then you must have this machine licenesed correctly

Shoot Me! Shoot Me NOW!!!
- Daffy Duck