New to MDX. Help Needed with a Calculation Definition in my SSAS 2005 Cube

I'm new to MDX and I don't quite get the hang of it yet, so I'll try to provide all of the relevant information that I can so that you guys can help me.

My company has a lot of "Rolling 12 Month" metrics and I've been able to incorporate all but a few in my SSAS 2005 cube. By "Rolling 12", we refer to the 12-month period that ends with the previous month. So all "Rolling 12" metrics reviewed in December 2005 refer to the period of December 2004 through November 2005.

So for "Rolling 12 Month Invoices", I have the following calculation defined in my cube and it seems to be correct:

SUM( LASTPERIODS ( 12, [Invoice Date].[Invoice Month]), [Measures].[Invoice $])

Now I'm having trouble defining the # of customers in the Rolling 12 month period. I added a DistinctCount measure for # of customers and have been able to use it in the definition of 2 other metrics.

For example, for the # of customers for 2004, I have it defined as:

([Measures].[# of Customers] , [Invoice Date].[Invoice Year].&[2004])

And it appears to fine.

I tried similar variation of the following with no luck:

SUM(SUM( LASTPERIODS ( 12, [Invoice Date].[Invoice Month]), [Measures].[# of Customers]))

Your help is greatly appreciated!

EDIT: I'm using SQL Server 2005 Standard Edition
EDIT: Please let me know what other details you may need about my cube.A Distinct Count measure isn't strictly additive, so try Aggregate() rather than
Sum() to compute it over a set (only works with AS 2005):

>>
Aggregate(LASTPERIODS ( 12, [Invoice Date].[Invoice Month]),
[Measures].[# of Customers])
>>
http://msdn2.microsoft.com/en-us/library/ms145524(en-US,SQL.90).aspx
>>

Aggregate (MDX)

Returns a scalar value calculated by aggregating either measures or an optionally specified numeric expression over the tuples of a specified set.
...

Distinct Count

An aggregation across the fact data contributing to the subcube when the slicer axis includes a set.

Calculations on the set generate an error. Calculations below granularity of the set are ignored.

...
>>

|||Thank you very much!!!

New SQL Server 2000 Install - Backup of Empty Database never compl

We installed SQL Server 2000 SP3 and created a number of databases. However
after a while the backups of these databases appear to hang forever and
required a reboot to clear. We then created a brand new empty database and
tried backing this up, but had the same problem.
Looking in the Event Viewer the following errors:
The device, \Device\Scsi\aarich1, did not respond within the timeout period.
We mirrored disks set, so we tried splitting the mirror both ways and
running the backups, but had the same problem.
Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
Error: 17805, Severity: 20, State: 3
Invalid buffer received from client.
18204 :
BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
create. Operating system error = 3(error not found).
When the backup hangs the CPU on the server drops off to zero and the
server becomes very unresponsive.
The Operating sytem is Windows 2003
Anyone any ideas?
Regards Mike Kimber
mikekimber1,
That looks like a hardware problem to me. Have you changed the drivers
recently? If so, it could be a driver problem.
Also check that the location you are backing up to really exists. I know, an
obvious point, but there may be a type somewhere in your code.
Mark.
"mikekimber1" wrote:

> We installed SQL Server 2000 SP3 and created a number of databases. However
> after a while the backups of these databases appear to hang forever and
> required a reboot to clear. We then created a brand new empty database and
> tried backing this up, but had the same problem.
> Looking in the Event Viewer the following errors:
> The device, \Device\Scsi\aarich1, did not respond within the timeout period.
> We mirrored disks set, so we tried splitting the mirror both ways and
> running the backups, but had the same problem.
> Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> 18204 :
> BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
> create. Operating system error = 3(error not found).
> When the backup hangs the CPU on the server drops off to zero and the
> server becomes very unresponsive.
> The Operating sytem is Windows 2003
> Anyone any ideas?
> Regards Mike Kimber
>
|||Is drive W: a mapped drive? Try using UNC (i.e. \\server\share) instead of
mapped drives.
SCSI timeouts are significant enough and they could indicate that there is a
serious H/W problem. You should address them first and then check your
databases for corruption. Use CHECKPOINT, DBCC DROPCLEANBUFFERS and DBCC
CHECKDB to ensure that you are loading clean database pages from the disk.
Microsoft has several hotfixes for 17805 errors, but they are probably not
related to your backups.
Adrian
"mikekimber1" <mikekimber1@.discussions.microsoft.com> wrote in message
news:C73D1ABA-7494-440E-812A-365647D51A9D@.microsoft.com...
> We installed SQL Server 2000 SP3 and created a number of databases.
> However
> after a while the backups of these databases appear to hang forever and
> required a reboot to clear. We then created a brand new empty database and
> tried backing this up, but had the same problem.
> Looking in the Event Viewer the following errors:
> The device, \Device\Scsi\aarich1, did not respond within the timeout
> period.
> We mirrored disks set, so we tried splitting the mirror both ways and
> running the backups, but had the same problem.
> Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> 18204 :
> BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
> create. Operating system error = 3(error not found).
> When the backup hangs the CPU on the server drops off to zero and the
> server becomes very unresponsive.
> The Operating sytem is Windows 2003
> Anyone any ideas?
> Regards Mike Kimber
>

New SQL Server 2000 Install - Backup of Empty Database never compl

We installed SQL Server 2000 SP3 and created a number of databases. However
after a while the backups of these databases appear to hang forever and
required a reboot to clear. We then created a brand new empty database and
tried backing this up, but had the same problem.
Looking in the Event Viewer the following errors:
The device, \Device\Scsi\aarich1, did not respond within the timeout period.
We mirrored disks set, so we tried splitting the mirror both ways and
running the backups, but had the same problem.
Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
Error: 17805, Severity: 20, State: 3
Invalid buffer received from client.
18204 :
BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
create. Operating system error = 3(error not found).
When the backup hangs the CPU on the server drops off to zero and the
server becomes very unresponsive.
The Operating sytem is Windows 2003
Anyone any ideas?
Regards Mike Kimbermikekimber1,
That looks like a hardware problem to me. Have you changed the drivers
recently? If so, it could be a driver problem.
Also check that the location you are backing up to really exists. I know, an
obvious point, but there may be a type somewhere in your code.
Mark.
"mikekimber1" wrote:
> We installed SQL Server 2000 SP3 and created a number of databases. However
> after a while the backups of these databases appear to hang forever and
> required a reboot to clear. We then created a brand new empty database and
> tried backing this up, but had the same problem.
> Looking in the Event Viewer the following errors:
> The device, \Device\Scsi\aarich1, did not respond within the timeout period.
> We mirrored disks set, so we tried splitting the mirror both ways and
> running the backups, but had the same problem.
> Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> 18204 :
> BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
> create. Operating system error = 3(error not found).
> When the backup hangs the CPU on the server drops off to zero and the
> server becomes very unresponsive.
> The Operating sytem is Windows 2003
> Anyone any ideas?
> Regards Mike Kimber
>|||Is drive W: a mapped drive? Try using UNC (i.e. \\server\share) instead of
mapped drives.
SCSI timeouts are significant enough and they could indicate that there is a
serious H/W problem. You should address them first and then check your
databases for corruption. Use CHECKPOINT, DBCC DROPCLEANBUFFERS and DBCC
CHECKDB to ensure that you are loading clean database pages from the disk.
Microsoft has several hotfixes for 17805 errors, but they are probably not
related to your backups.
Adrian
"mikekimber1" <mikekimber1@.discussions.microsoft.com> wrote in message
news:C73D1ABA-7494-440E-812A-365647D51A9D@.microsoft.com...
> We installed SQL Server 2000 SP3 and created a number of databases.
> However
> after a while the backups of these databases appear to hang forever and
> required a reboot to clear. We then created a brand new empty database and
> tried backing this up, but had the same problem.
> Looking in the Event Viewer the following errors:
> The device, \Device\Scsi\aarich1, did not respond within the timeout
> period.
> We mirrored disks set, so we tried splitting the mirror both ways and
> running the backups, but had the same problem.
> Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> 18204 :
> BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
> create. Operating system error = 3(error not found).
> When the backup hangs the CPU on the server drops off to zero and the
> server becomes very unresponsive.
> The Operating sytem is Windows 2003
> Anyone any ideas?
> Regards Mike Kimber
>