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 Process Very Slow

One of my cube in my AS 2005 DB has about 24 million record and bunch of dimensions. I have been watching its processing time in the past week or two. I have been doing Full Process every morning, on average it takes about 23 minutes to finish the full process. However starting yesterday, all of sudden, it took 34 minutes to finish, then today, it is like crawl, it is now about 68 minutes, it is only reading about 7.55 million (total is 24 million). Could anyone give me some pointer on what is going on.

I have 8 dual core processor, 24 gb memory on 64 bit SQL 2005 standard on windows 2003 64 bit with Database engine, AS, RS and IS in one server.

Hello, if you have a sudden increase in processing time of the cube you can have thousands of explanations for that problem.

Please provide more information about applications competing for resources on the same box, including an OLTP application. Are you only running a BI-solution or more applications?

You should not have these problems with a server with that capacity.

And SQL Server 2005 / Analysis Services, Standard Edition, only supports 4 GB RAM.

Enterprise edition do not have that limit.

HTH

Thomas Ivarsson

|||Check your datasource, indexes may have been dropped, etc. Narrow the problem down to SSAS or your datasource. Since you said its been fine for awhile, usually a hit in performance occurs at the datasource.|||

Thanks Thomas and Zach! That's what happened when you have all the eggs in one box, it turns out one of backup job has been schedule running too frequently and take all I/O. I do have couple of quesitons:

1) Right now my ssis process cube task is setup as Full Process the whole AS database (around 15 cubes), and half of the time is used to process the 24 million cube, do you think it is better to setup to process this cube separately, do you think just doing "ProcessIncremental" and choose "affected objects" should be enough to replace "ProcessFull", I know if you choose "ProcessData" then you have to do "ProcessIndex" afterwards?

2) I did change default setting of AS one of memory/lowMemoryLimit from 75 to 50, and memory/totalmemoryLimit from 80 to 55, Do you think this will affect the AS cube performance?

|||Are these cubes time related? IE: partitioned by week, if partitioned at all? Is your system setup to handle Process Update and having your dimensions update any partition data...instead of reprocessing the entire database?|||

I most often recommend full process.

Your options are a bit limited since you run the standard edition of Analysis Services 2005 that do not have partitions as a feature.

With partitions you can process parts of the fact tables and save some processing time.

With the hardware capacity you have available you can consider using the Enterprise Edition instead.

The suitable setting for memory is covered in the Performance Guide pointed to at the top of this newsgroup.

HTH

Thomas Ivarsson

cube limit

Hi all,

I am new to SSAS with SQL2005SP2. Currently, I have a fact table with almost 10 million records. In this table, I have at least 27 fields which store monthly data for the past 27 months along with some other fields. At first, I plan to make those 27 fields to one field and add another field like period to capture the year and month information so I can link to my time dimension table. Of course, this makes the new table with 270 million record counts. It took forever to process the cube. If I add more dimenstions into this cube, I got an error msg saying it exceed the limits. I am trying to find out the limit but did not get anywhere. Does anyone know the max dimensions is allowed in a cube or the max intersection can be created for a cube?

wenchi

did you patrition your cube?

what exactly error message you have got?

how many dimensions (attribute hierarchies) has the cube?

|||

I think the theoretical limit is 2 billion attributes in a cube. If you could answer Vladimir's questions we might be able to help identify your issue.

cube limit

Hi all,

I am new to SSAS with SQL2005SP2. Currently, I have a fact table with almost 10 million records. In this table, I have at least 27 fields which store monthly data for the past 27 months along with some other fields. At first, I plan to make those 27 fields to one field and add another field like period to capture the year and month information so I can link to my time dimension table. Of course, this makes the new table with 270 million record counts. It took forever to process the cube. If I add more dimenstions into this cube, I got an error msg saying it exceed the limits. I am trying to find out the limit but did not get anywhere. Does anyone know the max dimensions is allowed in a cube or the max intersection can be created for a cube?

wenchi

did you patrition your cube?

what exactly error message you have got?

how many dimensions (attribute hierarchies) has the cube?

|||

I think the theoretical limit is 2 billion attributes in a cube. If you could answer Vladimir's questions we might be able to help identify your issue.

Cube browse in SSMS slow to start

I have a cube that has a couple hundred million rows and when I begin a cube browse, it takes a very long time for it to start. I profiled it and looked at the query it was running. It was querying for (what looked like) the totals for all measures at the All level for every dimension.

First of all, I would expect this to not take too long on my cube (no aggs) but I stopped the query after 45 minutes. (I know that adding some aggs to the cube would help and I will do that eventually.)

Second, how come does this query need to be run at the beginning of a browse? When the browse starts, there isn't anything in the data section of the pivot table.

How come is this necessary?

Mark

http://mgarner.wordpress.com

Try to see if this situation has anything to do with OWC.

Try to use MDX Editor in SQL Managemet studio. See what is taking most of the time.

Is it loading of metadata?

Try to send simple MDX query to bring only totals from your cube. Something like:

Select measures.members on 0 from [MyCube]

Run Profiler and see where the time is spent. 45 minutes looks bit excessive.

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

|||

Edward,

Thanks so much for your response.

I agree that 45 mins is a bit excessive. It could also be something that is wrong with my cube design, but I wanted to get some info about this start-up query anyways.

select filter([Measures].members, [Measures].currentmember.member_caption = [Measures].currentmember.member_caption) on columns from [myCube]

This is the query that is running when I start a browse session in SSMS. I noticed that the query looks the same and the same operations are executed when I run the query in an MDX query window as well. It takes the same amount of time.

Do you happen to know why this query is run each time a browse window is open?

I also noticed (in Profiler) that each time this query is run that it doesn't look like it is ever retrieved from cache. Is there some reason why this query can't be put into cahce? If not, can you think of a reason why my cube design might keep it from being placed in cache?

Also, when I take out the vast majority of my partitions and only leave 4 (which would be amost 10 million fact row lines) with 15% aggregations, the query takes about 1 minute. This also could be due to my server and/or SAN configuration, but I would still expect that a query that just returns the totals for all the measures at the [All] level for every dimension wouldn't take too long.

Thanks in advance,

Mark

http://mgarner.wordpress.com