Hi,

I have a big cube based on a Fact table from Oracle
database. Fact table has around 270 million rows, and cube
has measures with Distinct count and count.
It took about 40 hours to generate this cube based on
HOLAP but the performance was bad while accessing the data
from Excel.
With MOLAP it took about 9 days, but the performance is
great.

Now my question is we need to process this cube every
month after adding every months worth of data. Obviously
we can not loose 9 days to re-process this cube whenever
we add new month's data. Is there any way to overcome this
problem?

I tried to explore creating two cubes; one is Static cube
with all previous years data and one as dynamic cube with
every months data till we roll in to another year. And a
virtual cube based on both of them, but I had troubles
merging the time dimensions.

Any idea in this regard is appreciated.

Thanks

Hi Shiva,

You should use partitioning for this cube. It should give you shorter
processing times. I'd also recommend that you watch temp file creation
during processing as you are more likely to encounter this since you are
using the disctinct count aggregate type. A lot of this material is covered
in the Analysis Services Performance Guide which is available on MSDN.


--
Sean

--
Sean Boon
SQL Server BI Product Unit

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.





"Shiva" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Hi Sean,

That's what exactly I was looking for. Thanks for the
update.

The cube which took 9 days to process has time dimension
as changing dimension. So I cannot divide this in to
partitions and need to create it from scratch.

Document says I can use DSO to process cubes in parallel
through SQL server. But I'm not using SQL server at all.
My data comes from Oracle database. Do you recommend any
other DSO tool? Any steps/document on processing cubes in
parallel while creating the cube would be great.

Thanks
Shiva

Two observations:

1) Have you ran the Optimize Schema wizard in the Cube Editor? You may be
having long processing times because of the complex joins being issued from
an unoptimized schema on the Analysis Services' side. For example if you
have 20 dimensions, and if you look at the SQL statements that AS issues to
the RDBMS (SQL Server or Oracle), you will notice that it issues a 21-way
inner join between the fact table and *all* of the dimension tables. If you
"optimize the schema" using the option from the Cube Editor, then you can
reduce that significantly. This is discussed in both the AS Performance
Guide and the AS Operations Guide pointed to from:
http://www.microsoft.com/sql/evaluat...bianalysis.asp

2) To process partitions in parallel, I would recommend that you look at
running the Parallel Processing Utility (PPU) located at:
http://www.microsoft.com/downloads/d...DisplayLang=en

--
Dave Wickert [MS]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Practices Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


<anonymous (AT) discussions (DOT) microsoft.com> wrote

Hi Dave,

Thanks for updating about PPU utility. I downloaded it and
currently it is processing 2 partitions in parallel. It
was easy to learn and the document along with it really
helped.

Hope this could save me some time.

Thanks
Shiva
e02cf13cbdb4&DisplayLang=en

Hi Dave,

The link from the page
http://www.microsoft.com/sql/evaluat...bianalysis.asp to the AS
operations guide seems to be broken, at least I cannot find it at the page
http://www.microsoft.com/technet/pro...n/default.mspx
I'm forwarded to.

Can you help me?

Thanks

Frank

"Dave Wickert (Microsoft)" <dwickert (AT) online (DOT) microsoft.com> wrote

Yes, I've seen that too. And I have folks working on it.

Over the weekend, the Technet site was re-designed/re-structured.
The AS Operations Guide was just recently posted and got dropped from their
content worksheet until early last week.
They tell me that it should be fixed in a couple of days.
Sorry about that.
--
Dave Wickert [MS]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Practices Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Frank Samuel" <FSamuel_nospam (AT) misag_remove_spam (DOT) com> wrote

It is *live*.
New url is:
http://www.microsoft.com/technet/pro.../anservog.mspx
Sorry for the delay.
--
Dave Wickert [MS]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Practices Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Dave Wickert [MSFT]" <dwickert (AT) online (DOT) microsoft.com> wrote