Customising Dimension Security

HI, I'm having problems specifying customised security in the Advanced tab (allowed members). I'm fairly new to mdx script and am trying to restrict access to a STAFF dimension which contains a loginname member which I want to restrict access to based on username matching the loginname DIM. Have tried the following which didn't work:
[STAFF_DIM].[Login Name].members(MID( USERNAME, INSTR(1,USERNAME, "\") + 1, 128 ) )

I read somewhere that this can only be used on leaf layer members (this is the first of 3 layers), also should I be using the filter function? Any help with trying to setup would be great help.
Before using this expression for dimension security, you may want to do some experimentation first, by creating a simple MDX query including this expression, and verifying that it evaluates to the expected member...

Hope this helps

CustomData on SQL Server connection string?

Analysis Services has a CustomData connection string property. You can put anything on it, then inside an MDX query you can use the CustomData() function to retrieve whatever was on the connection string. This is helpful for passing in a security token.

Is there anything equivalent for SQL Server?

I also posted this here, but got no response so thought I'd check in the security forum.

I'm not sure that there is a property which is exactly suited for passing custom user data, but there is a property which you might modify without affecting pretty much anything else.

The property is "Application Name" (SSPROP_INIT_APPNAME).

|||

I see where you're going with that, but is there a SQL function to detect the application name from the connection string of the currently connected person?

|||

Hi,

What you can do to get the application name provided in the property is to run a statement like this:

select program_name from master..sysprocesses where spid=@.@.spid

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

CustomData on SQL Server connection string?

Analysis Services has a CustomData connection string property. You can put anything on it, then inside an MDX query you can use the CustomData() function to retrieve whatever was on the connection string. This is helpful for passing in a security token.

Is there anything equivalent for SQL Server?

I also posted this here, but got no response so thought I'd check in the security forum.

I'm not sure that there is a property which is exactly suited for passing custom user data, but there is a property which you might modify without affecting pretty much anything else.

The property is "Application Name" (SSPROP_INIT_APPNAME).

|||

I see where you're going with that, but is there a SQL function to detect the application name from the connection string of the currently connected person?

|||

Hi,

What you can do to get the application name provided in the property is to run a statement like this:

select program_name from master..sysprocesses where spid=@.@.spid

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Custom Semi-Additive Member using Analysis Service 2005 Standard Edition

[Edited For Clarity]

I’m using Analysis Service 2005 the standard edition:

I’m trying to create a custom semi-additive measure through MDX. My time dimension has month granularity with years on top of that. Basically, I’m trying to create an average the months while summing the averages across non time dimensions.

My best solution was using the following code:

avg(descendants([time].[hierarchy].currentmember,[time].[hierarchy].[month], self), sum(measures.[measure_to_aggregate]))

the problem with the above code is that it does not include empty months of non empty years. That is, if an attribute exists for a month in 2004, I want to see all the months of 2004 as part of the denominator. However, if an attribute does not exist for any months in 2004 I want to see Null.

I also tried the following code:

avg(descendants([time].[hierarchy].currentmember,[time].[hierarchy].[month], self), sum(measures.[measure_to_aggregate]), includeempty)

but the statement could not be parsed due to too many arguments in the avg function. Can anyone clarify what the problem is here?

Another way of doing this is using the count member. Basically I would count all the periods in the year and use that as a denominator to find the average:

sum([measures].[measure_to_aggregate]) / count (descendants ([time].[hierarchy].currentmember,[time].[hierarchy].[month], self))

The problem with this statement is that I can’t differentiate between empty years and non-empty years, so the code computes the average for all years in the dimension.I also tred using the nonempty function around descendants, but it excludes both months of empty and non-empty years.

Any ideas?

DNA,

You are saying that statement below is OK, and one problem is that it does not include empty cells.

avg(descendants([all year].[hierarchy].currentmember,[all year].[hierarchy].[period], self), sum(measures.[adjusted headcount]))

Try to use CoalesceEmpty:

avg(descendants([all year].[hierarchy].currentmember,[all year].[hierarchy].[period], self), sum(CoalesceEmpty(measures.[adjusted headcount],0)))

This will replace NULLs with 0.

Vidas Matelis

Edited: Added ,0

|||

Thanks for replying Vidas Matelis,

I have edited my post to make the previous post much more clear.

The issue with using Coalesce is that I have more years in my dimension than the cube itself.

Therefore I would not want to replace the null values of the addtional years in the dimension with zeros.

|||

Please note that includeempty is an option for Count(), but not for Avg(). How about using Count() with Filter(), like:

[measures].[measure_to_aggregate] / count(Filter(descendants(

[time].[hierarchy].currentmember, [time].[hierarchy].[month]),

Not IsEmpty(([measures].[measure_to_aggregate], [time].[hierarchy].Parent))))

|||

DNA,

I would assume that for years where you have no data, you would get result as 0. So what if you use IIF statement and replace 0 with NULL? Would that work?

IIF(avg(descendants([all year].[hierarchy].currentmember,[all year].[hierarchy].[period], self), sum(CoalesceEmpty(measures.[adjusted headcount],0))) = 0

, NULL

,avg(descendants([all year].[hierarchy].currentmember,[all year].[hierarchy].[period], self), sum(CoalesceEmpty(measures.[adjusted headcount],0)))

)

Vidas Matelis

|||

Thanks for the replies and ideas. For clarification:

To Deepak Puri,

I tried using filter but it excluded all empty values, meaning the empty values for periods for the years within the cube were exclude as well. The way I wanted to approach this is by excluding all empty values for the years that are not in the cube while including the empty periods for the years that are in the cube.

To: Vidas Matelis,

And an iif statement works very similiar to case statements and coalesceempty. When I used the coalesceempty I had to make a seperate calculated measure to use it because there was an error saying too many arguments in the avg() function. I could certainly try using the code you have provided but I would think that this code would also exclude all empty values instead of selectively excluding values for certain years.

|||

Ultimately, the code will have to know somehow that some years are considered "empty" and some are not. You are the only one who can tell what the criteria is. It could be as simple as hardcoding 2004 as the first non-empty year, or, perhaps you will have more complex criteria, i.e. year in which all months are empty for Root() of everything else, or something else. Let's assume that you created named set with all the months from the years that you consider "non-empty", i.e. something like

CREATE SET NonEmptyMonths AS Exists([Time].[Month].[Month], [Time].[Year].[2004] : NULL )

I.e. here I hardcoded 2004 as the first "non-empty" year. After that you can take any solution proposed above and use

Intersect(NonEmptyMonths, Descendants([Time].[Hierarchy].CurrentMember, [Time].[Hierarchy].Month))

Instead of using Descendants directly. You won't have to filter empty cells anymore, of course, because Intersect will take care of it.

|||

Would there be a way without hardcoding it?

The reason being is that I want it to be automated and to account for changes in the time dimension (ie. additional years and periods being added).

|||Please see my previous message - uou have to tell us the criteria - which years are considered to be "populated". Is it a member property of the Year attribute ? Is it year which has at least one non-empty month at the Root ? Is it something else ? Once we know you business logic we can translate it into MDX, but without knowing it - not much we can do.|||Mosha, sorry for the late reply, the years are considered to be populated if there is at least one non-empty months.|||

OK, then the CREATE SET statement can look like something around the following theme:

CREATE SET NonEmptyMonths AS Descendants( NonEmpty( [Time].[Hierarchy].[Year].MEMBERS ), [Time].[Hierarchy].[Month] )

|||

Something along the following lines:

CREATE SET NonEmptyMonths AS Descendants( NonEmpty([Time].[Hierarchy].[Year]), [Time].[Hierarchy].[Month] )

|||

CREATE SET NonEmptyMonths AS Descendants( NonEmpty([Time].[Hierarchy].[Year]), [Time].[Hierarchy].[Month] )

Custom Code in SSAS

Hi,

in AS2000 you could register an own dll to add functionality in MDX for calculations. What is the equivalent in AS2005?

Thanks...

The same except now it is a .NET assembly.

Mosha has some interesting examples on his web site and in his blog.

http://www.mosha.com

http://www.sqljunkies.com/WebLog/mosha/

Enjoy.

_-_-_ Dave

|||

Dave,

thanks, I'll have a look at it...

Curve fitting in MDX

I have a set of dimensions+facts from which I can derive a histogram of frequency by age (age in days, frequency in %).

I then use that histogram to forecast the distribution of an event over time - bascially by multiplying the magnitude of the event by the histogram.

The problem is, the histogram will always be noisey, especially when there's relatively little history in the cube, so what I want is to do some sort of area-preserving curve fit, and then use that computed curve instead of the raw histogram.

I've looked a little bit at data mining, but from what I understand, I don't think it can do what I need. I need this curve to be calculated for potentially 1000's of different slices of the cube, and I need it recalculated daily (or every time a report is run, ideally). It's not possible to "train" something with the curve ahead of time (which seems to be the core concept in data mining).

Borrowing the query from a previous thread...

with
member [Measures].[Offset] as
[Age].[Age].currentMember.memberValue
+ datediff(
"d",
[Date].[Date].currentMember.name,
StrToMember(@.Date).name
)

member [Measures].[Allocated] as
(
[Measures].[IRDC],
[Date].[Date].[All],
StrToMember("[Age].[Age].[" +
CStr([Measures].[Offset]) +

"]"
)
)

member [Measures].[Total] as
(
[Measures].[IRDC],
[Date].[Date].[All],
[Age].[Age].[All]
)

member [Measures].[Share] as
[Measures].[Allocated] / [Measures].[Total]

member [Measures].[ERDA] as
[Measures].[OEDA] * [Measures].[Share]

select
{
[Age].[Age].&[0]:[Age].[Age].&[0].lead(@.Window)
* [Measures].[ERDA]
} on columns,
{
order(
StrToMember(@.Date).lag(@.Window) : StrToMember(@.Date),
[Date].[Date].currentMember.memberValue,
desc
)
} on rows
from
[Cube]

In the above query, [Measures].[Share] is the histogram.

Any suggestions how to approach such a problem? Can a CLR "stored proc" be used to good advantage to do something like this? Any good references on how-to?

Anyone?|||

Here is an interesting discussion I had about the time series algorithm and whether it would be possible to get a different time series for thousands of different slices. The thought I had was that you could pivot the dataset yourself so that each slice had one row in the dataset and the time series (histogram) was represented as separate columns. Then you could train one regression model which might be decent at looking at the data for that particular row/slice and spitting out a prediction:

http://www.sqlserverdatamining.com/DMCommunity/Newsgroup/1898.aspx

Generally, if you know how you want it to predict the future, you may be able to write custom code and be smarter than any generic data mining algorithm. So looking into writing an MDX sproc would be my suggestion. As far as generic MDX stored proc examples, the following has lots of good examples:

http://www.codeplex.com/ASStoredProcedures

Also, you might keep your eyes peeled for some statistical functions to be added to that project. No ETA for those yet, but it may be soon. They are mentioned here, and though I'm not sure if any of those functions will help you, they might be good examples:

http://www.codeplex.com/ASStoredProcedures/Thread/View.aspx?ThreadId=2581

Also, SQL Server Samples contains a few stored proc examples. One might be somewhat helpful as an example as it loops over a set, does some calculations, then returns a number. If you have installed the latest (February 2007?) samples, this function can be seen at the following path: <C:\program files\Microsoft SQL Server\90\Samples\Analysis Services\Programmability\AMO\AMOAdventureWorks\CS\StoredProcedures\StoredProcedures.cs>

public static float WeightedAverage(Set set, String weightExpression, String inputExpression)
{
float expression;
float weight;

Expression weightExpr = new Expression(weightExpression);
Expression inputExpr = new Expression(inputExpression);

float total = 0;
float totalWeight = 0;

foreach (Tuple tuple in set)
{
expression = (float)inputExpr.Calculate(tuple);
weight = (float)weightExpr.Calculate(tuple);

total += expression * weight;
totalWeight += weight;
}

if (totalWeight > 0)
{
return total / totalWeight;
}
else
{
return 0;
}
}

A final thought would be that if you can model this in Excel with various Excel formulas, you can use Excel formulas directly in an MDX query.

Hope some of those suggestions help.

|||Thanks for the suggestions - I'll follow up on those in a couple weeks when I get back to the question of curve-fitting this data.