OLAP 2005 Calculated Measure using Field/Dimension

I am building an OLAP Cube off someone else's data model, so i am a bit stuck trying to decision off model decisions already made and being used.

They have a field for Satisfaction which has the value Yes, No, and Blank when no survey result was received.

I need to make a satisfaction percent based on (count of Yes) / (count of Yes + count of No). So i do not count blank when no result was received.

I need the caculated measure to work against all of the dimensions, so i do not want to build such a specific MDX code that it only works with Time, for example.

Help will be extremely Appreciated

*A

If there is a Satisfaction dimension/attribute, with the members above: Yes, No, and Blank, and a cube "count" measure like SurveyCount, then does something like this work:

([Measures].[SurveyCount], [Satisafaction].[Satisfaction].[Yes])/

(([Measures].[SurveyCount], [Satisafaction].[Satisfaction].[Yes])

+ ([Measures].[SurveyCount], [Satisafaction].[Satisfaction].[No]))

|||WORKED GREAT AND WAS REALLY EASY TO UNDERSTAND AND CHANGE FOR OTHER USES!!!!!!!sql

OLAP 2005 Calculated Measure using Field/Dimension

I am building an OLAP Cube off someone else's data model, so i am a bit stuck trying to decision off model decisions already made and being used.

They have a field for Satisfaction which has the value Yes, No, and Blank when no survey result was received.

I need to make a satisfaction percent based on (count of Yes) / (count of Yes + count of No). So i do not count blank when no result was received.

I need the caculated measure to work against all of the dimensions, so i do not want to build such a specific MDX code that it only works with Time, for example.

Help will be extremely Appreciated

*A

If there is a Satisfaction dimension/attribute, with the members above: Yes, No, and Blank, and a cube "count" measure like SurveyCount, then does something like this work:

([Measures].[SurveyCount], [Satisafaction].[Satisfaction].[Yes])/

(([Measures].[SurveyCount], [Satisafaction].[Satisfaction].[Yes])

+ ([Measures].[SurveyCount], [Satisafaction].[Satisfaction].[No]))

|||WORKED GREAT AND WAS REALLY EASY TO UNDERSTAND AND CHANGE FOR OTHER USES!!!!!!!

odd TEXT type field problem

Hi,
There is a db with 200GB, with ~160 GB data in it. Most of the data was a
TEXT type field in a table. That field has been dropped and added back with
default NULL but it appears I still have ~160 GB of data.
How is that possible?
updatestats would help?
The table in question has a two field composite clustered index, but not on
the TEXT type field.
The truth is the whole thing is very fragmented. Defragmenting the clustered
index would place exclusive lock on the table?
Your help would be appreciated,
JanosYou can try running DBCC CLEANTABLE on the table that had the TEXT column.
Look up syntax in BOL.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Janos Horanszky" <kob_uki@.hotmail.com> wrote in message
news:emmBIbZEFHA.3368@.TK2MSFTNGP10.phx.gbl...
> Hi,
> There is a db with 200GB, with ~160 GB data in it. Most of the data was a
> TEXT type field in a table. That field has been dropped and added back
with
> default NULL but it appears I still have ~160 GB of data.
> How is that possible?
> updatestats would help?
> The table in question has a two field composite clustered index, but not
on
> the TEXT type field.
> The truth is the whole thing is very fragmented. Defragmenting the
clustered
> index would place exclusive lock on the table?
> Your help would be appreciated,
> Janos
>|||I appreciate Adam, I will try it on our test environment, I let you know
asap.
Janos
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eDFFq6dEFHA.1012@.TK2MSFTNGP14.phx.gbl...
> You can try running DBCC CLEANTABLE on the table that had the TEXT column.
> Look up syntax in BOL.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Janos Horanszky" <kob_uki@.hotmail.com> wrote in message
> news:emmBIbZEFHA.3368@.TK2MSFTNGP10.phx.gbl...
> with
> on
> clustered
>

Odd null behavior

I am writing an upsert proc that should detect the change in state for a record. The change in state happens when a particular date field (default null) is populated. However, I can not get a record set that detects the changes properly.

Here is an example
set ANSI_NULLS on
go
create table #t1
(
ID int,
DateField datetime
)

create table #t2
(
ID int,
DateField datetime
)

insert into #t1 (ID, DateField) values (1, '7/20/2006')
insert into #t2 (ID, DateFIeld) values (1, null)

select * from #t1 join #t2 on #t1.ID = #t2.ID where #t1.DateField <> #t2.DateField

drop table #t1
drop table #t2

The select should return a record because NULL does not equal '7/20/2006' but it doesn't.
What am I missing?

Thanks in advance.

straight out of BOL:

"Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown."

use the IS NULL clause to assist.

HTH,

Derek

|||Then why does this return a record?:

set ANSI_NULLS off
go
declare @.var datetime
select @.var = null

create table #t1
(
ID int,
DateField datetime
)

insert into #t1 (ID, DateField) values (1, '7/20/2006')

select * from #t1 where #t1.DateField <> @.var

drop table #t1|||

ANSI NULLS setting only affects comparisons of the form:

column <Operator> @.value

column <Operator> literal

value <Operator> @.value

value <Operator> literal

and combinations thereof. The column to column comparison will always follow the ANSI semantics for NULL value comparison. So you could write your SELECT like:

-- Depends on whether you have datetime values with the default value or not

select * from #t1 join #t2 on #t1.ID = #t2.ID

where #t1.DateField <> coalesce(#t2.DateField, '')

--or

select * from #t1 join #t2 on #t1.ID = #t2.ID

where #t1.DateField <> #t2.DateField

or (#t1.DateField IS NULL and #t2.DateField IS NOT NULL)

or (#t1.DateField IS NOT NULL and #t2.DateField IS NULL)