(newbie) updating dimensions

Hi,

I apologise if I have missed something obvious here, but I am having a problem finding how to set up a cube to update dimensions automatically when data is changed in the source database. From what I can see in Books Online, dimension writeback does not do this because the update is initiated by the user. I have got storage settings for measures figured out, and we are using low-latency MOLAP. In BIDS, there does not seem to be a similar way to set up the dimensions to be updated. Can anyone advise me on how this is done?

Any help much appreciated.

Hello. The feature that you are looking for is maybe "proactive caching".

My advice is to be very careful about this feature. You will need to be an experienced SSAS2005(Analysis Services 2005) consultant to set this up properly.

I also think that it is only a feature in the enterprise edition.

The budget version is to have an SSIS-package(Integration Services) that detects changes in the database and automatically start an SSAS2005 processing task after that.

Looks like you are planning to build a cube on top of an OLTP-system(transaction system) and ignore the option to build a data warehouse/ data mart?

I always recommend a datamart separate from the source system as the minimum requirement to build a good analytic solution.

HTH

Thomas Ivarsson

Have a look at proactive caching here:

http://www.microsoft.com/technet/prodtechnol/sql/2005/rtbissas.mspx

|||Thanks for your reply, Thomas. We already have our options worked out re: proactive caching as far as measures are concerned, where the BIDS UI is perfectly clear. It is for dimensions specifically that we are having trouble. Can anyone shed any light on that specifically? Thanks very much for any help.|||

Hello again Ed.

I have not used this feature myself and have no plans to do it in the near future.

You have all dimensions as ROLAP?

I you buy "MS SQL server 2005 Analysis Services"(SAMS) there is almost a whole chapter(24) with recommendations of how to configure this properly.

HTH
Thomas Ivarsson

|||Thank you Thomas. I'll definitely take a look at that book.sql

(Harder?) cube query & design question

Hi,

I have an MDX query (and worst case a cube design) problem that I haven't been able to solve, any ideas on how to go about this? Here's a simplified description, starting with the

Dimension & Attributes

* We make phone [Call]s.

* In each [Call], and for a number of [Product]s we ask a number of [Question]s.

* Each [Question] results in an [AnswerText]. These also reside in a user hierarchy [Answer Dimension].[Q and A].

* All of the above are attributes in the [Answer Dimension].

Facts

* Measures.[Answer Count], which at the granular level is always one, i.e. each fact records that we recieved a single [AnswerText] to a single [Question] about a single [Product] in a single [Call].

* Measures.[Call Count], which is the number of [Call]s made.

NB: We have thousands of different questions and answers, so surfacing each individual question and answer as a measure is not an option.

Queries & issues

* Counting the answers to a single particular question is easy:

SELECT
Measures.[Answer Count] ON 0
FROM cube
WHERE ([Answer Dimension].[Q and A].[Question].&[What color is it?].&[Blue])

* What I can't figure out is how to get Measures.[Answer Count] for multiple simultaneous questions, i.e.:

"For how many products and calls are &[What color is it?].&[Blue] AND &[What shape is it?].&[Round]"

I've tried (unsuccessfully) the following:

1) Various ways that equate to doing an intersection between the first and the second question. This fails since it returns the empty set - a single fact Measure.[Answer Count] only correspond to a single question.

2) Creating sets at the [Answer Dimension] leaf level where [Answer Count] = 1, and counting the number of tuples in the set. Although this looked promising it still failed me, the dimensionality either didn't allow combining the two questions or didn't slice the facts at all when say using a Filter() to combine the sets, even when using two different attribute to specify the answers.

3) Aggregating [Answer Count] to the set {[Call] * [Product]}, and Filter() where both questions have [Answer Count] >= 1. Again promising, but can't figure out the syntax to use.

Big Questions

* Is 2) or 3) above doable at all? What is the rough syntax needed?

* Is there a better (working!-) way to query this cube?

* Is there a better way to design the cube for answering these types of combined questions (remembering we have thousands of distinct questions and answers, and new ones get added over time, and a total of millions of facts)?

Any and all suggestions Much Appreciated!

Kristian

Very complex problem. My question is simply if you have tried data mining and a decision tree model on this problem? Is DM not an option?

Regards

Thomas Ivarsson

|||

Assuming that the question: "For how many products and calls are &[What color is it?].&[Blue] AND &[What shape is it?].&[Round]" refers to counting product/call combinations, cascading NonEmpty() might compute what you're looking for, like:

Count(NonEmpty(NonEmpty({[Call].[Call].[Call] * [Product].[Product].[Product]},

{[Answer Dimension].[Q and A].[Question].&[What color is it?].&[Blue]}),

{[Answer Dimension].[Q and A].[Question].&[What shape is it?].&[Round]}))

|||Yes! This gives the right total on my mini test cube. One (hopefully simple) follow-up question:

The calculation now happens at the right

[Call].[Call].[Call] * [Product].[Product].[Product]

level. How do I write the query so that I get the Count in a colum, and the list of Products on the rows? I.e.

Occurrences
Car 2
Bowl 1

For instance, this won't work:

WITH
SET MySet AS
[... Deepak's code from above ...]
MEMBER Measures.Occurrences AS
Count(MySet)
SELECT
[Product].[Product].[Product] ON 0
FROM cube

since it will give all Products the same total. Ideas?

Many thanks!

Kristian

|||

Hi Kristian,

To your query above, maybe you can add "Existing", to select only relevant tuples for each cell context:

MEMBER Measures.Occurrences AS
Count(Existing MySet)

|||Many thanks Deepak, EXISTING works for small sets, except:

When using large sets, the SSAS service crashes when running the above query. Any ideas on how to make the above query not crash the service, either through increasing limits on the SSAS instance or optimizing the query itself, any ideas?

Kristian|||

Kristian, unfortunately I haven't done much tweaking of memory settings for AS 2005 - maybe someone else can comment on this?

One alternative, which may save memory but be much slower, is to avoid creating the cross-joined named set. So, the Occurrences calculated measure could be directly defined as:

Count(NonEmpty(NonEmpty({(Existing [Call].[Call].[Call]) * (Existing [Product].[Product].[Product])},

{[Answer Dimension].[Q and A].[Question].&[What color is it?].&[Blue]}),

{[Answer Dimension].[Q and A].[Question].&[What shape is it?].&[Round]}))

sql

"Total" and "GRAND TOTAL" are incorrect

Hi all,

When browsing my cube i get the correct data per enitity, but incorrect data in the "total" and "grand total". what happens acutally is that i'm getting the same value of the "all" member in the totals of all the levels of the hierarchy that i'm slicing the cube with.

For example, if the hierarchy is as follows: A-->B-->C, then i'm getting the below:

A B C

a1

b1 10

b2 20

total 800

Grand total 800

i suspect that it is related to the attribute relationships which i have defined, where the aggregation of the data is being performed on

mmmh ...

yes I'm quite sure there's a problem with your attribute relationships.

Try to check all the relationship in your hierarchy.

|||How are your attributes related? It should be something like C has a relationship to B, and B has a relationship to A.|||

Hi,

Are the attribute-key relationships correct?

or

Do you got some NULL-key-columns in your fact-table !?
the grand total maybe sums up also the Unknown data ?

Kind regards

"Server: The operation has been cancelled." When Processing Cube

There are several other posts around this but none really seem to point to a solution. Here is my situation:

1. We've been running in production with SP1 of Standard Edition with no problems since October (4GB, Windows Server Standard, 4 fact tables, 20 million rows in largest fact)

2. I applied SP2 to development last week. While working in dev I saw the "Server: The operation has been cancelled." error for the first time while I was making some dimension changes (i'd never seen this error in SP2). I tried to process the cube several times and it always errored this way. I backed out my changes, processed the cube and all was well. I re-applied some of my changes and all was still well. I decided to upgrade prod since I thought the problem at this point was some bad configuration of the dimension.

3. We upgraded two servers in prod with the exact same hardware configuration, but with SP2 now and Standard edition (NOTE: The cube structure was not changed). We've been processing fine since Monday's load until last night. Last night one of the servers failed during the cube processing step with the "Server: The operation has been cancelled." error.

4. I've got the process running again after restarting the analysis services process...only thing I could think to try right now. Hopefully it works...i didn't see any posts with any type of guidance that might help

Has anyone else seen a problem where what worked in SP1 stopped working in SP2? Are most people running SP2 using the most recent patch rollup? Should I move to that? Any hope this issue was solved in one of the patch rollups?

Thanks.

Sounds like you might have a commitTimeout set, if you are seeing these operation cancelled errors from from the processing task. As far as I can tell the default setting for ForceCommitTimeout was changed in SP2, but I did not think there was one for the CommitTimeout. See this post for more information on these two settings http://geekswithblogs.net/darrengosbell/archive/2007/04/24/SSAS-Processing-ForceCommitTimeout-and-quotthe-operation-has-been-cancelledquot.aspx|||

Thanks for the response. I had already tried that with no luck based on other posts I've seen. I saw one posting where the person said they have to restart the services every night. I just set EVERY timeout in the properties (by choosing advanced) to 0...I just had a Cancel Operation in dev again. I'll try it with all the timeouts set to 0 to see what hapepns.

Thanks.

|||This problem is getting really annoying. On one server it has never happened, on another it happens about every other day. If I restart the service then the process runs through. Note that in dev restarting the service doesn't always fix it...sometimes it just takes trying to run it multiple times. Something in SP2 broke SSAS processing...I never had a single problem in SP1.|||

I'm surprised no one else is seeing these issues with SP2. I had no issues with cube processing until I applied SP2. I just got a different error. Note that so far in every case if I just restart the service then the next cube load works fine. Is there any other tracing I can turn on to help identify what the issue is?

OnError,DWS569794SQL2,NT AUTHORITY\SYSTEM,Analysis Services Processing Task,{F9170A49-2D23-4DA6-962C-CD55A57C191A},{1E79FD64-2D1F-48A8-910B-30501E61BF0E},7/26/2007 7:08:48 AM,7/26/2007 7:08:48 AM,-1056964601,0x,Internal error: The operation terminated unsuccessfully.
OnError,DWS569794SQL2,NT AUTHORITY\SYSTEM,master,{F9625461-CC09-4D2F-A3DE-6B64B9D0E230},{1E79FD64-2D1F-48A8-910B-30501E61BF0E},7/26/2007 7:08:48 AM,7/26/2007 7:08:48 AM,-1056964601,0x,Internal error: The operation terminated unsuccessfully.
OnError,DWS569794SQL2,NT AUTHORITY\SYSTEM,Analysis Services Processing Task,{F9170A49-2D23-4DA6-962C-CD55A57C191A},{1E79FD64-2D1F-48A8-910B-30501E61BF0E},7/26/2007 7:08:48 AM,7/26/2007 7:08:48 AM,-1056767999,0x,Memory error: Allocation failure : Not enough storage is available to process this command. .
OnError,DWS569794SQL2,NT AUTHORITY\SYSTEM,master,{F9625461-CC09-4D2F-A3DE-6B64B9D0E230},{1E79FD64-2D1F-48A8-910B-30501E61BF0E},7/26/2007 7:08:48 AM,7/26/2007 7:08:48 AM,-1056767999,0x,Memory error: Allocation failure : Not enough storage is available to process this command. .
OnError,DWS569794SQL2,NT AUTHORITY\SYSTEM,Analysis Services Processing Task,{F9170A49-2D23-4DA6-962C-CD55A57C191A},{1E79FD64-2D1F-48A8-910B-30501E61BF0E},7/26/2007 7:08:48 AM,7/26/2007 7:08:48 AM,-1054932978,0x,Errors in the OLAP storage engine: An error occurred while processing the 'Tracking F' partition of the 'Tracking' measure group for the 'Warehouse' cube from the Cube database.
OnError,DWS569794SQL2,NT AUTHORITY\SYSTEM,master,{F9625461-CC09-4D2F-A3DE-6B64B9D0E230},{1E79FD64-2D1F-48A8-910B-30501E61BF0E},7/26/2007 7:08:48 AM,7/26/2007 7:08:48 AM,-1054932978,0x,Errors in the OLAP storage engine: An error occurred while processing the 'Tracking F' partition of the 'Tracking' measure group for the 'Warehouse' cube from the Cube database.
OnError,DWS569794SQL2,NT AUTHORITY\SYSTEM,Analysis Services Processing Task,{F9170A49-2D23-4DA6-962C-CD55A57C191A},{1E79FD64-2D1F-48A8-910B-30501E61BF0E},7/26/2007 7:08:48 AM,7/26/2007 7:08:48 AM,-1054932986,0x,Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
OnError,DWS569794SQL2,NT AUTHORITY\SYSTEM,master,{F9625461-CC09-4D2F-A3DE-6B64B9D0E230},{1E79FD64-2D1F-48A8-910B-30501E61BF0E},7/26/2007 7:08:48 AM,7/26/2007 7:08:48 AM,-1054932986,0x,Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.

|||

In the errors you have, it seems that this is the root cause:

Memory error: Allocation failure : Not enough storage is available to process this command

In case you have large dimensions, a common solution for out of memory errors is to reduce the parallelism for processing dimension attributes. There is the server property CoordinatorExecutionMode; the default is -4 which means that server will process 4 x NumberOfProcs dimension attributes in parallel (so if you have many attribute members, processing them in parallel, even for a single dimension, will lead to out of memory). Try setting it to 1.

Adrian Dumitrascu

|||I've been having the same issue with two individual servers both running SP2, a third server running SP1 will still build the exact same cube without fail.

On both SP2 servers the cube simply hangs after reporting a couple of attribute key not found errors- even running a trace reveals no actual errors or further progress from this point and any attempts to stop the processing short of restarting the SSAS service fail.

I've tried changing ForceCommitTimeout and CoordinatorExecutionMode in vain.
|||Getting exactly the same problem. Applied SP2 and the cube starts processing but never completes with no obvious errors. Desperate for a solution.....

|||

Adrian, your response is not the answer. There are problems with SP2 that did not exist in SP1. I do have some updates.

1. I did try setting the flag to ignore the memory errors. I believe that this did make a difference.

2. I'm not completely sure about #1, because I also made a big cube change as well. A long time ago I had built a degenerate dimension as I needed to quickly implement some functionality. I was suspecting that this was what was causing the cube processing errors. I built a new dimension and updated by ETL to create a standard dimension to replace the degenerate dimension. Once this was done (crossing my fingers) the cube has built without any issue.

For the other people posting, are you using degenerate dimensions? Have you tried setting the MemoryLimitErrorEnabled to false?

Microsoft readers, please note the common thread though, these issues did not exist in SP1.

|||

I believe this error is due to lack of memory, either from lack of memory on your machine, or (more likely) from the 32-bit platform itself. To the best of my knowledge the 32-bit versions of analysis server can only utilize a maximum of 3 GB of memory, which includes page file (virtual memory) usage. In my own experience, these errors occur when I am processing a large cube or dimension and the memory usage (combined RAM and virtual memory) reaches about 2.5GB. I think setting the MemoryLimitErrorEnabled to false helps greatly, as does setting processing operations to run in parallel, but only one process at a time (for some reason this works much better than "sequential"). I think the only permanent solution is to upgrade the server to 64-bit, and that is what we are doing know with our server.

Restarting the service clears out the cache, which makes more application memory available, and likewise would make the process more likely to complete successfully.

"Server: The operation has been cancelled." When Processing Cube

There are several other posts around this but none really seem to point to a solution. Here is my situation:

1. We've been running in production with SP1 of Standard Edition with no problems since October (4GB, Windows Server Standard, 4 fact tables, 20 million rows in largest fact)

2. I applied SP2 to development last week. While working in dev I saw the "Server: The operation has been cancelled." error for the first time while I was making some dimension changes (i'd never seen this error in SP2). I tried to process the cube several times and it always errored this way. I backed out my changes, processed the cube and all was well. I re-applied some of my changes and all was still well. I decided to upgrade prod since I thought the problem at this point was some bad configuration of the dimension.

3. We upgraded two servers in prod with the exact same hardware configuration, but with SP2 now and Standard edition (NOTE: The cube structure was not changed). We've been processing fine since Monday's load until last night. Last night one of the servers failed during the cube processing step with the "Server: The operation has been cancelled." error.

4. I've got the process running again after restarting the analysis services process...only thing I could think to try right now. Hopefully it works...i didn't see any posts with any type of guidance that might help

Has anyone else seen a problem where what worked in SP1 stopped working in SP2? Are most people running SP2 using the most recent patch rollup? Should I move to that? Any hope this issue was solved in one of the patch rollups?

Thanks.

Sounds like you might have a commitTimeout set, if you are seeing these operation cancelled errors from from the processing task. As far as I can tell the default setting for ForceCommitTimeout was changed in SP2, but I did not think there was one for the CommitTimeout. See this post for more information on these two settings http://geekswithblogs.net/darrengosbell/archive/2007/04/24/SSAS-Processing-ForceCommitTimeout-and-quotthe-operation-has-been-cancelledquot.aspx|||

Thanks for the response. I had already tried that with no luck based on other posts I've seen. I saw one posting where the person said they have to restart the services every night. I just set EVERY timeout in the properties (by choosing advanced) to 0...I just had a Cancel Operation in dev again. I'll try it with all the timeouts set to 0 to see what hapepns.

Thanks.

|||This problem is getting really annoying. On one server it has never happened, on another it happens about every other day. If I restart the service then the process runs through. Note that in dev restarting the service doesn't always fix it...sometimes it just takes trying to run it multiple times. Something in SP2 broke SSAS processing...I never had a single problem in SP1.|||

I'm surprised no one else is seeing these issues with SP2. I had no issues with cube processing until I applied SP2. I just got a different error. Note that so far in every case if I just restart the service then the next cube load works fine. Is there any other tracing I can turn on to help identify what the issue is?

OnError,DWS569794SQL2,NT AUTHORITY\SYSTEM,Analysis Services Processing Task,{F9170A49-2D23-4DA6-962C-CD55A57C191A},{1E79FD64-2D1F-48A8-910B-30501E61BF0E},7/26/2007 7:08:48 AM,7/26/2007 7:08:48 AM,-1056964601,0x,Internal error: The operation terminated unsuccessfully.
OnError,DWS569794SQL2,NT AUTHORITY\SYSTEM,master,{F9625461-CC09-4D2F-A3DE-6B64B9D0E230},{1E79FD64-2D1F-48A8-910B-30501E61BF0E},7/26/2007 7:08:48 AM,7/26/2007 7:08:48 AM,-1056964601,0x,Internal error: The operation terminated unsuccessfully.
OnError,DWS569794SQL2,NT AUTHORITY\SYSTEM,Analysis Services Processing Task,{F9170A49-2D23-4DA6-962C-CD55A57C191A},{1E79FD64-2D1F-48A8-910B-30501E61BF0E},7/26/2007 7:08:48 AM,7/26/2007 7:08:48 AM,-1056767999,0x,Memory error: Allocation failure : Not enough storage is available to process this command. .
OnError,DWS569794SQL2,NT AUTHORITY\SYSTEM,master,{F9625461-CC09-4D2F-A3DE-6B64B9D0E230},{1E79FD64-2D1F-48A8-910B-30501E61BF0E},7/26/2007 7:08:48 AM,7/26/2007 7:08:48 AM,-1056767999,0x,Memory error: Allocation failure : Not enough storage is available to process this command. .
OnError,DWS569794SQL2,NT AUTHORITY\SYSTEM,Analysis Services Processing Task,{F9170A49-2D23-4DA6-962C-CD55A57C191A},{1E79FD64-2D1F-48A8-910B-30501E61BF0E},7/26/2007 7:08:48 AM,7/26/2007 7:08:48 AM,-1054932978,0x,Errors in the OLAP storage engine: An error occurred while processing the 'Tracking F' partition of the 'Tracking' measure group for the 'Warehouse' cube from the Cube database.
OnError,DWS569794SQL2,NT AUTHORITY\SYSTEM,master,{F9625461-CC09-4D2F-A3DE-6B64B9D0E230},{1E79FD64-2D1F-48A8-910B-30501E61BF0E},7/26/2007 7:08:48 AM,7/26/2007 7:08:48 AM,-1054932978,0x,Errors in the OLAP storage engine: An error occurred while processing the 'Tracking F' partition of the 'Tracking' measure group for the 'Warehouse' cube from the Cube database.
OnError,DWS569794SQL2,NT AUTHORITY\SYSTEM,Analysis Services Processing Task,{F9170A49-2D23-4DA6-962C-CD55A57C191A},{1E79FD64-2D1F-48A8-910B-30501E61BF0E},7/26/2007 7:08:48 AM,7/26/2007 7:08:48 AM,-1054932986,0x,Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
OnError,DWS569794SQL2,NT AUTHORITY\SYSTEM,master,{F9625461-CC09-4D2F-A3DE-6B64B9D0E230},{1E79FD64-2D1F-48A8-910B-30501E61BF0E},7/26/2007 7:08:48 AM,7/26/2007 7:08:48 AM,-1054932986,0x,Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.

|||

In the errors you have, it seems that this is the root cause:

Memory error: Allocation failure : Not enough storage is available to process this command

In case you have large dimensions, a common solution for out of memory errors is to reduce the parallelism for processing dimension attributes. There is the server property CoordinatorExecutionMode; the default is -4 which means that server will process 4 x NumberOfProcs dimension attributes in parallel (so if you have many attribute members, processing them in parallel, even for a single dimension, will lead to out of memory). Try setting it to 1.

Adrian Dumitrascu

|||I've been having the same issue with two individual servers both running SP2, a third server running SP1 will still build the exact same cube without fail.

On both SP2 servers the cube simply hangs after reporting a couple of attribute key not found errors- even running a trace reveals no actual errors or further progress from this point and any attempts to stop the processing short of restarting the SSAS service fail.

I've tried changing ForceCommitTimeout and CoordinatorExecutionMode in vain.
|||Getting exactly the same problem. Applied SP2 and the cube starts processing but never completes with no obvious errors. Desperate for a solution.....

|||

Adrian, your response is not the answer. There are problems with SP2 that did not exist in SP1. I do have some updates.

1. I did try setting the flag to ignore the memory errors. I believe that this did make a difference.

2. I'm not completely sure about #1, because I also made a big cube change as well. A long time ago I had built a degenerate dimension as I needed to quickly implement some functionality. I was suspecting that this was what was causing the cube processing errors. I built a new dimension and updated by ETL to create a standard dimension to replace the degenerate dimension. Once this was done (crossing my fingers) the cube has built without any issue.

For the other people posting, are you using degenerate dimensions? Have you tried setting the MemoryLimitErrorEnabled to false?

Microsoft readers, please note the common thread though, these issues did not exist in SP1.

|||

I believe this error is due to lack of memory, either from lack of memory on your machine, or (more likely) from the 32-bit platform itself. To the best of my knowledge the 32-bit versions of analysis server can only utilize a maximum of 3 GB of memory, which includes page file (virtual memory) usage. In my own experience, these errors occur when I am processing a large cube or dimension and the memory usage (combined RAM and virtual memory) reaches about 2.5GB. I think setting the MemoryLimitErrorEnabled to false helps greatly, as does setting processing operations to run in parallel, but only one process at a time (for some reason this works much better than "sequential"). I think the only permanent solution is to upgrade the server to 64-bit, and that is what we are doing know with our server.

Restarting the service clears out the cache, which makes more application memory available, and likewise would make the process more likely to complete successfully.

"Missing" Data / Data Not Making It Into AS2005 Cube

All of the data in one of my fact tables in the relational db doesn't seem to be making into the cube. However, all of the data from our sales fact table is indeed making into the cube.

There is no filtering going on in the Named Query in the DSV that brings in this data. What could it be? The table's relationship? Dimension Usage?

I also developed a small test cube and was able to see all of the data. I would hate the thought of having to rebuild/redevelop the cube from scratch.

I've tried everything that I could think of.

Any thoughts?

Hello. One possible explanation can be that the service account, running SSAS2005, do not have permissions in your relational db. Are all you fact tables in the same database /data warehouse? Do you have several data source systems?

Regards

Thomas Ivarsson

|||I think I'm making progress on the troubleshooting.

I just deleted a lot of the dimensions in the Dimension Usage tab for this particular measure group. In doing so, all of the data is now there.

I will slowly add the dimension relationships back to see which one is the cause and then will try to understand why that is the case.|||I figured it out.

"Error in Processing Cubes in Analysis Services"

Hi,

While processing the cube in Analysis server, we are getting frequent error saying

"Analysis Server Error: Connection to the server is lost; Time:2/9/2007 3:56:08 PM
Error(-2147221411): Process operation failed; Time:2/9/2007 3:56:08 PM
Error(-2147221423): Could not open server object (Internal (Database 'PMM' could not be opened on the server)); ". help in this regard will be greatly appreciated.

Thanks,

Umesh

Take a look at the log folder that is located under OLAP folder, right next to the bin folder. It is often installed to C:\Program Files\Microsoft SQL Server\MSSQL.2

If you see multiple SQLDump***.mdmp there. And if you see that after any such processing attempt you see new file is created- this is indication of Analysis Server running into situation it cannot recover from, but abort processing operation.

You should install latest service pack for SQL Server 2005 - currently it is SP1.

After, try running unprocess for entire database and then processing all the objects.

If you still see .mdmp files created, contact customer support and show them the repro schenario for the problem you having.

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

"Best Practices" for UDM on OLTP?

Are there particular things you should do when putting the UDM on top of an OLTP schema? I've been trying to do this, but can't get the cube or dimensions to build. Just wondering if I'm missing something or if this "feature" really exists.

This is very open question. You should try and look for some book or training about data warehousing and OLAP.

Here is some information for you: http://www.kimballgroup.com/html/books.html

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

|||

Sorry, let me clarify. I'm pretty well versed with dimensional modeling. However, I'm specifically looking for information on using the SSAS 2005 UDM on top of an OLTP schema. For instance, what are the "gotcha's" and how far away can you get from the dimensional model with the UDM. Do you need to use the dimensional model, at the core of the UDM, then embellish your schema with OLTP tables? I guess I'm saying that a lot has been promised about bypassing ETL with the UDM and I'd like to know how much of that is really true. Again, I've tried to build a cube with the UDM directly sourcing from an OLTP schema, but I couldn't get anything to work. I'm currently researching the OLTP schema, since it's a dev schema and not a prod schema, so that might be the problem. I just want to make sure there aren't any other issues I should be aware of.

|||

In version 2005 Analysis Services greatly improved the ability for you to build multidimensional models, or unified dimensional models on top of variety relational schemas, OLTP included. Analysis Services allow for great modeling flexibility.

If the question is how easiy it is to map OLTP schema into multidimensional model, the answer is it: depends. It very much depends on the way OLTP schema is stuctured. It very much depends on how much of the relational schema you would like to see in UDM.

Also depends on how clean the data in OLTP is. If you allow for inconsistencies or your legacy system. If your OLTP system has been through several interations of tweaks and upgrades, you will need to have an ETL process to clean it up. In some cases there is no way around it.

In short, AS2005 is very flexible and poweful, but with flexibility comes the danger of creating wrong design. Try and use Analysis Services to create a simple UDM's first. Take a look at the best practices. Test your UDM see if you getting the right answers...

Hope that helps:

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

|||Where can I find ANY tutorial on using UDM methodology - Against AdventureWorks or other?

"Best Practices" for UDM on OLTP?

Are there particular things you should do when putting the UDM on top of an OLTP schema? I've been trying to do this, but can't get the cube or dimensions to build. Just wondering if I'm missing something or if this "feature" really exists.

This is very open question. You should try and look for some book or training about data warehousing and OLAP.

Here is some information for you: http://www.kimballgroup.com/html/books.html

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

|||

Sorry, let me clarify. I'm pretty well versed with dimensional modeling. However, I'm specifically looking for information on using the SSAS 2005 UDM on top of an OLTP schema. For instance, what are the "gotcha's" and how far away can you get from the dimensional model with the UDM. Do you need to use the dimensional model, at the core of the UDM, then embellish your schema with OLTP tables? I guess I'm saying that a lot has been promised about bypassing ETL with the UDM and I'd like to know how much of that is really true. Again, I've tried to build a cube with the UDM directly sourcing from an OLTP schema, but I couldn't get anything to work. I'm currently researching the OLTP schema, since it's a dev schema and not a prod schema, so that might be the problem. I just want to make sure there aren't any other issues I should be aware of.

|||

In version 2005 Analysis Services greatly improved the ability for you to build multidimensional models, or unified dimensional models on top of variety relational schemas, OLTP included. Analysis Services allow for great modeling flexibility.

If the question is how easiy it is to map OLTP schema into multidimensional model, the answer is it: depends. It very much depends on the way OLTP schema is stuctured. It very much depends on how much of the relational schema you would like to see in UDM.

Also depends on how clean the data in OLTP is. If you allow for inconsistencies or your legacy system. If your OLTP system has been through several interations of tweaks and upgrades, you will need to have an ETL process to clean it up. In some cases there is no way around it.

In short, AS2005 is very flexible and poweful, but with flexibility comes the danger of creating wrong design. Try and use Analysis Services to create a simple UDM's first. Take a look at the best practices. Test your UDM see if you getting the right answers...

Hope that helps:

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

|||Where can I find ANY tutorial on using UDM methodology - Against AdventureWorks or other?

"#Value!" error for calculated cells

I have designed an AS 2000 cube with quite complex calculated cells, which work fine in cube browser or with MDX. The moment I use Excel 2007 Pivot table (both in default or compatibility mode), it shows #Value! instead of the calculations. I thought GetPivotData simply returned values from the cube and did not do any calculations of its own... I spent a lot of time to design the cube, but now it cannot be used from Excel, which it needs to be. Any help would be greatly appreciated.

Best regards.

Have you tried creating a copy of your cube, removing all the calculated cells and then connecting to it?

This might help isolate if it is just an Excel connectivity issue or an problem to do with the calculations. Unfortunately you cannot use profiler to trace AS 2000 like you can with AS 2005 otherwise we could trace the MDX that Excel is sending to the server.

|||

Thank you Darren,

It is definitely not an Excel connectivity issue, since everything else shows on the cube. It seems to fall over when it comes across calculated cells which are defined on the cube. And it does seem to be specific to Excel only. I remember the was an issue with pivot tables when the was a limitation on the cell definition MDX length, but it is unlikely to be that (I used the very minimun number of dimensions). Maybe there is known issue with the display of certain calculated cells in Excel ...

|||I think I managed to locat ethe actual problem area - those caclulated cells use a User Defined Function. When I remove the function, the "#Value!" message disappears . It is strange, because the function is not called by excel, it should be resolved on the cube where it works 100%...|||

The problem in fact looks similar to one described in

http://support.microsoft.com/kb/238306

But that was valid for much older versions of AS and was supposed to be fixed a while ago. (I am using up to date version of AS 2000).

|||

it should be resolved on the cube where it works 100%...

I am just wondering if this is not the case. AS2k used to do a fair bit of the query resolution on the client. If you run ProcessMonitor while Excel is trying to execute the query you might be able to see if it is trying to execute the UDF on the client (although it might be hard as ProcessMonitor catches a lot of information)|||Actually an easier way might be to try installing your UDF on a client machine and see if that fixes the problem.|||

I tried that and it di dnot work. One thing I noticed Excel looked in C:\Documents and Settings\f2978326\Application Data\Microsoft\Template for UserDefined.UDF file and UserDefined is the name of the library while UDF is the name of the class that calculations use ..... Does anyone actually have the information on how PivotTable service handles cells which reference UDFs from analysis server? So far I have not been able to get it anywhere...

% of Total

I have a matrix report built on a database not on the cube.

2006

Q1

Volume MS %

Sub Channel Product

X X1 10

X2 20

Subtotal 30

Y Y1 20

Y2 20

Subtotal 40

I need to calculate the Market Share values which are Volume of the product / Subtotal for example

MS% of Product X1 = Volume of X1 / Subtotal i.e 10/ 30 * 100 = 33.33 %

How can i achieve this within the report..Can somebody help/advise...

Why dont you make 1 more column in your query for the subtotal

it will be like

select subchannel, product, year, quarter, volume,
( select sum(volume)
from table t2
where t2.subchannel=t1.subchannel and t2.year=t1.year and t2.quarter=t1.quarter
group by t2.subchannel, t2.year, t2.quarter
) as subtotal
from table t1

After getting this column, you can use the inscope function for changing the field in the details column in the matrix.

|||

Can you please elaborate how to use the InScope on the details column, please

|||

hi,

i guess you have 2 groups in your table, 1 for subchannel and 1 for product

where subchannel has a footer where the subtotal is calculated

just name the field where "30" stand SubTotal

and the field where "10" stands Volume

and add a new field next to the "10"-field with the forumlar =ReportItems!Volume.value/ReportItems!SubTotal.value

should work, otherwise calculate it in your query in a new column:

SELECT subchannel, product, volume,

(SELECT Sum(Volume)

FROM TableXY

WHERE SubChannel=A.SubChannel) AS SubTotal

FROM TableXY AS A

i would solve it with the query ...

greets

gerhard

|||

Now you have the field subtotal in your dataset.

lets say the group name for the group year is "group_year".

Then instead of using the expression Fields!volume in the details column, use this expression

Inscope("group_year",Fields!volume/Fields!subtotal,Fields!volume)

% of Total

I have a matrix report built on a database not on the cube.

2006

Q1

Volume MS %

Sub Channel Product

X X1 10

X2 20

Subtotal 30

Y Y1 20

Y2 20

Subtotal 40

I need to calculate the Market Share values which are Volume of the product / Subtotal for example

MS% of Product X1 = Volume of X1 / Subtotal i.e 10/ 30 * 100 = 33.33 %

How can i achieve this within the report..Can somebody help/advise...

Why dont you make 1 more column in your query for the subtotal

it will be like

select subchannel, product, year, quarter, volume,
( select sum(volume)
from table t2
where t2.subchannel=t1.subchannel and t2.year=t1.year and t2.quarter=t1.quarter
group by t2.subchannel, t2.year, t2.quarter
) as subtotal
from table t1

After getting this column, you can use the inscope function for changing the field in the details column in the matrix.

|||

Can you please elaborate how to use the InScope on the details column, please

|||

hi,

i guess you have 2 groups in your table, 1 for subchannel and 1 for product

where subchannel has a footer where the subtotal is calculated

just name the field where "30" stand SubTotal

and the field where "10" stands Volume

and add a new field next to the "10"-field with the forumlar =ReportItems!Volume.value/ReportItems!SubTotal.value

should work, otherwise calculate it in your query in a new column:

SELECT subchannel, product, volume,

(SELECT Sum(Volume)

FROM TableXY

WHERE SubChannel=A.SubChannel) AS SubTotal

FROM TableXY AS A

i would solve it with the query ...

greets

gerhard

|||

Now you have the field subtotal in your dataset.

lets say the group name for the group year is "group_year".

Then instead of using the expression Fields!volume in the details column, use this expression

Inscope("group_year",Fields!volume/Fields!subtotal,Fields!volume)

% of Total

I have a matrix report built on a database not on the cube.

2006

Q1

Volume MS %

Sub Channel Product

X X1 10

X2 20

Subtotal 30

Y Y1 20

Y2 20

Subtotal 40

I need to calculate the Market Share values which are Volume of the product / Subtotal for example

MS% of Product X1 = Volume of X1 / Subtotal i.e 10/ 30 * 100 = 33.33 %

How can i achieve this within the report..Can somebody help/advise...

Why dont you make 1 more column in your query for the subtotal

it will be like

select subchannel, product, year, quarter, volume,
( select sum(volume)
from table t2
where t2.subchannel=t1.subchannel and t2.year=t1.year and t2.quarter=t1.quarter
group by t2.subchannel, t2.year, t2.quarter
) as subtotal
from table t1

After getting this column, you can use the inscope function for changing the field in the details column in the matrix.

|||

Can you please elaborate how to use the InScope on the details column, please

|||

hi,

i guess you have 2 groups in your table, 1 for subchannel and 1 for product

where subchannel has a footer where the subtotal is calculated

just name the field where "30" stand SubTotal

and the field where "10" stands Volume

and add a new field next to the "10"-field with the forumlar =ReportItems!Volume.value/ReportItems!SubTotal.value

should work, otherwise calculate it in your query in a new column:

SELECT subchannel, product, volume,

(SELECT Sum(Volume)

FROM TableXY

WHERE SubChannel=A.SubChannel) AS SubTotal

FROM TableXY AS A

i would solve it with the query ...

greets

gerhard

|||

Now you have the field subtotal in your dataset.

lets say the group name for the group year is "group_year".

Then instead of using the expression Fields!volume in the details column, use this expression

Inscope("group_year",Fields!volume/Fields!subtotal,Fields!volume)

#value in Cube Calculation

Hello all

GIven the following calculation

([Measures].[12 Month Total Sales],[Sales History].[Sales YM].[Year].lag(1))

were 12 Month Total Sales is a measure of Sale History why do I get #value for this calculation ?

Thanks for your assistance

My guess is that [Sales History].[Sales YM].[Year] is a level, and lag() only works on a member. Does this do what you want?

([Measures].[12 Month Total Sales],[Sales History].[Sales YM].currentmember.lag(1))

Chris

|||thank you

#N/A in measures - Excel 2003 client

After giving some users read access to the cube, by using a readonly role -
The [Measures] are displayed with #N/A in Excel 2003. Only users with loal administrator previledge can see measures.

Please advise.

What I encountered with AS 2005 was that Cell Security defaulted to denying access to a reader role, resulting in #N/A. So I simply entered "1" as the MDX expression for "allow reading of cube content" - not sure if this is the issue for you as well:

http://msdn2.microsoft.com/en-us/library/ms174847(SQL.90).aspx

>>

Granting Custom Access to Cell Data

After you grant a database role in Microsoft SQL Server 2005 Analysis Services (SSAS) read or read/write permissions to a cube, you have to grant to this database role the permission to view some or all cell data. Only granting cube access to a database role does not give that database role access to specific cube cells.

...

>>

|||Can you please give more details about when AS2005 by default disables cell security ? Creating cell security, even as trivial as constant 1 may have very bad effect on performance, since there are many important optimizations which are disabled in the presence of cell security...|||

Hi Mosha,

From what I can tell (based on a simple prototype AS 2005 RTM cube), cell read is disabled by default, unless "Full control (Administrator)" is selected on this page:

http://msdn2.microsoft.com/en-us/library/ms188508(SQL.90).aspx

>>

General (Role Designer) (SSAS)

>>

And that is regardless of "Enable read permissions" being checked on this page (unless "1" is entered for the "Allow reading of cube content" MDX expression):

http://msdn2.microsoft.com/en-us/library/ms177279(SQL.90).aspx

>>

Cell Data (Role Designer) (SSAS)

>>

Is there perhaps a better way to configure cell read access for a non-admin role?

|||

Dear ASMK,

Please check your cell security MDX, the measure you are viewing might be in the

denied member list. This might be helpfull.

#N/A in measures - Excel 2003 client

After giving some users read access to the cube, by using a readonly role -
The [Measures] are displayed with #N/A in Excel 2003. Only users with loal administrator previledge can see measures.

Please advise.

What I encountered with AS 2005 was that Cell Security defaulted to denying access to a reader role, resulting in #N/A. So I simply entered "1" as the MDX expression for "allow reading of cube content" - not sure if this is the issue for you as well:

http://msdn2.microsoft.com/en-us/library/ms174847(SQL.90).aspx

>>

Granting Custom Access to Cell Data

After you grant a database role in Microsoft SQL Server 2005 Analysis Services (SSAS) read or read/write permissions to a cube, you have to grant to this database role the permission to view some or all cell data. Only granting cube access to a database role does not give that database role access to specific cube cells.

...

>>

|||Can you please give more details about when AS2005 by default disables cell security ? Creating cell security, even as trivial as constant 1 may have very bad effect on performance, since there are many important optimizations which are disabled in the presence of cell security...|||

Hi Mosha,

From what I can tell (based on a simple prototype AS 2005 RTM cube), cell read is disabled by default, unless "Full control (Administrator)" is selected on this page:

http://msdn2.microsoft.com/en-us/library/ms188508(SQL.90).aspx

>>

General (Role Designer) (SSAS)

>>

And that is regardless of "Enable read permissions" being checked on this page (unless "1" is entered for the "Allow reading of cube content" MDX expression):

http://msdn2.microsoft.com/en-us/library/ms177279(SQL.90).aspx

>>

Cell Data (Role Designer) (SSAS)

>>

Is there perhaps a better way to configure cell read access for a non-admin role?

|||

Dear ASMK,

Please check your cell security MDX, the measure you are viewing might be in the

denied member list. This might be helpfull.