MS SQL Server OLAP - AS2005 OLAP Distinct Count

Author

AS2005 OLAP Distinct Count - how to make it faster

Acius

2005-11-22, 3:24 am

Reading the blog on distinct counts at
http://spaces.msn.com/members/denst...iew&_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

Peter Yang [MSFT]

2005-11-22, 7:23 am

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.

--------------------
| Thread-Topic: AS2005 OLAP Distinct Count - how to make it faster
| thread-index: AcXvOTQgNJOngWbKRbSQ
BAsobasAYA==
| X-WBNR-Posting-Host: 203.202.23.100
| From: "=?Utf-8?B?QWNpdXM=?=" <acius@nospam.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@microso
ft.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/denst...Services&_c11_b
logpart_blogpart=blo
gview&_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
|

Jéjé

2005-11-22, 7:23 am

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@nospam.nospam> wrote in message
news:B815B0BF-D5DB-419E-A305- 18E6145727B3@microso
ft.com...
> Reading the blog on distinct counts at
> http://spaces.msn.com/members/denst...iew&_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

Acius

2005-11-22, 8:24 pm

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@nospam.nospam> wrote in message
> news:B815B0BF-D5DB-419E-A305- 18E6145727B3@microso
ft.com...
>
>
>

Jéjé

2005-11-22, 8:24 pm

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@nospam.nospam> wrote in message
news:A17A53E1-AC17-435A-9CCB- 73DF34025F8C@microso
ft.com...[color=darkred]
> 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:
>

Sponsored Links