AS2005: MemberUniqueNameStyle = NamePath

Hi,

After having performance problems we implemented the Attribute Relationship in the cubes, but 99% of the hierarchies do not have a unique key for the levels. The suggestion from MS was to create the key as a collection from the parents to the level itself. Isn't it equivalent to MemberUniqueNameStyle = NamePath?

MemberUniqueNameStyle = NamePath (Analysis Services generates a compound name consisting of the name of each level and the caption of the member.) http://msdn2.microsoft.com/en-us/library/ms174900(SQL.90).aspx.

Thanks.

No, MemberUniqueNameStyle simply controls how Analysis Services generates the unique names of members - it doesn't affect the members or attribute relationships themselves. By using composite keys for your attributes, however, you will see changes in unique names generated using the default key-based algorithm.

HTH,

Chris

AS2005 Slow initial cube opening

Hi

We are having a problem in our AS2005 cubes that the initial openeing of the cubes is exceptionally slow. When the user connects though ProClarity, or I open the cube in the designer, the first report or first display of data takes about 2 minutes to return. Once it is open, the next query that you run is fine. If you exit the application, and go back in, you have to wait again for the first report.

This is very annoying to the users and frustrating for me as I cannot see anything that could be causing this.
We are running Windows Server 2003 x64 edition SP1 with SQL AS2005 x64 SP1.

Has anyone seen this before or can point me in the right direction where to look to solve this issue.

Thanks
Michael

Are you running ProClarity Professional directly againts the cube or are you using http access though ProClarity Analytics Server?

Caching of data will take some time, after a full process or restart of the server(the first user will have to pay the price of waiting) but two minutes seems to be to much. My customer run on a less powerful server than yours and the client access is though Citrix. In the morning our responstime is 30 sec to one minute.

Can you run profiler on SSAS2005 and see what happens during these two minutes?

Regards

Thomas Ivarsson

|||

we are accessing the cube directly, not using http.

The issue is that The does not only occure on the first query of the morning after a process, but on every new login during the day. I did a trace on the AS DB and there were a lot of "Discover Begin" and "End" EventClass events occuring before the MDX query was run. The were doing a lot of "10 - MDSCHEMA_MEMBERS" EventSubclass.

Could this be caused by the way the Dimensions are setup?

Thanks

Michael

|||

It can be something regarding dimension design. Do you have attribut relations defined in user hierarchies and are you using rigid relationship types? Check this Blog for more information: http://blogs.conchango.com/christianwade/archive/2006/07/12/AS-2005-Optimization-Experiences.aspx

Regards

Thomas Ivarsson

|||

Thanks, we are busy looking at a redesign of this Cube. This was designed on the Sept CTP last year, before there was any blogs about optimisations or anything like that, hence the bad Dimension design. I really just wanted to ensure that there wasn't something else out there that could be causing this.

Regards
Michael

AS2005 Projects best practice?

Hi,

I'm currently looking for the best practice to set-up my AS2005 projects. Any help would be appreciated.

We currently need to build a lot of cubes with AS2005 and we have a lot of AS2005 developers working on this... Here are some points to keep in mind:

Cubes will share dimensions

Some cubes will have their exclusive dimension

multiple developers working at the same time on the projects

Here are my options:

Create one big project to hold all my cubes and dimensions

This is easy to set-up and manage

Might have some problems having multiple developers working at the same time

What if a dimension is used in multiple cubes but we need to define distinct hierarchies for every cube?

Create on project for the common objects (dimensions) and one project per cube

I have the feeling that this would be hard to manage

How can I link the dimensions from the common objects project to our different objects?

What happens if a developer modify a dimension in the common object project ? would all the cubes using it be automatically updated?

Create one project per cube

This is easy to set-up and manage

Modifying a dimension may require to modify all projects (major overhead) if used in multiple cubes

What is your take on this and how do you guys manage your AS2005 projects?

TIA,

Aiwa

I would add

that you also need to think about DSV objects. Are those developed one time

before all dimensions and cubes are started being developed or even DSVs are

going to be incrementally developed?

Eventually, are those going to be deployed as part of one database?

Are you planning to do test driven development? Will your test development be

performed in parallel with the solution development? If yes, does it mean that

any incremental work done by one developer should result in fully deployable

solution for all the cubes as part of one database, which is going to be automatically

tested on regular basis?

|||

Hi Andrew,

you are right I forgot DSV. Those should be developed before developing cubes and dimensions but change to DSV are likely to happen in the long run.

Deploying to one or more database is not a show stopper for us right now.

Yes, any incremental work done by one developer should result in a fully deployable solution.

Regards,

Aiwa

|||

Personally

i have never seen a group of people actively developing the same cube in

parallel and using a source control system. All files participating in the AS

project are in XML format. So it is possible to resolve conflicts using the

source control system you have. Some of the conflicts will be resolved

automatically. Some will have to be solved manually. But you need to make

experiments and see how it goes.

I think it

will be easier if different people work within the same project but each object

is changed only by one developer at the same time. For instance, today I would

work on dimension A and I know that nobody will touch A today. Tomorrow

somebody else could work on A but I will not. This would reduce the number of

conflicts.

Also, I do

not see big amount of manual work to do when designing a dimension or cube. The

work is mainly mental one. So, if resolution of conflicts becomes a problem

then it is possible for 2 or 3 developers make their local experiments and,

having tested locally and succeeded, get together in one room and combine their

efforts by recreating the object from the scratch while picking up the good

steps from each solution. Yes, one of the developers will have to repeat the

steps of 3 people once again, but it will not be mental work requiring a lot of

energy, but rather mechanic work.

I think you

need just try to follow your processes for a week and see how it goes.

AS2005 Cube Processing

I have problems with processing cubes on AS2005. I get this error message while processing:

Memory error: Allocation failure: Not enough storage is available to process this command. Error in the OLAP storage engine.

This machine has 6GB of memory and once it hits 3.5GB the processing stops. AWE is turned on.

I would appreciate, if anyone can help on this.

Thanks,

Gopal

Analysis Services is not AWE aware. So it can only use 2GB on a 32bit box (3GB if you have /3GB switch in boot.ini). Looks like the processing operation is running out of memory.

What are the number and sizes of dimension and partition tables? Do you have aggregations? Look at the processing log (in the process dialog) and find out what is the operation that causes out of memory - dimension processing, partition processing, building aggregations, etc.

This could also happen due to excessive parallelism. Try processing the dimensions and partitions individually.

|||

Thanks for your reply.

I managed to process the partitions and dimensions individually. That worked.

Gopal

|||

Hi.

Just a short question:

Is it really true that SSAS can use only 2GB RAM on a 32bit system? The technical reference for SQL Server 2005 Standard and Enterprise states that SQL Server can use the amount of RAM supported by the operating system which is 4GB for a Server 2003 Standard edition and 32GB for a Server 2003 Enterprise edition, both on 32bit systems. Does this apply only to the SQL Server Database Engine then and not the Analysis Services Server?

Regards

Kjetil

T.K. Anand wrote:

Analysis Services is not AWE aware. So it can only use 2GB on a 32bit box (3GB if you have /3GB switch in boot.ini). Looks like the processing operation is running out of memory.

What are the number and sizes of dimension and partition tables? Do you have aggregations? Look at the processing log (in the process dialog) and find out what is the operation that causes out of memory - dimension processing, partition processing, building aggregations, etc.

This could also happen due to excessive parallelism. Try processing the dimensions and partitions individually.

|||

In the 64-bit versions of Analysis Services...has this memory use limit been removed?

...cordell...

|||Yes - 64bit version doesn't have these limitations.

AS dont process cubes

Hi,

my english is very bad, so i try to explain....

We have a MOLAP structure and several jobs executing SSIS packages. This jobs yesterday and today don′t finished , just stay executing without error messages and the cubes not up to date. I stop and restart AS instance and the cubes is up to date. But i can not do this every time. I see in Event viewer and dont have any error messages. I see in other forun to disable proactive caching...is this correct ?...wich problems i can have doing this ?...and what i can do to resolve this problem ?..

Thanks

To clarify, you have a cube with MOLAP partitions. Do you have proactive caching enabled? Are you using SSIS to process your cubes?

Thanks,
Bryan

|||

Yes..i have a cube with MOLAP partitions, but proactive caching is disabled and SSIS process the cubes.

But in some cases only stop and restart AS to process de cubes, and the job with ssis package stay runing ..stay runing..stay runing and dont do anything....i start the profile and the command to begin the cube shows up, but stay...stay..stay....can you help me ?....thanks a lot

|||

It's very hard to say what is happening with the cube. So, I would recommend some experimentation. Does the cube process when done manually in BIDS or in SSMS? Do parts of the olap database process while other parts hang?

There have been some posts and blogs indicating this happens when a resource limitation is being hit. I would think you would see something in the error logs regarding this, but maybe not. One of the recommendations from this was to break apart the processsing job into a series of steps. This might help as well.

Another poster on SQL Junkies is complaining of the same issue. You may want to reach out to him/her to see if the problem has been resolved:

http://groups.google.com/group/microsoft.public.sqlserver.datawarehouse/browse_thread/thread/77d4f542ff7d288a/307dfc5dc1bcdfc3#307dfc5dc1bcdfc3

Finally, I would recommend making sure you are on SP2.

Good luck,
Bryan

AS connection problems from Excel

I have problems to create connections to some of my cubes from Excel.

Configuration

Client- XP professional with Excel (2007, B2TR) and SQL 2005 SP2 TR client part's installed.

Server - Windows 2003 R2 with SQL AS 2005 SP2 TR.

Logged in as administrator with same password on client as server.

There are no rules configured for any of my databases.

When I try to create a new Analysis connection from Excel there is no cubes in the list of available cubes for some of my databases.

It works great for two om my databes but not for the others. I have compared my databases but I can't find anything that is different. And it works great to browse all my cubes from SQl Server BI Development Studio from my client.

Any idea?

Thanks,
Christer

If the cubes are deployed and processed, then you suppose to see it in the Excel 2007 beta 2. Can you be more specific in your question, I might can help as I am using the same configuration you have.

IMRAN

AS / Creating cubes

Hi,
I have started to learn Analysis Services. I did the tutorial step by step,
but the last step "create the 'Sales' cube" doesn't work. I get the error
message "Unable to create cube 'sales'" - Unspecified error. It doesn't even
work with the manuelly in footmart creatd cube. What is wrong here? I have
also installed sp3a.
My other question is that is it possible to create diagramms in AS? If no,
how do you create you database diagramms?
Many thanks in advance
Do you belong to OLAP Admin group? Do you have write permission on OLAP
cube data directory?
Eric Li
SQL DBA
MCDBA
Stefan wrote:

> Hi,
> I have started to learn Analysis Services. I did the tutorial step by step,
> but the last step "create the 'Sales' cube" doesn't work. I get the error
> message "Unable to create cube 'sales'" - Unspecified error. It doesn't even
> work with the manuelly in footmart creatd cube. What is wrong here? I have
> also installed sp3a.
> My other question is that is it possible to create diagramms in AS? If no,
> how do you create you database diagramms?
> Many thanks in advance
>

AS / Creating cubes

Hi,
I have started to learn Analysis Services. I did the tutorial step by step,
but the last step "create the 'Sales' cube" doesn't work. I get the error
message "Unable to create cube 'sales'" - Unspecified error. It doesn't even
work with the manuelly in footmart creatd cube. What is wrong here? I have
also installed sp3a.
My other question is that is it possible to create diagramms in AS? If no,
how do you create you database diagramms?
Many thanks in advanceDo you belong to OLAP Admin group? Do you have write permission on OLAP
cube data directory?
Eric Li
SQL DBA
MCDBA
Stefan wrote:

> Hi,
> I have started to learn Analysis Services. I did the tutorial step by step
,
> but the last step "create the 'Sales' cube" doesn't work. I get the error
> message "Unable to create cube 'sales'" - Unspecified error. It doesn't ev
en
> work with the manuelly in footmart creatd cube. What is wrong here? I have
> also installed sp3a.
> My other question is that is it possible to create diagramms in AS? If no,
> how do you create you database diagramms?
> Many thanks in advance
>