question on using median to aggregate a measure

Hey all,

I'm still learning SSAS + MDX and am not sure how to go about using the MEDIAN function (instead of sum or avg) to aggregate my measure.

In my dataset, I have Geography, Time, and 'Use Type' dimensions that splice a Sale Value measure. I'd like to construct a measure that uses the Median instead of the traditional avg or sum aggregations.

I read the MSDN page on the median function (http://msdn2.microsoft.com/en-us/ms145570.aspx), but I'm still lost on what to do in the SSAS designer to add this measure. Any suggestions?

Thanks!

I'm not aware of a native "median" aggregation in SSAS - you could use the Median() function at the measure group fact dimension granularity, as in this Adventure Works example; but performance may be poor:

>>

With Member [Measures].[Median Sales Amount] as

Median(Exists([Sales Summary Order Details].[Sales Orders].[Order Line],,

"Sales Summary"), [Measures].[Sales Amount]),

NON_EMPTY_BEHAVIOR = [Measures].[Sales Amount],

FORMAT_STRING = "Currency"

select {[Measures].[Order Quantity], [Measures].[Average Sales Amount],

[Measures].[Median Sales Amount]} on 0,

Non Empty [Product].[Category].Members on 1

from [Adventure Works]

where [Date].[Calendar].[Calendar Year].&[2002]

--

Order Quantity Average Sales Amount Median Sales Amount
All Products 60,918 $8,308.44 $809.33
Accessories 5,207 $260.49 $60.56
Bikes 24,908 $7,509.60 $2,181.56
Clothing 16,927 $754.02 $91.18
Components 13,876 $5,142.58 $418.51

>>

|||Hi Deepak,

Yeah - I figured out how to use the Median function when creating arbitrary MDX expressions, but why can't you create preprocessed cubes using an arbitrary aggregation method? When I splice and dice my cube, I wanted the numbers to be the pre-processed Median at the level I was at, instead of the sum or average. Is this really not possible?|||Does anyone know if there's a way to create a custom aggregator? In this case, using Median instead of SUM/Avg?|||This is an interesting idea, but not possible in Analysis Services 2005.|||Hmm - actually, it sounds like I might be able to get what I want via MDX Scripting. Does anyone know if this is possible? Essentialy, I want a measure where every cell is computed as the median of whatever dimensional intersection is specified.

question on using median to aggregate a measure

Hey all,

I'm still learning SSAS + MDX and am not sure how to go about using the MEDIAN function (instead of sum or avg) to aggregate my measure.

In my dataset, I have Geography, Time, and 'Use Type' dimensions that splice a Sale Value measure. I'd like to construct a measure that uses the Median instead of the traditional avg or sum aggregations.

I read the MSDN page on the median function (http://msdn2.microsoft.com/en-us/ms145570.aspx), but I'm still lost on what to do in the SSAS designer to add this measure. Any suggestions?

Thanks!

I'm not aware of a native "median" aggregation in SSAS - you could use the Median() function at the measure group fact dimension granularity, as in this Adventure Works example; but performance may be poor:

>>

With Member [Measures].[Median Sales Amount] as

Median(Exists([Sales Summary Order Details].[Sales Orders].[Order Line],,

"Sales Summary"), [Measures].[Sales Amount]),

NON_EMPTY_BEHAVIOR = [Measures].[Sales Amount],

FORMAT_STRING = "Currency"

select {[Measures].[Order Quantity], [Measures].[Average Sales Amount],

[Measures].[Median Sales Amount]} on 0,

Non Empty [Product].[Category].Members on 1

from [Adventure Works]

where [Date].[Calendar].[Calendar Year].&[2002]

--

Order Quantity Average Sales Amount Median Sales Amount
All Products 60,918 $8,308.44 $809.33
Accessories 5,207 $260.49 $60.56
Bikes 24,908 $7,509.60 $2,181.56
Clothing 16,927 $754.02 $91.18
Components 13,876 $5,142.58 $418.51

>>

|||Hi Deepak,

Yeah - I figured out how to use the Median function when creating arbitrary MDX expressions, but why can't you create preprocessed cubes using an arbitrary aggregation method? When I splice and dice my cube, I wanted the numbers to be the pre-processed Median at the level I was at, instead of the sum or average. Is this really not possible?|||Does anyone know if there's a way to create a custom aggregator? In this case, using Median instead of SUM/Avg?|||This is an interesting idea, but not possible in Analysis Services 2005.|||Hmm - actually, it sounds like I might be able to get what I want via MDX Scripting. Does anyone know if this is possible? Essentialy, I want a measure where every cell is computed as the median of whatever dimensional intersection is specified.

Question on Settings in Connection

I've programmed a user defined function (SQL2000), which in a specific query
references a linked server (another SQL instance, BTW contained in same
physical server). The sintaxis is ok, but i couldn't apply the definition
because of following error:
"Error 7405: Heteogeneous queries require the ANSI_NULLS and ANSI_WARNINGS
options to be set for the connection. This ensures consistente
query semantics. Enable these options and then reissue your query."
I set the corresponding settings in both servers, section Connections of
Server's properties, but to no avail.
Which is the trick here? How is resolved the 'connection' issue referred in
the error message?
Thanks in advanceMiguel Castanuela (MiguelCastanuela@.discussions.microsoft.com) writes:
> I've programmed a user defined function (SQL2000), which in a specific
> query references a linked server (another SQL instance, BTW contained in
> same physical server). The sintaxis is ok, but i couldn't apply the
> definition because of following error:
> "Error 7405: Heteogeneous queries require the ANSI_NULLS and ANSI_WARNINGS
> options to be set for the connection. This ensures consistente
> query semantics. Enable these options and then reissue your query."
> I set the corresponding settings in both servers, section Connections of
> Server's properties, but to no avail.
> Which is the trick here? How is resolved the 'connection' issue referred
> in the error message?
The trick is to stop using Enterprise Manager for editing functions and
stored procedures. Use Query Analyzer instead, this is a far better tool
for the task.
The particular problem here, is that Enterprise Manager creates functions
and procedures with ANSI_NULLS and QUOTED_IDENTIFIER OFF, and these
settings are saved with the procedure/function. Thus you need to recreate
the function with ANSI_NULLS ON. (In Query Analyzer all needed options
are ON by default.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Many thanks, it resolves the problem.
In ahead I will take this great tip in account.
"Erland Sommarskog" wrote:

> Miguel Castanuela (MiguelCastanuela@.discussions.microsoft.com) writes:
> The trick is to stop using Enterprise Manager for editing functions and
> stored procedures. Use Query Analyzer instead, this is a far better tool
> for the task.
> The particular problem here, is that Enterprise Manager creates functions
> and procedures with ANSI_NULLS and QUOTED_IDENTIFIER OFF, and these
> settings are saved with the procedure/function. Thus you need to recreate
> the function with ANSI_NULLS ON. (In Query Analyzer all needed options
> are ON by default.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>

Question on Internal Activation Stored Procedure Security Context

CLR function has the following few lines which is invoked from Internal Activation Stored Procedure:

SqlCommand command = Connection.CreateCommand();

command.CommandText = "CREATE ASSEMBLY " + "\"" + AsmName + "\"" +" AUTHORIZATION [dbo]"+ " FROM " + "'" + regasm.UncPath + "'" + " WITH PERMISSION_SET=SAFE";

command.ExecuteNonQuery();

I am getting the following error:

"Could not impersonate the client during assembly file operation."

The CLR function is invoked from Service Broker internal activation stored procedure.

"SELECT user_name()" returns dbo just before CREATE ASSEMBLY execution.

SqlContext.WindowsIdentity.Name is "NT AUTHORITY\SYSTEM" as the Data Engine runs with the LocalSystem account.

How do I create a the necessary security context for "CREATE ASSEMBLY" to succeed ?

Service Broker Queue activation with EXECUTE AS = "SELF", "OWNER", domain account or dbo, all result in the above error. The Service Broker assembly having the internal activation stored procedure is registered "unsafe".

Many Thanks.

You have to mark the database trustworthy. Because the activated procedure is under an EXECUTE AS context, you are seeing all the problems described here: http://msdn2.microsoft.com/en-us/library/ms188304.aspx

HTH,
~ Remus

|||

Thanks for your reply. I have done "SET TRUSTWORTHY ON" to the DB initiating the dialog. But still "Could not impersonate client" error is thrown. I am using CERTIFICATES for dialog security. I have few "SELECT" statements inside the CLR Stored procedure, they execute fine; Looks CREATE ASSEMBLY is denied in this security context.

Will switching from 'NT AUTHORITY\SYSTEM' to a domain account just before "CREATE ASSEMBLY" will help ?

|||

The problem is not related to activation, but to EXECUTE AS USER = '...' context. The same error is returned if you run

EXECUTE AS USER='dbo';

and then try to run the CREATE ASSEMBLY statement (e.g. from a SQL Server Management Studio query window).

Under this context, after the database is marked trusthworthy, the CREATE ASSEMBLY succeeds if the login that 'dbo' is mapped to is made member of sysadmin server role.

HTH,
~ Remus

|||

BTW, if you need to avoid the syadmin membership requirement, the easiest workaround is to create the assembly from the assembly bits, not from a file. Since you're talking about an activated proc, I assume the assembly bits are available as a message payload (otherwise I really don't see the need to create an assembly in an activated procedure).

HTH,
~ Remu

|||

Yes..As you mentioned within SQL Server management studio, I had tried earlier and it works - with "EXECUTE AS USER = domain account" as well. But within CLR proc it fails. I tried including "EXECUTE AS" inside the transact SQL batch, the error remains.

I am creating the assembly from a network path (just sending this path as a broker message), so I may not be able to use assembly bits.

|||

Hi to all,

I am facing the same problem with the following line of

//code which i am trying

CREATE ASSEMBLY MyAssembly FROM 'C:\Documents and Settings\Administrator.ORC80\My Documents\Visual Studio 2005\Projects\MyDB1\MyDB1\bin\Debug\MyDB1.dll'

WITH PERMISSION_SET=SAFE

GO

//error i am getting

Msg 6585, Level 16, State 1, Line 1

Could not impersonate the client during assembly file operation.

please somebody help me...

|||Is the database marked as TRUSTWORTHY ?

Question on Internal Activation Stored Procedure Security Context

CLR function has the following few lines which is invoked from Internal Activation Stored Procedure:

SqlCommand command = Connection.CreateCommand();

command.CommandText = "CREATE ASSEMBLY " + "\"" + AsmName + "\"" +" AUTHORIZATION [dbo]"+ " FROM " + "'" + regasm.UncPath + "'" + " WITH PERMISSION_SET=SAFE";

command.ExecuteNonQuery();

I am getting the following error:

"Could not impersonate the client during assembly file operation."

The CLR function is invoked from Service Broker internal activation stored procedure.

"SELECT user_name()" returns dbo just before CREATE ASSEMBLY execution.

SqlContext.WindowsIdentity.Name is "NT AUTHORITY\SYSTEM" as the Data Engine runs with the LocalSystem account.

How do I create a the necessary security context for "CREATE ASSEMBLY" to succeed ?

Service Broker Queue activation with EXECUTE AS = "SELF", "OWNER", domain account or dbo, all result in the above error. The Service Broker assembly having the internal activation stored procedure is registered "unsafe".

Many Thanks.

You have to mark the database trustworthy. Because the activated procedure is under an EXECUTE AS context, you are seeing all the problems described here: http://msdn2.microsoft.com/en-us/library/ms188304.aspx

HTH,
~ Remus

|||

Thanks for your reply. I have done "SET TRUSTWORTHY ON" to the DB initiating the dialog. But still "Could not impersonate client" error is thrown. I am using CERTIFICATES for dialog security. I have few "SELECT" statements inside the CLR Stored procedure, they execute fine; Looks CREATE ASSEMBLY is denied in this security context.

Will switching from 'NT AUTHORITY\SYSTEM' to a domain account just before "CREATE ASSEMBLY" will help ?

|||

The problem is not related to activation, but to EXECUTE AS USER = '...' context. The same error is returned if you run

EXECUTE AS USER='dbo';

and then try to run the CREATE ASSEMBLY statement (e.g. from a SQL Server Management Studio query window).

Under this context, after the database is marked trusthworthy, the CREATE ASSEMBLY succeeds if the login that 'dbo' is mapped to is made member of sysadmin server role.

HTH,
~ Remus

|||

BTW, if you need to avoid the syadmin membership requirement, the easiest workaround is to create the assembly from the assembly bits, not from a file. Since you're talking about an activated proc, I assume the assembly bits are available as a message payload (otherwise I really don't see the need to create an assembly in an activated procedure).

HTH,
~ Remu

|||

Yes..As you mentioned within SQL Server management studio, I had tried earlier and it works - with "EXECUTE AS USER = domain account" as well. But within CLR proc it fails. I tried including "EXECUTE AS" inside the transact SQL batch, the error remains.

I am creating the assembly from a network path (just sending this path as a broker message), so I may not be able to use assembly bits.

|||

Hi to all,

I am facing the same problem with the following line of

//code which i am trying

CREATE ASSEMBLY MyAssembly FROM 'C:\Documents and Settings\Administrator.ORC80\My Documents\Visual Studio 2005\Projects\MyDB1\MyDB1\bin\Debug\MyDB1.dll'

WITH PERMISSION_SET=SAFE

GO

//error i am getting

Msg 6585, Level 16, State 1, Line 1

Could not impersonate the client during assembly file operation.

please somebody help me...

|||Is the database marked as TRUSTWORTHY ?

Question on exporting reports

I have 2 reports in my Access program(SQL DB) that I need to export into 1
text file. The OutputTo function works great for 1 file, but I can't figure
out an easy way to add the second report to the end of this text file.CD,
Might want to post that question to the Access NG.
From a SQL perspective, you can use the UNION clause to combine compatable
resultsets. The file can be generated by using OSQL, BCP (OUT) or the
easiest DTS.
HTH
Jerry
"CD" <CD@.discussions.microsoft.com> wrote in message
news:1D3CCE59-54C4-48E5-B4AE-2C1C88D43F79@.microsoft.com...
>I have 2 reports in my Access program(SQL DB) that I need to export into 1
> text file. The OutputTo function works great for 1 file, but I can't
> figure
> out an easy way to add the second report to the end of this text file.

question on date (or string after using convert function) comparis

select case when '02/12/2005' >= '01/02/2006' then 'T' else 'F' end
the result return is 'T' why? (2005 should be < 2006)
On Mon, 13 Feb 2006 19:05:26 -0800, kei wrote:

>select case when '02/12/2005' >= '01/02/2006' then 'T' else 'F' end
>the result return is 'T' why? (2005 should be < 2006)
Hi kei,
You are comparing two string constants. They may look like dates to you
and me (though probably not the same dates - I'm from the part of the
world that uses dd/mm/yyyy), but SQL Server doesn't try to interpret
what you write - it takes you literally.
You could try
SELECT CASE WHEN CAST('02/12/2005' AS datetime) >=
CAST('01/02/2006' AS datetime) THEN 'T' ELSE 'F' END
and pray that SQL Server interprets the ambiguous date format the same
way you do.
Or you could switch to a non-ambiguous date format:
SELECT CASE WHEN CAST('20051202' AS datetime) >=
CAST('20060201' AS datetime) THEN 'T' ELSE 'F' END
For more information, check Tibor Karaszi's article on SQL Server date
and time handling: http://www.karaszi.com/SQLServer/info_datetime.asp
Hugo Kornelis, SQL Server MVP

question on AqcuireConnection

Hi,

I created a function that supposedly checks a connection manager if a connection can be established.
When I was testing the code, I found out that the following code always returns "Successful" for flat files and SMTP connection even if the flat file does not even exist or there are no connection parameters for SMTP.

Can you tell me what to use to check if a connection can be established for all types of connection managers?

Here is the function that I created which does not seem to work as I expected:

Public Function connect(ByVal connMgr As Object) As String
Dim connected As String
Try
Dts.Connections(connMgr.name).AcquireConnection(Nothing)
Return "Successful"
Catch ex As Exception
Return "Failure"
Finally
Dts.Connections(connMgr.name).ReleaseConnection(Nothing)
End Try
End Function

Thanks.

AcquireConnection returns an object that represents the connection. You need to check this object. It will be different for each connection manager.

An ADO.NET connection manager will return an IDbConnection object.

The SMTP Connection manager simply returns the connection string of the connection manager.

There isn't a generic way to determine if a connection can be established for all types of connection managers.
|||

A flat file does not need to exist for AcquireConnection to be successful. It can point to a destination file to be created at the runtime.

Thanks.