Hi everyone, I was hoping someone could help me.

I have implemented a simple calculated member that counts the number of
patron memberships. It is defined as follows:

sum(PeriodsToDate(date.[(all)],date.CurrentMember),[Measures].[Number of
Memberships])

I am finding that I am getting inconsistent results if i compare it to my
simple SQL query that queries the very same sql table used to construct the
fact table within analysis server, namely membership_table. Note also that
the column name 'membership' (in the SQL query) is used to construct the
measure [Measures].[Number of Memberships] above:

select sum(memberships)
from membership_table
where date <= '20051231'

What I find is that my sql statement (as i vary the hard coded date) gives
consistent results at the Day 'level' through the years 1980 - 2004. But as
soon as I hit mid 2005, i start to generate inconsistent results, with
differences being a few memberships. The differences remain constant for a
period of time then jump. for example from March 05 to September 05 the
difference between the membership numbers is 2. From October 05 to December
05 they are 250.

The result generated by the Cube is always less than the true value when
these inconsistencies occur.

I know for a fact that the SQL statement generates the correct results
(through comparison with other reports), however the Cube is not giving me
the right results.

Would anyone know what might be going on? Any help would be really
appreciated!

Kindest regards,
John Bright

Hi John,


Have you tried to reconcile [Measures].[Number of
Memberships] with the SQL memberships data on individual days in the
offending range (say, Oct-Dec)?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks Deepak!

I did try reconcile, however that wasnt the problem.

What I found was that OLAP was not taking into account DATES with non-zeroed
times.

For example, it would only consider dates of the following format:
2005-09-16 00:00:00.000

I disregarded dates of the following format: 2005-09-16 12:45:32.153

I had to do a convert/case to format all dates to have zero time values. Now
I am getting all correct results.

This is really wierd. Is this what is suppose to be happening?

Many thanks
John


"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

John,

If your date dimension table key is a datetime field, then it will only
join/match to a fact datetime field where both and time are identical -
that would be expected.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks Deepak,

You are absolutely right! Thanks for that clarification.

cheers, John

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote