Cube Processing

Hi ,
I have a cube, whose fact table has over 50 million records,
each day the cube is incrementally processed
as I was incrementally updating everyday it was not a problem.
but lately there was some problem while cube was processing
and cube need to be re-processed from scratch as I have lost
all the process data from year 2003.
To process 1 day data it takes around 20 minutes, please let me
know the easiler way to process the complete data from year 2003.
secondly is there anyway i can take the backup and restore it and
later which require no processing of cube and cubes data is intact.
Thanks,
MonTo do individual periods, your best solution is to use partitioning. A
typical design might be yearly partitions for all of history and monthly
partitions in the current year. This allows you to process just a particular
year (for previous years) or individual months in the current year.
On your second question, archive and restore is done at the database level.
If the database was consistent when you backed it up (i.e. no processing was
required); then it will be restored in the same state (i.e. no processing
required).
--
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.
"Mon" <Mon@.discussions.microsoft.com> wrote in message
news:A8E3DEF2-B4FF-4F30-95CE-800D285BB6C0@.microsoft.com...
> Hi ,
> I have a cube, whose fact table has over 50 million records,
> each day the cube is incrementally processed
> as I was incrementally updating everyday it was not a problem.
> but lately there was some problem while cube was processing
> and cube need to be re-processed from scratch as I have lost
> all the process data from year 2003.
> To process 1 day data it takes around 20 minutes, please let me
> know the easiler way to process the complete data from year 2003.
> secondly is there anyway i can take the backup and restore it and
> later which require no processing of cube and cubes data is intact.
> Thanks,
> Mon
>

Cube corruption

I have an AS 2005 cube (MOLAP) that is updated incrementally on a daily basis. Twice in the last few weeks, the cube has suddenly become corrupt. The By-Day aggregations for previous days show incredibly large positive and negative numbers. These numbers at the time they entered the cube were accurate.

As background, the incremental update is done via a view and the view is calculating fields. At this point the cube is processed manually during the AM, and the processing has not reported any errors.

Any insight would be appreciated.

Doug

First try to install SP1 for SQL Server 2005 and see if the same is happening. (http://www.microsoft.com/downloads/details.aspx?familyid=cb6c71ea-d649-47ff-9176-e7cac58fd4bc&displaylang=en)

If your problem persists, please contact customer support and report the problem.

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

|||

hi Doug,

I am investigating this issue and trying to reproduce it. Could you give me more information on this issue such as:

1. Are the results correct after a process clear followed by a process full on the cube (and everything else it depends on)?

2. What is the sequence of operations that consistently lead to this situation (like start with no databases, deploy the project, process full, then continue with daily process update)?

3. how by day aggs are setup?

thank,

Gang

|||

Gang,

Thanks for looking into this.

1. Yes, the results are fine if we do a Full Process. The couple of times this has happened, that has been the solution.

2. The sequence of events here is pretty straightforward. We just do a Process Incremental every morning (after updating the dimensions). We've been in production about a month now. I'm doing it manually so far just to watch it execute. It never reports failure thru the output windows. Occasionally, our data migration fails, and I'm unable to refresh the cube. In this case, since each nightly load ends up in a table, I just add that table to the view and do the incremental update to catch us up.

The cube is loaded thru a view that has calced fields. That may be a problem for the cube. The view correctly unions the tables, and the data are fine if you query the view. I tested this. The cube, on the other hand, may not be able to digest the union of two recordsets that contain calculated fields. That might be something to check on. This is the only thing I can think of that might me causing the problem. Most of the time, the incremental process works just fine, though we did notice a real performance hit when we added the calculated fields to the view loading the cube.

3. I'm not sure what you mean with this question. We're in the telcom business, and the data in question are call records, a couple of million a day. In the datamart that's underneath the cube, each call gets assigned a "day" key. "Day" is the lowest level of granularity in the DataMart. In the cube, we group by Year/Month/Day and sum the call minutes. What we occasionally see is that certain days become corrupt, with super large numbers appearing, both positive and negative. The data around them are fine. We process full and the problem goes away.

Hope this helps

Doug

|||

Doug,

thank you for taking the time to report the problem in such great detail. We are going to simulate your project. Is that possible to share your project with us? All we need are data source, DSV, dimensions plus cube. Not the data. It will be great If you can trim your project to exactly what's necessary to repro the problem (i.e. exclude “experimental” cubes/dimensions).

Gang

|||

Gang,

Any help is appreciated. I have created a Winzip archive with the VS objects (.Cube, .dim ,etc.) How do I get that to you? I'm not seeing a way on the forum to attach this file.

Doug

|||

Doug,

could you send it to me via email(get my email address by clicking my name)?

thanks,

Gang