Re: Analysis Manager Operations extremely slow

From: MurthyJ (MurthyJ_at_discussions.microsoft.com)
Date: 09/16/04 

Date: Thu, 16 Sep 2004 14:11:01 -0700

Hello Dave

It's quite a coincidence for me as I just went through some of your talk on
TopTenTips only yesterday. It was quite helpful, thanks.

- We haven't yet created any roles yet although we planned to have them.
- Each of the cubes have 12 partitions with monthly slices. Quite a few on
the whole that use the same shared dimensions. But by no means hundreds of
objects.
- We are running AM while being on the server, not remotely.
- Finally, to the last question, Yes we are using domain authentication. If
Is there an easy way to test it, say by giving all access to everyone role so
that there is no authentication that would be needed while requesting edits?

Counting multiple hierarchies as dimensions, we 10 regular dimensions and 6
virtual dimensions. In order to support these virtuals, we have defined many
member properties.

The first time we bring up the cube edit it take 1 min 20 secs, but now
think that's because of the Oracle connection and the building of the schema
page with a fact table and lots of dimensions.

However, any change to a dimension (such as a default member on time
dimension), a) take a long time, b) very surprisingly, it's changing the
status of the cube to be 'Unprocessed' without prompting. I was wondering if
it's a bug. I tested this behaviour on a separate test cube and it always
prompts to confirm that the cube will have to be reprocessed if we go
forward with the change. The test cube has only one partition whereas the
real one has 13. This is a related but different problem from the one I
originally reported.

Thanks for taking time.
Murthy

"Dave Wickert [MSFT]" wrote:

> From the sizing you've described so far I am very surprised that you are
> seeing AM slow down like that. Are there any places where you have hundreds
> of objects, e.g. you've created hundreds of security roles; or hundreds of
> partitions in one of your cubes?
>
> A couple of other questions.
> Are you running AM remotely or on the server itself?
> Are you using a domain account when running AM or local administrator?
> I ask this because one of the other possible sources of the "slowness" could
> be domain authentication.
>
> --
> Dave Wickert [MSFT]
> dwickert@online.microsoft.com
> Program Manager
> BI SystemsTeam
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "MurthyJ" <MurthyJ@discussions.microsoft.com> wrote in message
> news:2D320B0D-3DB9-4484-9F42-F2DDA1F4C216@microsoft.com...
> > Hi
> >
> > We are running SQL Server 2000 Analysis Services SP3, currently in
> > development. Our data warehouse source is in Oracle 9i. We have about five
> > cubes each with about 9 regular dimensions, 1 virtual dimension. One of
> the
> > regular dimensions has five different hierarchies, three of them defined
> as
> > virtual. Each of the cubes have many partitions. A lot of these dimensions
> > are defined as shared. The cubes themselves are not bad for browsing,
> > although we haven't loaded all of the data yet. The dev machine is running
> > Windows 2003 Server, has 2GB of memory.
> >
> > So, we have a pretty sizable amount of information that is read from and
> > written to the repository whenever we attempt to edit a cube or a
> dimension.
> > However, things have gotten extremely slow now, Just clicking edit on a
> cube
> > takes two of minutes or more before it brings up the window. Some of the
> > changes we may do on a shared dimension (such as setting a default member
> for
> > time) are making the analysis manager to simply hang (never returns).
> >
> > I checked the mdb repository size to be close to 10MB. Hoping that it may
> > improve things, I migrated the repository to a local SQL Server instance
> > blank database (not msdb). It has not improved anything at all.
> >
> > I am hoping someone can be give me some pointers to look at other things
> to
> > fix these problems.
> >
> > I appreciate your suggestions/ideas.
> >
> > Thanks
> > Murthy.
>
>
>

Relevant Pages

  • Re: Fairly Large Cube-Performance Improvement recommendations
    ... move) if the cube has large number of dimensions. ... We have 30 dims in the cube and the storage design wizard taked about 45 ... > 1) execute a fairly long query, let's say one that runs for 20 seconds. ...
    (microsoft.public.sqlserver.olap)
  • Spoof shared dimension for virtual cube
    ... just getting back into Analysis Services again, ... Two cubes, ... Cube1 has 10 more dimensions, ... I'm sure it's possible to get this result with MDX (which I'm still not ...
    (microsoft.public.sqlserver.olap)
  • How to create a "Stock" cube ?
    ... and dimensions, but now I must take it one step further and create what I ... The cube will have dimensions like: ... Itemnumber, Itemgroup, Stocklocation, Blocked, Customs classification ... Thus selecting a particular itemnumber ...
    (microsoft.public.sqlserver.olap)
  • Re: Cube very slow to querying when in the middle of a process
    ... how many partitions are you dealing with if you add new ... >> Well, when incrementally updating dimensions, AS2k will automatically ... >> refresh the cube partitions below it which may have an impact on this. ... >> point where you may want to have reporting OLAP cubes and processing OLAP ...
    (microsoft.public.sqlserver.olap)
  • Re: changing MOLAP ro ROLAP
    ... with few partitions (the ones that are created ... >automatically, nothing I made), and few dimensions. ... > I want to change it to ROLAP. ... > management studio and change each partition individually, and the cube, ...
    (microsoft.public.sqlserver.olap)
Loading