Calculated measure Vs data source view

From: newbie via SQLMonster.com (forum_at_SQLMonster.com)
Date: 01/17/05 

Date: Mon, 17 Jan 2005 14:58:13 GMT

I have a problem for which i have 2 approaches - calculated measures and data source view. Can someone please help decide which would be better. alternate solutions most welcome.

Fact
====
Field1 Field2 ProductID
A Z ...
B null ...
C X ...
C null ...

Field2 points to a dimension
============================

Field1 is not really a "true dimension".
========================================

measures needed
===============
distinct count of ProductID when Field1 is A or B
distinct count of ProductID when Field1 is B or C
distinct count of ProductID when Field2 is not null

Calculated measure approach
===========================

1. Need to define Field1 and Field2 as dimensions - while this makes sense for Field2, it does not for Field1 - i mean it is not really a dimension in the true sense. It holds something like "table that was the source of data"

2. Will calculated members give good performance when measure is distinct count?

3. The 3 calculated measures would be
[Measure].[Productcount],[Field1].[A] + [Measure].[Productcount],[Field1].[B]

[Measure].[Productcount],[Field1].[B] + [Measure].[Productcount],[Field1].[C]

[Measure].[Productcount],[Field2].[not null] - how do I define this not null bit?

Data Source View approach
=========================
3 views in the dsv -
select * from table where Field1 in (A,B)
select * from table where Field1 in (B,C)
select * from table where Field2 is not null

And define 3 separate measure groups on these. Will these views need to be indexed (They are not complicated views with joins or anything)

thanks in advance 

-- 
Message posted via http://www.sqlmonster.com

Relevant Pages

  • Re: Calculated measure Vs data source view
    ... >data source view. ... > distinct count of ProductID when Field1 is A or B ... > distinct count of ProductID when Field2 is not null ...
    (microsoft.public.sqlserver.olap)
  • Re: How to find Minimum of four fields
    ... SELECT Id, Field1, Field2, "PhoneCost" AS CostType, PhoneCost AS CostAmount ...
    (microsoft.public.access.gettingstarted)
  • Re: How to find Minimum of four fields
    ... Let's assume you currently have a table with Id, Field1, Field2, WebCost, ... PhoneCost, StoreCost, and ShowCost. ...
    (microsoft.public.access.gettingstarted)
  • RE: Querying by multiple dates
    ... UNION ALL SELECT Field1, Field2, Date2 AS VisitDate ...
    (microsoft.public.access.queries)
  • Re: Copying a Field
    ... Since Field1 has no value on a new record, niether will Field2. ... > field is the primary key and a second field will for the some of the time ... > Debbie D. ...
    (microsoft.public.access.forms)