AS2005 OLAP Distinct Count - how to make it faster

#1

Reading the blog on distinct counts at
http://spaces.msn.com/members/denste...ew&_c=blogpart
It's suggesting a cube per distinct count partitioned over a range of
partitions.
Does the same apply in AS2005.. each distinct count in it's own measure group?

When creating a new measure group in BI Development Studio it doesn't allow
a fact table to be selected in more than 1 Measure Group. How do you create
multiple distinct counts (in multiple measure groups) from the one fact table?

Thanks

#2

Hello,

Based on my scope, we cannot create mulitple measure group based on a
single fact table in a cube. You may want to create different cube if you
really need this.

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===

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

--------------------

Quote:

Thread-Topic: AS2005 OLAP Distinct Count - how to make it faster
thread-index: AcXvOTQgNJOngWbKRbSQBAsobasAYA==
X-WBNR-Posting-Host: 203.202.23.100
From: "=?Utf-8?B?QWNpdXM=?=" <acius (AT) nospam (DOT) nospam
Subject: AS2005 OLAP Distinct Count - how to make it faster
Date: Mon, 21 Nov 2005 23:49:01 -0800
Lines: 11
Message-ID: <B815B0BF-D5DB-419E-A305-18E6145727B3 (AT) microsoft (DOT) com
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.sqlserver.olap
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.olap:64719
X-Tomcat-NG: microsoft.public.sqlserver.olap

Reading the blog on distinct counts at
http://spaces.msn.com/members/denste...ervices&_c11_b

logpart_blogpart=blogview&_c=blogpart

Quote:

It's suggesting a cube per distinct count partitioned over a range of
partitions.
Does the same apply in AS2005.. each distinct count in it's own measure
group?

When creating a new measure group in BI Development Studio it doesn't
allow
a fact table to be selected in more than 1 Measure Group. How do you
create
multiple distinct counts (in multiple measure groups) from the one fact
table?

Thanks

#3

you can create a logical view based on your fact table, then use this
logical view for your dcount measure group.

and , yes, its recommanded to create a dedicated measure group to aggregate
the data for the best performance.
but I have a standard measure group which include a dcount measure and the
performance was good for 50 millions of rows on a simple PC with 512mb of
ram!!!

"Acius" <acius (AT) nospam (DOT) nospam> wrote

Quote:

Reading the blog on distinct counts at
http://spaces.msn.com/members/denste...ew&_c=blogpart
It's suggesting a cube per distinct count partitioned over a range of
partitions.
Does the same apply in AS2005.. each distinct count in it's own measure
group?

When creating a new measure group in BI Development Studio it doesn't
allow
a fact table to be selected in more than 1 Measure Group. How do you
create
multiple distinct counts (in multiple measure groups) from the one fact
table?

Thanks

#4

Thanks,

Sounds like that's what I'll have to do.
I assume that means multiple reads of the fact table?

From a design point of view wouldn't it be better if they automatically
threw distinct counts in their own partition and left them (logically) in the
measure group relating to the fact table? (Since this is where the user would
normally be expecting them)

Regards
"JÃ©jÃ©" wrote:

Quote:

you can create a logical view based on your fact table, then use this
logical view for your dcount measure group.

and , yes, its recommanded to create a dedicated measure group to aggregate
the data for the best performance.
but I have a standard measure group which include a dcount measure and the
performance was good for 50 millions of rows on a simple PC with 512mb of
ram!!!

"Acius" <acius (AT) nospam (DOT) nospam> wrote in message
news:B815B0BF-D5DB-419E-A305-18E6145727B3 (AT) microsoft (DOT) com...
Reading the blog on distinct counts at
http://spaces.msn.com/members/denste...ew&_c=blogpart
It's suggesting a cube per distinct count partitioned over a range of
partitions.
Does the same apply in AS2005.. each distinct count in it's own measure
group?

When creating a new measure group in BI Development Studio it doesn't
allow
a fact table to be selected in more than 1 Measure Group. How do you
create
multiple distinct counts (in multiple measure groups) from the one fact
table?

Thanks

#5

yes, the result is multiple reads.
also the DCount read sort the query by the DCount column (I have not look at
the query, by I presume its like AS2000 because the loading process was long
due to the sort command)

"Acius" <acius (AT) nospam (DOT) nospam> wrote

Quote:

Thanks,

Sounds like that's what I'll have to do.
I assume that means multiple reads of the fact table?

From a design point of view wouldn't it be better if they automatically
threw distinct counts in their own partition and left them (logically) in
the
measure group relating to the fact table? (Since this is where the user
would
normally be expecting them)

Regards
"Jéjé" wrote:

you can create a logical view based on your fact table, then use this
logical view for your dcount measure group.

and , yes, its recommanded to create a dedicated measure group to
aggregate
the data for the best performance.
but I have a standard measure group which include a dcount measure and
the
performance was good for 50 millions of rows on a simple PC with 512mb of
ram!!!

"Acius" <acius (AT) nospam (DOT) nospam> wrote in message
news:B815B0BF-D5DB-419E-A305-18E6145727B3 (AT) microsoft (DOT) com...
Reading the blog on distinct counts at
http://spaces.msn.com/members/denste...ew&_c=blogpart
It's suggesting a cube per distinct count partitioned over a range of
partitions.
Does the same apply in AS2005.. each distinct count in it's own measure
group?

When creating a new measure group in BI Development Studio it doesn't
allow
a fact table to be selected in more than 1 Measure Group. How do you
create
multiple distinct counts (in multiple measure groups) from the one fact
table?

Thanks

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

AS2005 OLAP Distinct Count - how to make it faster

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

AS2005 OLAP Distinct Count - how to make it faster - 11-22-2005 , 01:49 AM

RE: AS2005 OLAP Distinct Count - how to make it faster - 11-22-2005 , 04:06 AM

Re: AS2005 OLAP Distinct Count - how to make it faster - 11-22-2005 , 07:13 AM

Re: AS2005 OLAP Distinct Count - how to make it faster - 11-22-2005 , 03:53 PM

Re: AS2005 OLAP Distinct Count - how to make it faster - 11-22-2005 , 08:04 PM