Re: Analysis Manager Operations extremely slow
From: Dave Wickert [MSFT] (dwickert_at_online.microsoft.com)
Date: 09/20/04
- Next message: Dave Wickert [MSFT]: "Re: Obtaining name of member from given cell"
- Previous message: Dave Wickert [MSFT]: "Re: Scripts in Analysis Services?"
- In reply to: MurthyJ: "Re: Analysis Manager Operations extremely slow"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 20 Sep 2004 09:36:22 -0700
WOW. Lots of issues. Let's take them in order.
1) slowness in AM. The issue that I was referring to revolves around the
total number of objects which DSO has to create when it is asked to
instantiate an database. So you will run into it with hundreds of roles,
hundreds of partitions, hundreds of *whatever*. There is really no "fix" for
it -- it is just the way that VB works. You can minimize the impact by
separating your objects into different databases or re-designing your system
to workaround. BTW: In SQL Server 2005, both RDBMS SMO and Analysis Services
AMO object models (written in managed code), now have two modes of
operation: a "light-weight" mode where just the name is exposed (when you
first form the collection); and a "heavy-weight" mode when you start asking
for properties. Our hope is that this will translate to seeing the
management utilities run quickly even if you have thousands of objects; and
delay the cost of having to instantiate subobjects until you actually need
it.
2) testing domain authentication. The trick that I use is to create a remote
file share on another computer which is configured to require
authentication. Then try to map a drive to it on your Analysis Server. The
user authentication used to access a remote file share is not unlike what
Analysis Services user in DSO. There is no way to turn off this
authentication.
3) cube editor slowness if connecting to Oracle. Yes, that could be impacted
as many of the table structures are visually drawn by querying the data
source for metadata.
4) having a cube go into an "unprocessed" state. I tried this with Foodmart
and it didn't happen. For example, I could change the default member of the
Gender dimension to "M" and still browse the Sales cube. You must have also
reprocessed the dimension. If you reprocess a non-changing dimension
(whether or not there is a change or not), then any cube/partition which
uses that dimension is forced into the "unprocessed" state. Might that be
what happened?
-- 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:D564116C-F116-4A55-A135-4D6BF65F2E53@microsoft.com... > 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. > > > > > >
- Next message: Dave Wickert [MSFT]: "Re: Obtaining name of member from given cell"
- Previous message: Dave Wickert [MSFT]: "Re: Scripts in Analysis Services?"
- In reply to: MurthyJ: "Re: Analysis Manager Operations extremely slow"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
