Anaylsis Server 2005 and wrong results in dimensions/cube

My cubes are somehow incorrect and I can't find out why:

I created a very simple table, because of wrong results in my project cubes and inserted 102 rows:

-

USE EA_DWH
GO

CREATE TABLE [dbo].[TEST_DWH]
(
[DWSTOREDATE_INT] INT NOT NULL,
[USER_NAME] VARCHAR(20) NULL
)
GO

INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021216','USER_99')
INSERT INTO TEST_DWH VALUES('20021217','USER_03')
INSERT INTO TEST_DWH VALUES('20021218','USER_04')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021219','USER_01')
INSERT INTO TEST_DWH VALUES('20021220','USER_01')
INSERT INTO TEST_DWH VALUES('20021220','USER_01')
INSERT INTO TEST_DWH VALUES('20021220','USER_01')
INSERT INTO TEST_DWH VALUES('20021220','USER_01')
INSERT INTO TEST_DWH VALUES('20021220','USER_01')
INSERT INTO TEST_DWH VALUES('20021220','USER_01')
INSERT INTO TEST_DWH VALUES('20021220','USER_01')
INSERT INTO TEST_DWH VALUES('20021220','USER_01')
INSERT INTO TEST_DWH VALUES('20021220','USER_03')
INSERT INTO TEST_DWH VALUES('20021220','USER_02')
INSERT INTO TEST_DWH VALUES('20021220','USER_02')
INSERT INTO TEST_DWH VALUES('20021220','USER_02')
INSERT INTO TEST_DWH VALUES('20021220','USER_03')
INSERT INTO TEST_DWH VALUES('20021220','USER_02')
INSERT INTO TEST_DWH VALUES('20021220','USER_02')
INSERT INTO TEST_DWH VALUES('20021220','USER_03')
GO

-

The first value of this table represents the date value as integer and the second value is the user name.

Now when building a cube (I) you will find out that the values differ to a simple group by on the sql database (II):

I count all user and the dimension is build on DWSTOREDATE_INT (logical key) as key attribute and USER_Name as regular attribute.

(I):

-

TEST DWH Count TEST DWH USER NAME Ergebnis 20021216 USER_99 22 20021216 Ergebnis 22 20021217 USER_03 1 20021217 Ergebnis 1 20021218 USER_04 1 20021218 Ergebnis 1 20021219 USER_01 62 20021219 Ergebnis 62 20021220 USER_01 16 20021220 Ergebnis 16 Gesamtergebnis 102

-

(II):

-

SQL query (SQL Server):

SELECT DWSTOREDATE_INT, USER_NAME, COUNT(*) AS Count_StoreDate_User
FROM TEST_DWH
GROUP BY DWSTOREDATE_INT, USER_NAME
ORDER BY DWSTOREDATE_INT

RESULT:

DWSTOREDATE_INT USER_NAME Count_StoreDate_User
-- --
20021216 USER_99 22
20021217 USER_03 1
20021218 USER_04 1
20021219 USER_01 62
20021220 USER_01 8
20021220 USER_02 5
20021220 USER_03 3

(7 row(s) affected)

-

I tried this example on three different computers and can't find out why this happens. On Analysis Server 2000 it works correctly.

My computer:

W2K3 (SP1), SQL Server 2005 EE (SP1 CTP)

I had the same results without SP1 CTP and installed it because I thought this issue may be corrected.

Please help me solve this problem.

Best regards

You are correct. This is one of the cases where AS2005 is different from AS2000.
In your example you have a case of dimension that is based on the non-unique key attribute.
In AS 2000 Analysis Server automatically makes a dimension key unique by concatenating all levels to the key level ( lowest level in the dimension). In AS 2005 this is no longer the case. Although in simple cases (like you have here) you might be little confused at first, the real gain comes in AS2005 being able to implement better performing and scalable dimensions.
To fix the problem in your dimension, you need to define your composite key for your dimension key attribute. Add USER_NAME column to the KeyColumn of your dimension key attribute.

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

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 stops working

We are sporadically seeing analysis services cubes stop responding for no apparant reason. This has happened 3 times in the last 5 days. There are no errors in the events logs, and no dump files to be found anywhere on the server.

Once this happens, If I go into SQL Server Management Studio and try to browse any of the cubes, I get the following error message:

"Unexpected Error: Server: The operation has been cancelled". This message appears under the "Dimension" column heading in the browser pane.

In the detail fields portion of the browser pane is the message:

"The query could not be processed".

We are running Enterprise edition, 64-bit platform, Service Pack 2. It works great most of the time...just don't have any idea how to troubleshoot this further.

Anyone have any suggestions?

This sounds a lot like the automated query cancellation behavior added in SP2 for the case where allocations are failing and the server is unable to shrink memory. Still, we should only hit that case once unless something else has gone wrong in the server. What does the msmdsrv process's memory usage look like when it's failing? I'm interested in process private bytes, and also the MSAS cleaner memory shrinkable and nonshrinkable numbers.

- Brad

|||Thanks for respondibng. I will take a look at the memory usage next time it fails. Most likely in the next day or two.|||

Here's what I found under MSAS memory counters in Performance Monitor. (FYI, total memory on the server is 8GB)

Total Memory:

Avg: 8602630

Max: 8605659

NonShrinkable:

Avg: 8601991

Max: 8604350

Shrinkable:

Avg: 1347

Max: 1445

|||

OK, so that means that the service is in fact out of memory, and for some reason, it's unable to reclaim the memory. This explains the behavior you're seeing, but it's not at all clear why killing sessions isn't making some of that nonshrinkable memory into shrinkable memory.

It might be useful to look at the output of DISCOVER_MEMORYUSAGE to try to characterize the memory in use, but it's likely that I would need more information to diagnose the issue more accurately. It's probably time to open a support ticket on this one, as I would most likely need to see a memory dump to determine where and why the memory is tied up.

- Brad

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 help - Dim builds, but cube fails.

If there are any AS gurus out there, I could use some help. I've been having some problems with particular AS dimension and cubes and it's driving me crazy! It doesn't matter what I do, nothing seems to work.

Anyway, here's what I'm trying to do. I've got a fairly simple dimension. There is a date stored in the dimension that is formatted as an int. The dimension needs to only display AGE, so I cast the int as a date and do a datediff to get AGE. The dimension builds just fine and I get the results I want. My problem is when I add the dimension to the cube. The cube fails to build and I get an error message - "Data source provider error: The column prefix 'MY TABLE' does not match with a table name or alias used in the query." Basically, AS is telling me that the table that is used for the dimension doesn't exist, even though the fact and the dimension are joined properly and I've validated the structure.

I've run a number of queries in QA on the two tables and everything works fine. No funky data issues. I've run the service packs a few times, but that didn't work either. I've tried making a cube that only has the just fact table and the one dimension table, and it still fails.

Basically, I'm out of ideas. Any help that anyone has is greatly appreciated.My first suggestion would be to examine Brett's excellent sticky (http://www.dbforums.com/t1196943.html) with suggestions for getting quick answers. You certainly appear to have done your homework in understanding the problem, but haven't done much to help me understand it!

If that error message is literally what AS reports, I suspect that the problem is the space in the 'MY TABLE' name. If you use characters outside of letters, digits, and underscores in your object names (like your space), then you need to use square brackets [] to surround that name.

-PatP|||My first suggestion would be to examine Brett's excellent sticky (http://www.dbforums.com/t1196943.html) with suggestions for getting quick answers. You certainly appear to have done your homework in understanding the problem, but haven't done much to help me understand it!

If that error message is literally what AS reports, I suspect that the problem is the space in the 'MY TABLE' name. If you use characters outside of letters, digits, and underscores in your object names (like your space), then you need to use square brackets [] to surround that name.

-PatP

Sorry. The table isn't actually called 'MY TABLE'. I was just using that as a generic reference. The real table name is 'F_LOAN'.

Pat, do you have any specifc questions I can answer? Are you pretty familiar with AS? In a nutshell, I have a dimesion that builds just fine. When I add it to the cube, the cube fails to build. I'm guessing that I'm completely missing something very basic or I've got a bug. :eek:

I've added some screen shots of the cube, the error and the dimension. A pic is worth a thousand words, right?|||A little more info...

If I remove the dimension logic that calcs the Loan Age, the cube will build. However, I don't understand how the logic could be a problem when the dim builds fine with the logic. I would expect the dimension to fail if there was a problem with the Loan Age calc logic.|||One more pic. This query is doing what I want the dimension to do.|||Well, I think I've got it solved. I know everyone was waiting with bated breath! :D

Evidently, the cube didn't like having a dim where the dim converted the date, stored as an int in the DB, to a date. Even though the dim was working just fine, the cube somehow couldn't handle it. So, I made a view based on my dim table and did the int-to-date conversion there. Then I used the view as the source for my dim. And...everything worked just fine. I guess I was just asking too much from AS.

Thanks.

Analysis services freezes on Processing a Cube

I have not succeeded in processing my cubes because anaylsis services freezes when ever i run process cube. What could be the reason for this. Thanks in anticipation for your help

Hi!

You should check firstly which step of the processing work you are having problems.

I suggest to trace the SQL sentence executed by SSAS against the source database to see if there exists problems in resolving the query.

Then, if no problems are encountered I would check if it stops when processing a dimension or a measure group, or when calculating the aggregations.

Thanks

Leandro

Analysis Services error

I was able to build SQL 2005 cubes from my project. However, for some reasons, it doesn't work any more, and I have the error below. Anyone can shed some light would be greatly appreciated.

Error 1 File system error: The logical file cannot be found inside the physical file. Physical file: \\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Beacon DW.0.db\Tbl Policy Coverage Data.6.dim\14.ERP Effective Month.Tbl Policy Coverage Data.dstore. Logical file: . Errors in the metadata manager. An error occurred when loading the Coverage dimension, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Beacon DW.0.db\Tbl Policy Coverage Data.16.dim.xml'. Errors in the metadata manager. An error occurred when loading the Production cube, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Beacon DW.0.db\Beacon DW.350.cub.xml'. 0 0

Hi,

It sounds like the file is corrupted. It looks like its only the Coverage dimension, so you may be lucky.

Can the project be built or is the error when you try to deploy the project to the server?

If the error is in the deployment try to deploy to a new database to see if the corruption is on the server itself.

If the error is on the build which tells me something is corrupted in the project files itself do the following.

1. Go to a back up of the project if you have one.

2. If not make a backup of the project now and delete the Coverage dimension from the project to see if you can build it without that dimension.

I hope that helps,

David Botzenhart

|||

It appears that it is one of the xml files for the Coverage dimension was corrupted on the SQL server. I have deleted the Coverage dimension on my project, and it failed with the same error when I rebuilt the cube. Do you know of how to fix the corrupted file on the SQL server?

Mitch Cheung

Analysis Services end-user frontend

Hi,

I've developped a simple testing SQL Server 2005 Analysis Services solution.

I tried with Excel 2003 and pivot tables for accessing the cubes, because end users are very used to that enviroment.

The problem is that selection of dimensions and measures is dificult because there are too many, and not arranged into folders. All attributes of all the dimensions and all the measures are displayed in a -plain- format (as far as I know). That's very frustrating, because is difficult for the user to select the correct dimensions, attributes, and measures.

I tried with XLAddinSetup.msi, but I didn't like it, because I think that isn't so powerful and easy to use than pivot tables.

What can I do?

Do you recommend to use another frontend, not Excel?

Many thanks.

This is a very difficult time to recommend a front-end to Analysis Services given Microsoft's recent acquisition of Proclarity:

http://www.microsoft.com/presspass/press/2006/apr06/04-03ProClarityPR.mspx

There are a lot of good tools on the market, but no-one knows how many will be around this time next year! Obviously Proclarity will be in some shape or form, though it might make sense to wait and see what Microsoft's plans are for it. It's probably a good idea to contact your local Proclarity office and ask them what is going to happen in the short-term; if there's someone from Proclarity reading this, perhaps they can make a public statement here?

Chris

|||

Chris Webb wrote:

This is a very difficult time to recommend a front-end to Analysis Services given Microsoft's recent acquisition of Proclarity:

http://www.microsoft.com/presspass/press/2006/apr06/04-03ProClarityPR.mspx

There are a lot of good tools on the market, but no-one knows how many will be around this time next year! Obviously Proclarity will be in some shape or form, though it might make sense to wait and see what Microsoft's plans are for it. It's probably a good idea to contact your local Proclarity office and ask them what is going to happen in the short-term; if there's someone from Proclarity reading this, perhaps they can make a public statement here?

Chris

Ok, I think I can wait.

On the other hand, I still think that Excel is enough for the moment, but my problem is, as I said before, that accessing to dimensions and measures of OLAP cubes is messy, because the list isn't hierarchy organized in folders.

Does anybody know how to do that?

|||

I don't think you will be able to do this with any current version of Excel, although Excel 2007 will be able to do it.

Chris

|||

As Chris mentions ProClarity might be a good bet for you. However, I can also recommend the Danish front-end TARGIT, which has some very compelling and user-friendly features. You can download a 30-day demo version from http://www.targit.com/Products/Demo_and_Videos/Download_30_days_trial_demo.aspx

TARGIT is able to handle the meta data from AS 2005 and present this in a nice manner.

|||

I also recommend that you look into the upcoming Office 2007 release. Excel has many enhancements specifically targetted at AS 2005.

http://www.microsoft.com/presspass/press/2005/oct05/10-23BiLaunchPR.mspx

|||

If you can't wait for Excel 2007 there are some 3rd party add-ins for Excel currently available:
XLCubed
BIXL
O2OLAP

If you want to move away from Excel and don't like ProClarity look at ExecutiveViewer from www.temtec.com. Have you tried Reporting Services with SSAS? Microsoft is pushing this very hard (but it is more a developer tool than an end-user solution).

Dr Cube

Analysis Services Calculations

Is it possible to group/ arrange Analysis Services Calculations in cubes into separate Folders? In other words similart to measures which can be grouped in to folders I would want to know if we can group these calculated meassures into folders.

Thanks

In the calculations tab you can see a very small properties button, called calculation properties, that you can click on.

This is in BI-Dev Studio.

HTH

Thomas Ivarsson

|||

Great, thanks..

Now how can I control the security of these calculated members. meaning, I do not want the calculated field to be available to a pariculat role?

I can control it at the Measure level that makes this calculated field. in other words I do not want one of the calculated field not visible to a particular Role Member..

|||

Now how can I control the security of these calculated members. meaning, I do not want the calculated field to be available to a pariculat role?

This is not possible in AS2005.

|||

Thanks Mosha, Is there any other work around with which I can make the caluclated fields available/ visible only to a set of users.

I have a calculated field of $'s which I do not want to be visible to all the memebers. It should be visible only to Managers who are members of Managers Role.

|||You should define it as a real member of the dimension then. Since the fact table won't have keys related to that member - after processing it will be empty - so you are not paying anything in the space. Then inside MDX Script you can overwrite it with any MDX expression you want, just like you do with the calculated members. But now since it is a real member, you can secure it in a role.|||

Another option is to add Cell Data security to the measure. The following article shows details of how to add security to only allow users to use a specific measure.

http://msdn2.microsoft.com/en-us/library/aa933600(SQL.80).aspx

This works for calculated members as well.

In the Cell Data tab of the Role editor, tick 'Enable read permissions', and add a statement referring to the measures the user IS allowed to use. This will then disallow access to any other measures.

e.g.

If we have 3 measures:

MemberCount
SalesCount
MemberPay

And want to diallow access to the MemberPay measure for a specific role, we could use the following statement in the Cell Data page:

Measures.CurrentMember.Name = "MemberCount" or Measures.CurrentMember.Name = "SalesCount"

Analysis Services Architecture

We have a large cube (over 500GB) and several other relatively small cubes built using AS 2000. We are now in the process of upgrading the environment. Basically we are going to buy new infrastructure and use AS 2005. And the OS is most likly XP. The large cube is the one that is widely used in the company by at least over 200 people. Small cube has very few users. My question is what is the best architecture we can use

1. Whether to use one large machine and then have one instance of AS. All cubes are to be migrated to this instance. Issue with this option, we can not makesure we give more resources to the important cube.

2. Have multiple instances of the AS running on the server. Use MS Resource manager (new product shipped with XP server) to manage the resources. Has anyone used MS resource manager or aware of any issue with it ?

3. Use clustered environment - I do not know how use this to allocate more resources to the important cube. Any thoughts ?

Much appreciate, any pointers on what we are trying to achieve here .

Thank you

It is hard to make any suggestion based on short description only.
But if you are going to have several smaller cubes, queried not often, it probably Ok to consolidate them with the bigger cube on the same server.
Your risks are whenever smaller cubes are queried, the bigger cube will see some slowdown. Again, you might be Okay with consolidating them together. Just make sure you give it excess capacity. Try it, test it.

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

Analysis services and traditional reporting

The idea of defining cubes directly on any relational database is very nice, especially with the possibility of giving "friendly names" to facts and dimensions.

I've read in a few places that analysis services provides "... a unified and integrated view of all your business data as the foundation for your traditional reporting, OLAP analysis and data mining."

I've tested the OLAP and the data mining aspects but I have yet to see how I can use a data source view defined in analysis services (without any cubes associated with it) to do traditional reporting.

I would like to use the "abstraction layer" that the analysis services data source view provides but in a traditional report such as a list of customers with names and addresses but no aggregations, so no cubes.

Is it possible to do "traditional reporting" through a data source view in analysis services (with its friendly names and regional attributes) without defining a cube? If so, how?

Thanks in advance.

Hi Gilles,

You could create a Report Model based on your DSV.

HTH,

Eric

|||

Thanks for the quick response.

I had noticed that a model would help and succeeded in creating a report from report services that uses the model. However, would I be able to use that same model has a data source from something else other than reporting services such as from within Excel?

What I would like to do is use the data source views in analysis services (again because of the friendly names and so forth) as my one source for all my reporting needs: OLAP, data mining AND traditional reporting, to be used by reporting services AND other reporting tools.

|||

Hi,

Report Model will help you with Reporting Services and Report Builder but I doubt you will be able to use the Report Model from Excel...

When working with Excel, I usually create a datasource to the cube itself.

Eric

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

Analysis Services 2005 Cubes

Hi,

I had designed the DW database like

1. For each report one fact and some dimensions are created

2. Now i want to create a cube. while creating a cube i got some doubts. i want to know that what is the better way to create a cube

2.1 Whether i have to use one cube for one report

or

2.2 Whether i have to create only one cube and i have to create different measures

Doubts:

1. If i go to option 2.1 then it takes more memory? (because each cube stores the data in structres)

2. if i go to option 2.2 then it hits performance? (because several users access several reports using the same cube)

Can you please help me in this.

Thanks

Dinesh

If you have common structures between your reports you will probably get much better performance and memory useage from haveing multiple measures in a single cube rather than a cube per report. The indexes and caching could all be shared between the measures increasing the performance.|||Thanks.

Analysis Services 2000 with SQL Server 2005

We have an application that uses Analysis Services 2000 Cubes with SQL Server 2005. We have a new client who has SQL Server 2005 on their server. Since our application requires Analysis Services 2000, I would like to know if there is any way they can get Analysis Services 2000 without buying SQL server 2000.

Hello! AS2000 and SQL Server 2000 are bundled. You run AS2000 on your SQL Server 2000 licence.

Answere: No!

HTH

Thomas Ivarsson

|||

Thank you, that helps. I was thinking that maybe they have some components for Analysis Services 2000 like they have for DTS 2000 in SQL Server 2005. The client was complaining about having to buy both SQL Server 2005 and SQL Server 2000. I will tell them that they have to do that.

Thanks,

M.Kundeti