Aggregation advice

Hi, I am having some trouble with changing the default aggregation behavior of my cube. I need to be able to change the aggregation at different levels of a dimension.

I am currently exploring the possibility of using custom member formulas, placing MDX script into a column within my dimension meaning that I could define a custom roll up at each level in my hierarchy – is this the best way?

I have two Dimensions as part of a pivot table:
DIMTime = [Year].[Quarter].[Hour]
DIMSpatial = [Group_1].[Group_0].[Route].[Link]
And a calculated measure = SUM(x)/SUM(N)

When I ‘roll up’ (total) this is what I am currently getting:

DIM Time

Group_0 2006

1 0.9398

2 0.9471

Total 0.9424

I want to change the roll up to be 0.9398 + 0.9471 = 1.8869

Could anyone give me some guidance on how the MDX Script should look? I have been trying lots of combinations but keep getting a #Value! Error. I haven’t yet been able to implement a custom aggregation so if I am going down the wrong avenue or if you think there is a better way of doing this please let me know…

Thanks in advance for any advice you can give me.

Simon

If you want the roll up to occur from fact table granularity, you could define a Named Calculation like 'x/N', then define a "sum" measure on it (assuming that the 2 measures are in the same fact table/measure group). If they are in different fact tables/measure groups, you might be able to use a measure expression|||

Thanks for your post Deepak and sorry for the slow response.

You are right in assuming that the 2 measures are in the same fact table. In trying to explain the problem I have over simplified my example in the first post. I have tried to put together this illustration

to explain further:

[On the left is what I am trying to achieve and on the right is what I am getting right now]

Click Here

I see from your description above that the default ‘SUM’ aggregation will work. But if I now wanted to ‘/n’ at each level in the hierarchy how would I go about changing this behaviour?

edit: Adding link to diagram instead

Aggregation advice

Hi, I am having some trouble with changing the default aggregation behavior of my cube. I need to be able to change the aggregation at different levels of a dimension.

I am currently exploring the possibility of using custom member formulas, placing MDX script into a column within my dimension meaning that I could define a custom roll up at each level in my hierarchy – is this the best way?

I have two Dimensions as part of a pivot table:
DIMTime = [Year].[Quarter].[Hour]
DIMSpatial = [Group_1].[Group_0].[Route].[Link]
And a calculated measure = SUM(x)/SUM(N)

When I ‘roll up’ (total) this is what I am currently getting:

DIM Time

Group_0 2006

1 0.9398

2 0.9471

Total 0.9424

I want to change the roll up to be 0.9398 + 0.9471 = 1.8869

Could anyone give me some guidance on how the MDX Script should look? I have been trying lots of combinations but keep getting a #Value! Error. I haven’t yet been able to implement a custom aggregation so if I am going down the wrong avenue or if you think there is a better way of doing this please let me know…

Thanks in advance for any advice you can give me.

Simon

If you want the roll up to occur from fact table granularity, you could define a Named Calculation like 'x/N', then define a "sum" measure on it (assuming that the 2 measures are in the same fact table/measure group). If they are in different fact tables/measure groups, you might be able to use a measure expression|||

Thanks for your post Deepak and sorry for the slow response.

You are right in assuming that the 2 measures are in the same fact table. In trying to explain the problem I have over simplified my example in the first post. I have tried to put together this illustration

to explain further:

[On the left is what I am trying to achieve and on the right is what I am getting right now]

Click Here

I see from your description above that the default ‘SUM’ aggregation will work. But if I now wanted to ‘/n’ at each level in the hierarchy how would I go about changing this behaviour?

edit: Adding link to diagram instead

Agent Job logs on using Default User Profile

Hi,

I get a strange problem whereby I have set up a Sql Server Agent job to run as a particular user. The job needs to access details from the users local profile.

The job fails to access the correct user profile when it logs in, and utilises the 'Default User' profile. This happens for scheduled jobs when the actual user used to run the job as is not logged into the machine where the Sql Server instance lives.

If, however, the user IS logged in at the time of job execution, the correct profile is loaded.

I can see this happening by simplying executing an OS command that kicks of a batch file with something like the following:

echo %USERPROFILE%
whoami

When the user is logged in I see the results as expected, eg:

UserProfile = C:\Documents and Settings\myUser
whoami=myDomain\myUser

However when the user is not logged on at the time of execution I get the following:

UserProfile = C:\Documents and Settings\Default User
whoami= myDomain\myUser

Any suggestions would be much appreciated....Its starting to do my head in...

Thanks.

What are associated privileges for that user to run as a SQLAgent account, in general if that job performs any copy or movement within network then the SQLAGent user account must need relevant privileges to complete that task.|||

Thanks for the reply..

At this stage the only thing my job is doing is outputing the results of whoami and %USERPROFILE% to a txt file. The job doesn't fail - which I would expect if there was a permissions problem. It just displays the %USERPROFILE% as Default User, when executing via a proxy account (not the SQL Agent Acc) - even though the whoami command shows the correct user is logged in.

Any ideas which privileges need to be applied to both the SQL Agent account, and the account which the proxy maps to?

|||Because of the Proxy usage this is the behaviour, I think. Refer to the books online about SQLAgent operator role

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/719ce56b-d6b2-414a-88a8-f43b725ebc79.htm

|||

The user that I am trying to get the step to log on as is a member of the sysadmin Server Role, so it should have access to all the Sql Server Agent functionality anyway - whether or not it has the SQLAgent operator role assigned..

The thing that has me really confused, is why can the Agent correctly logs the user in to execute the job (ie load the correct profile), ONLY when that same user is currently logged into the machine SQL Server is running on?

Is there maybe some way I could log the user in first, then execute the required step - ensuring that the user is logged out post execution - the thing is though I shouldn't need to do that...aaahhh..

thanks for our help...

Agent Job logs on using Default User Profile

Hi,

I get a strange problem whereby I have set up a Sql Server Agent job to run as a particular user. The job needs to access details from the users local profile.

The job fails to access the correct user profile when it logs in, and utilises the 'Default User' profile. This happens for scheduled jobs when the actual user used to run the job as is not logged into the machine where the Sql Server instance lives.

If, however, the user IS logged in at the time of job execution, the correct profile is loaded.

I can see this happening by simplying executing an OS command that kicks of a batch file with something like the following:

echo %USERPROFILE%
whoami

When the user is logged in I see the results as expected, eg:

UserProfile = C:\Documents and Settings\myUser
whoami=myDomain\myUser

However when the user is not logged on at the time of execution I get the following:

UserProfile = C:\Documents and Settings\Default User
whoami= myDomain\myUser

Any suggestions would be much appreciated....Its starting to do my head in...

Thanks.

What are associated privileges for that user to run as a SQLAgent account, in general if that job performs any copy or movement within network then the SQLAGent user account must need relevant privileges to complete that task.|||

Thanks for the reply..

At this stage the only thing my job is doing is outputing the results of whoami and %USERPROFILE% to a txt file. The job doesn't fail - which I would expect if there was a permissions problem. It just displays the %USERPROFILE% as Default User, when executing via a proxy account (not the SQL Agent Acc) - even though the whoami command shows the correct user is logged in.

Any ideas which privileges need to be applied to both the SQL Agent account, and the account which the proxy maps to?

|||Because of the Proxy usage this is the behaviour, I think. Refer to the books online about SQLAgent operator role

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/719ce56b-d6b2-414a-88a8-f43b725ebc79.htm

|||

The user that I am trying to get the step to log on as is a member of the sysadmin Server Role, so it should have access to all the Sql Server Agent functionality anyway - whether or not it has the SQLAgent operator role assigned..

The thing that has me really confused, is why can the Agent correctly logs the user in to execute the job (ie load the correct profile), ONLY when that same user is currently logged into the machine SQL Server is running on?

Is there maybe some way I could log the user in first, then execute the required step - ensuring that the user is logged out post execution - the thing is though I shouldn't need to do that...aaahhh..

thanks for our help...

After Install sql 2005 by default

Is sql2005 sercurity logins and password case-sensitive? I installed by
default nothing special and no select any case sensitive box.
Thanks
If the collation selected during installation is the default
Latin1_General_CI_AS (Case insensitive, Accent Sensitive), the login name
should be case insensitive, but the password will be always sensitive.
Gilberto Zampatti
"mecn" wrote:

> Is sql2005 sercurity logins and password case-sensitive? I installed by
> default nothing special and no select any case sensitive box.
> Thanks
>
>

After Install sql 2005 by default

Is sql2005 sercurity logins and password case-sensitive? I installed by
default nothing special and no select any case sensitive box.
ThanksIf the collation selected during installation is the default
Latin1_General_CI_AS (Case insensitive, Accent Sensitive), the login name
should be case insensitive, but the password will be always sensitive.
Gilberto Zampatti
"mecn" wrote:
> Is sql2005 sercurity logins and password case-sensitive? I installed by
> default nothing special and no select any case sensitive box.
> Thanks
>
>

After Install sql 2005 by default

Is sql2005 sercurity logins and password case-sensitive? I installed by
default nothing special and no select any case sensitive box.
ThanksIf the collation selected during installation is the default
Latin1_General_CI_AS (Case insensitive, Accent Sensitive), the login name
should be case insensitive, but the password will be always sensitive.
Gilberto Zampatti
"mecn" wrote:

> Is sql2005 sercurity logins and password case-sensitive? I installed by
> default nothing special and no select any case sensitive box.
> Thanks
>
>

Affecting performance?

I have a table with 23 columns. 8 columns are not always filled by users and so valued to default. Would this affect the overall performance of querying this table? Should I separate those 8 columns and link with a one-to-one relation?

Thanks

Any idea?|||From a database design point of view I would seperate them.

Affecting performance?

I have a table with 23 columns. 8 columns are not always filled by users and so valued to default. Would this affect the overall performance of querying this table? Should I separate those 8 columns and link with a one-to-one relation?

Thanks

Any idea?|||From a database design point of view I would seperate them.