Help with cube measures

I'm relatively new to AS but have managed to get a data mart, dimensions and
cubes up and running in AS 2005 (SP1).

I'm having difficulty trying to do something seemingly easy with respect to
measures.

One of my dimensions is Accounts. I have about 7 additional dimensions
including Time.
I have 5 measures. One of these measures is heavily used most all of the
time.
I need to filter this measure by the Account dimension to include only
measures >= <defined_number>.
The other measures should reflect this change also, but I also need to
analyze other dimensions with or without the Accounts dimension in the
crosstab.
I need this to be put in the aggregations, not a temporary dynamic
calculation.

Aside from doing this at the database level, is their a way to do this in AS
either with a calculated member, named calculation or other?
I've been trying for a few days but just can't seem to get it.

Please, any help would be great.

-Troy

Let me see if I can go at this a different way.

If I try to create a calculated member as follows in the VS 2005 designer:

Name: Filtered Volumes

Parent Hierarchy: MEASURES

Expression: [Measures].[Export TEUS] >= 500.00

Format String: "#,#.0"

Visible: True

Non-Empty Behavior: Export TEUS

After processing the cube, the values for any and all cells is -1.0

Maybe if someone could explain the why to me, it could start the wheels turning more and perhaps help anyone viewing theis post.

Thanks,

-Troy

|||

What's happening with our calculated member is that the Expression "[Measures].[Export TEUS] >= 500.00" is evaulated and returns True which is being displayed as a numberic value and True is -1.0 while False is 0 in numeric terms. (Actually any non-zero value is consider True, but True generally converts to 1.0 or -1.0 depending on the system.)

I don't quite understand what you're trying to achieve, but what you might want to try doing is using something like if([Measures].[Export TEUS] >= 500.00, [Measures].[Export TEUS], Null). (If you are using Analysis Services 2000 both the second and third parameters to the iff functions will need to be of the same type so you'll need to change the null to something like 0.)

Another possibility, depending on what you are trying to do, is to use a the Filter() mdx function in an MDX query.

|||

Matt,

Your suggestion is exactly what I am trying to do. Thank you.

The expression is evaluating as I would have hoped. However, the measure is displaying NULL cells, which is one thing I don't want.

Could you suggest a way to prevent this? I have looked at NONEMPTY, but because of my lack of experience writing MDX I've had no luck.

Again, thanks for the help!!

|||

Here's an example of the use of non empty in an MDX Query in order to filter rows containing only nulls:

First the query that returns rows with null:

with member measures.a as iif([Measures].[Unit Sales]>500, [Measures].[Unit Sales], Null)
select {measures.a} on 0,
[Customer].[City].members on 1

Then the query with non empty added to remove the null rows:

with member measures.a as iif([Measures].[Unit Sales]>500, [Measures].[Unit Sales], Null)
select {measures.a} on 0,
non empty [Customer].[City].members on 1

Help scripting backup devices

Hi guys

I am relatively new to SQL Server admin and I have been handed a task of creating backup devices for a particular SQL Server which has 204 databases.

Rather than go through and create Full, Incramental and Transaction Log backup devices for each database is ther anyway of doing the following

Create a script to go through and for each database name in 'sysdatabases'

create a folder e.g \sqldata\backup\%databasename%

and within each folder create a backup device called

%databasename%_full
%databasename%_inc
%databasename%_log

Also all these databases are running in 'Simple' recovery mode so obviously I need to change this to 'Full' to enable incramental and log backups - is this possible using the same script.

Hope someone can help as the thought of doing all of this individually for each database scares me silly!!! :)

Thanks in advance for any help

Hanleynot sure if this is an option but could you create a database maintenance plan inwhich the option 'All databases' is set and does the backup?|||I could do that but I need to create the backup devices first, that is my problem, I need to automate the creation of 3 backup devices for each database (240 of them)

:)|||This is 100% untested, but it should give you an idea or two:DECLARE @.cDb sysname

DECLARE zDb CURSOR FOR SELECT
sd.name
FROM master.dbo.sysdatabases AS sd

OPEN zDb
FETCH zDb INTO @.cDb

WHILE 0 = @.@.fetch_status
BEGIN
EXECUTE ('EXECUTE master.dbo.xp_cmdshell ''mkdir z:\sqldata\backup\'
+ @.cDb + '''')

EXECUTE ('EXECUTE sp_adddumpdevice ''disk'', '''
+ @.cDb + '_full'', ''z:\sqldata\backup\' + @.cDb + '_full.dmp''')
EXECUTE ('EXECUTE sp_adddumpdevice ''disk'', '''
+ @.cDb + '_inc'', ''z:\sqldata\backup\' + @.cDb + '_inc.dmp''')
EXECUTE ('EXECUTE sp_adddumpdevice ''disk'', '''
+ @.cDb + '_log'', ''z:\sqldata\backup\' + @.cDb + '_log.dmp''')

EXECUTE ('ALTER DATABASE ' + @.cDb + ' SET RECOVERY FULL')

FETCH zDb INTO @.cDb
END

CLOSE zDb
DEALLOCATE zDb-PatP|||Pat P

Thanks very much for that

I'll give it a try on Monday

Much appreciated

:)|||Pat P

Script was successful, exactly what I was looking for.

Many thanks

Hanley

:) :)