Re: Processing dimensions based on huge table no longer works

#1

I think the SELECT DISTINCT that happen when you process the
fact-table-based dimensions must take a lot of time and resources.
Maybe you could try creating indexes on the appropriate columns? Does it
make sense?

Olivier.

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

Quote:

Hi, I've been running a database for about 4 months now, adding data daily
and incrementally updating dimensions daily.
This has worked for months, but suddenly everything freezes when I try to
process dimensions based on the fact table (huge). The dimensions based on

smaller tables work fine. This happens through the UI or DSO.

Quote:

The last thing I see is where it executes the query on the Oracle
database. Could it be that the dataset has become so large that the query

is now timing out?

Quote:

Any help would be appreciated, the data is now way behind...

ADAM

#2

That's already been done. To process one dimension it used to take 2 minutes. As the data grew, it was taking 45min - 1 hour. I think it may be timing out or something. There are only about 500 or so members in the dimension, but the table containing them has hundreds of millions of rows if not billions by now.
I think I may have hit some limit...

"Olivier Matrat" wrote:

Quote:

I think the SELECT DISTINCT that happen when you process the
fact-table-based dimensions must take a lot of time and resources.
Maybe you could try creating indexes on the appropriate columns? Does it
make sense?

Olivier.

"adami" <adami (AT) discussions (DOT) microsoft.com> wrote in message
news:6B7CAB03-ABBF-415C-A276-C0E469ACABE3 (AT) microsoft (DOT) com...
Hi, I've been running a database for about 4 months now, adding data daily
and incrementally updating dimensions daily.
This has worked for months, but suddenly everything freezes when I try to
process dimensions based on the fact table (huge). The dimensions based on
smaller tables work fine. This happens through the UI or DSO.

The last thing I see is where it executes the query on the Oracle
database. Could it be that the dataset has become so large that the query
is now timing out?
Any help would be appreciated, the data is now way behind...

ADAM

#3

You should build dimensional tables. That's proper data warehousing
technique. Building dimensions on fact tables is not a good practice.

-Sean

Sean Boon
Microsoft Office BI

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

Quote:

AB-SOL-U-TELY. Yes. Conclusive rather than valid I'd say.
It seems to me that it isn't going to work unless you do that.

Surely they can see that accessing ~500 rows is more beneficial than
accessing billions of rows.

Hobson's choice really. Good luck, you might need it

)

Regards
Jamie

"adami" wrote:

The fact table is already indexed & partitioned.
I've been battling with the Oracle team not to have any dimensions based
on columns in the fact table, but they wont listen because it still worked

(but slowly). Do you think this presents a valid argument for forcing them
to seperate all of the dimension data from the fact table?

Quote:

"Jamie" wrote:

Can you split your fact table based dimensions into seperate dimension
tables?

If so perhaps a ROLAP dimension would work for you.

If its Oracle you could partition the fact table.

Just some ideas...

Regards
Jamie Thomson

"adami" wrote:

Hi, I've been running a database for about 4 months now, adding data
daily and incrementally updating dimensions daily.
This has worked for months, but suddenly everything freezes when I
try to process dimensions based on the fact table (huge). The dimensions

based on smaller tables work fine. This happens through the UI or DSO.

Quote:

The last thing I see is where it executes the query on the Oracle
database. Could it be that the dataset has become so large that the query

is now timing out?

Quote:

Any help would be appreciated, the data is now way behind...

ADAM

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Processing dimensions based on huge table no longer works

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

Re: Processing dimensions based on huge table no longer works - 07-22-2004 , 12:37 PM

Re: Processing dimensions based on huge table no longer works - 07-22-2004 , 01:15 PM

Re: Processing dimensions based on huge table no longer works - 07-23-2004 , 04:32 PM