SQL,tables in a DB: point

Showing posts with label point. Show all posts

Thursday, March 22, 2012

(Basic?) User-defined hierachy question

Hi Everyone,

I'm pretty new to developing with analysis services & OLAP, but was hoping someone here could point me in the right direction.

I have two dimension tables (Department, Employee), and one fact table (TelephoneCalls). Using a star schema, the two dimension tables are joined by their PK fields through the Fact table.

I'm wanting to drill down through the hierachies set up in Department, but also having the Employee branches populated at the right nodes. (ie a department may have a number of sub-departments, and also employees associated with it).

Based on this information, does anyone know how I could achieve this?

Much Appreciated,

Andrew

Is an employee associated with one and only one department when a telephone call occurs? If so, you may want to have a single dimension table for employee that associates the employee with a department. Otherwise, SSAS 2005 does not support cross-dimensional hierarchies. That said, you might find an interface that lets you structure something like this, and you can always do a cross-join of department and employee in MDX.

Hope that helps,
Bryan

|||

Unfortunately employee's have the freedom to jump around departments, so I can't make a single dimension table for it.

I'll try your suggestions to build it using MDX.

Thanks for your help.

Andrew

|||

"Jump around" as in "with each phone call" or just every few months or so? If it's the later, you might consider implementing a Type 2 slowly changing dimension.

Monday, March 19, 2012

"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)

Thanks.

|||

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

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.

|||

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.

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.

|||

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

Friday, February 24, 2012

"Blending" Two Tables

Hi All,

I've searched and haven't found anything so if this has been answered, please point me to the correct thread.

I'm trying to "blend" two tables together into a dataset for use in an asp project - sort of merging but not exactly. Here is the scenario:

Table 1 - Software

MfrID ProductID Description

1 1 Word

1 2 Excel

1 3 Access
2 1 AutoCAD

2 2 Inventor

- where 1=Microsoft; 2 = Autodesk

Table 2 - User Proficiency

UserID ProductID SkillLevel

1 1 2

1 2 3

So I want to end up with:

ProductID Description SkillLevel

1 Word 2

2 Excel 3

3 Access null --> or anything; the point is this record!

If I use a LEFT JOIN, I get it to work for ALL users/ALL Products but as soon as I specify WHERE clauses for USERID and MFRID [respectively], I only get all of one table or the other. If I apply a literal new column "1 AS SkillLevel" on Products and use a UNION, I get "duplicates" because the values for SkillLevel are different.

What I want is to ignore the SkillLevel row if it is the null or 1 case...IOW if the user has a setting use it instead of the default, otherwise use the default. I hope this makes sense.

TIA

Mike

Ok..One possible solution, instead of using the where clause filter, on the join condition itself you can apply the filter (be carefull, it may change the entire query result)

But this is depends with what result you need.

Can you post the detailed sample data. I feel there is some gap on your posted sample data.

|||

There is not enough data in the Proficiency table. You cannot distinguish between any Manufacturer's ProductID. Whose ProductID = 1? Microsoft or Autodesk?

Surely you don't mean to equate a SkillLevel of 2, for ProductID = 1 means that the user is equally proficient in both Word and Autocad.

And your Proficiency table doesn't indicate that there is anyone with skills in Access, yet you want Access in the desired output -But following the same 'rules', Autocad and Inventor 'should' also be in the output with NULL values for SkillLevel. But it is not -why?

|||

Hi Mike,

I agree with Arnie, you need to expand more about what you want to accomplish here. This is a guess:

select

ProductID,

Description,

(select max(up.SkillLevel) from dbo.[User Proficiency] as up where up.ProductID = s.ProductID) as SkillLevel

from

dbo.software as s

where

MfrID = 1;

Select the maximum [SkillLevel] from table [User Proficiency], for each row that match the criteria in table [Software].

Notice that there is not relation with the [MfrID], so it could be that we select the [SkillLevel] from a product that does not belong

to the same manufacture.

AMB

|||

Ok, sorry guys! I was trying to simplify this and I guess it backfired on me. To answer the questions - I work for a global Autodesk reseller. We have hundreds of trainers and each has a multi-level proficiency heirarchy such as Demo-Low Level which means the person can Train at the highest level AND is a low level Demo jock. I'm working on taking the old system, moving it forward and putting it on the web. I have tables out the backend with lookups and everything else so in my example, the Products table is linked to a manufacturer's table to pull in the Microsoft, or Autodesk, etc. as are the skill levels, users, and on and on.

What happens is products fall into "Verticals" with Autodesk so there is MSD [Mechanical software], BSD [Architectural software], etc. Each of these could have a dozen programs and a trainer can be proficient in some and not know others within that Vertical. Some trainers can also be multi-Vertical so they have skills in multiple areas but, again, may only know one or two programs within each specific vertical. Also programs are added as Autodesk releases them so the user may not have a proficiency yet if the program was just added.

So what I want to do is

Step 1: Find out which Verticals the trainer in question has selected, then pull ALL of the software programs from that Vertical into a result which goes to a dataset then an ASP gridview.

Step 2: Then poll the UserSkills table and return any proficiencies the user has already set for any of the given software and set the Skill Level for any of the rows returned in Step 1

Now, I could do this in 2 steps looping through the results from Step 2 and applying them to Step 1. If I can do this in a single SQL statement it would work a lot more efficiently. Here is my closest attempt:

SELECT DISTINCT Products.VerticalID, Products.ProductID, 1 AS SkillPrimaryID, 1 AS SkillSecondaryID FROM Products

WHERE VerticalID='4'

UNION

SELECT DISTINCT UserSkills.VerticalID, UserSkills.ProductID, UserSkills.SkillPrimaryID, UserSkills.SkillSecondaryID FROM UserSkills WHERE UserID='6' AND VerticalID='4'

ORDER BY ProductID

This produces this result where the user only has set a proficiency for Vertical #4 -> Product #54:

4 54 1 1
4 54 4 1
4 55 1 1
4 56 1 1
4 57 1 1
4 58 1 1
4 59 1 1

You can see where lines 1 and 2 are "duplicates" - line 1 has the default "1" and line 2 has the user's stored value of "4". Now I realize that these are not duplicates to SQL since the values are different. What I want to do is have this result set w/o line 1 since line 2 is the row I need. Does this clear things up? Whether the first Select adds a literal or not doesn't matter to me if there is some other way to accomplish this. I added the literal so I could use the Union.

|||Awesome! Thank you very much AMB!!! I got it using:

select ProductID, ProductDescription, (select max(up.SkillPrimaryID) from dbo.UserSkills as up where up.ProductID = s.ProductID and userid='6') as SkillLevel

from dbo.Products as s where VerticalID = '4';

I handle cad programming and there's not sql typically so I'm rusty. I definitely need to read up on the filters!

Mike

Thursday, March 22, 2012

(Basic?) User-defined hierachy question

Monday, March 19, 2012

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

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

Friday, February 24, 2012

"Blending" Two Tables

SQL,tables in a DB

Blog Archive

About Me