Another way than using a Cursor

Hi all,
I have the following example Table & Data...
Create Table Test
(
Cust_Code varchar(10),
Acc_Deal_Type Varchar(3),
Account_No Varchar(30),
AccountTypes Varchar(50)
)
Insert Into Test (Cust_Code, Acc_Deal_Type, Account_No)
Values ('123456','MMC','12345678')
Insert Into Test (Cust_Code, Acc_Deal_Type, Account_No)
Values ('123456','MMC','12345679')
Insert Into Test (Cust_Code, Acc_Deal_Type, Account_No)
Values ('123456','CCL','12345680')
Insert Into Test (Cust_Code, Acc_Deal_Type, Account_No)
Values ('123457','MMC','12345681')
Insert Into Test (Cust_Code, Acc_Deal_Type, Account_No)
Values ('123457','MMC','12345682')
I need to concatanate the distinct values of Acc_Deal_Types Column for
each Cust_Code and set the AccountTypes column with the Result.
So, in this example, the grouped results would be two rows with the
Cust_Code & AccountTypes Column selected:
1st Row
Cust_Code = 123456
AccountType = MMC,CCL
2nd Row
Cust_Code = 123457
AccountType = MMC
How can I create the AccountTypes column without using a cursor? The
Table in question has around 7000 rows.
Thanks
Barrytry
select distinct Acc_Deal_Type + Cust_Code as NewType into Test# from Test
update Test
set Acc_Deal_Type = NewType
-- select Cust_Code, Acc_Deal_Type, Account_No, AccountTypes , NewType
from Test a, Test# b
where NewType = Acc_Deal_Type + Cust_Code
increase the zize of the Acc_Deal_Type though|||Hi, Barry
See: http://www.aspfaq.com/show.asp?id=2529
Razvan

Another example of incomplete support of some legal database names

It has already been reported that the View tool in Enterprise Manager
cannot handle databases which have a period in their name (e.g.
"mydomain.com"). It doesn't show the individual field names.
Today I found a second problem: when scheduling a Job under SQL Server
Agent, in the New Step dialog you are prompted for a database name.
Databases with a period in their name are not listed. Tomorrow I'll
use ISQL to schedule the job in Windows Scheduler.
Hopefully MSFT will add these tests to the test scripts for the next
version and service pack (but don't hold your breath).
PS: I inherited this database name. It's too late (read: expensive)
now to fix it.
-Tom.Many of the tools do not support non-standard object names.
Even though SQL Server lets you create objects with invalid
names they aren't really legal names. You can find the rules
for object names in books online under the topic Using
Identifiers
-Sue
On Tue, 23 Mar 2004 19:39:31 -0700, Tom van Stiphout
<tom7744@.no.spam.cox.net> wrote:

>It has already been reported that the View tool in Enterprise Manager
>cannot handle databases which have a period in their name (e.g.
>"mydomain.com"). It doesn't show the individual field names.
>Today I found a second problem: when scheduling a Job under SQL Server
>Agent, in the New Step dialog you are prompted for a database name.
>Databases with a period in their name are not listed. Tomorrow I'll
>use ISQL to schedule the job in Windows Scheduler.
>Hopefully MSFT will add these tests to the test scripts for the next
>version and service pack (but don't hold your breath).
>PS: I inherited this database name. It's too late (read: expensive)
>now to fix it.
>-Tom.|||On Tue, 23 Mar 2004 19:39:31 -0700, Tom van Stiphout wrote:

>It has already been reported that the View tool in Enterprise Manager
>cannot handle databases which have a period in their name (e.g.
>"mydomain.com"). It doesn't show the individual field names.
>Today I found a second problem: when scheduling a Job under SQL Server
>Agent, in the New Step dialog you are prompted for a database name.
>Databases with a period in their name are not listed. Tomorrow I'll
>use ISQL to schedule the job in Windows Scheduler.
>Hopefully MSFT will add these tests to the test scripts for the next
>version and service pack (but don't hold your breath).
Hopefully not. I would prefer that MSFT removes the ability to call a
database "mydomain.com" or other pathological formats.

>PS: I inherited this database name. It's too late (read: expensive)
>now to fix it.
That's too bad.
I presume that you can still write scripts for all of the things that
you can't accomplish via the GUI? If so, then you have the tradeoff
costs of writing everything in scripts versus changing the name of the
database in the application (and all the associated testing).
--
"There is more to life than simply increasing its speed." - Mahatma Gandhi|||On Tue, 23 Mar 2004 21:32:35 -0700, Sue Hoegemeier
<Sue_H@.nomail.please> wrote:
You are correct. To my horror I found that:
sp_renamedb 'pubs', '123'
works, while BOL clearly has strict rules about the first character of
an identifier.
Would it kill MSFT to implement those rules, and not allow identifiers
that it later can't work with?
-Tom.
>Many of the tools do not support non-standard object names.
>Even though SQL Server lets you create objects with invalid
>names they aren't really legal names. You can find the rules
>for object names in books online under the topic Using
>Identifiers
>-Sue
>On Tue, 23 Mar 2004 19:39:31 -0700, Tom van Stiphout
><tom7744@.no.spam.cox.net> wrote:
>|||You could always send your request for this to
sqlwish@.microsoft.com
-Sue
On Wed, 24 Mar 2004 06:56:01 -0700, Tom van Stiphout
<tom7744@.no.spam.cox.net> wrote:
>On Tue, 23 Mar 2004 21:32:35 -0700, Sue Hoegemeier
><Sue_H@.nomail.please> wrote:
>You are correct. To my horror I found that:
>sp_renamedb 'pubs', '123'
>works, while BOL clearly has strict rules about the first character of
>an identifier.
>Would it kill MSFT to implement those rules, and not allow identifiers
>that it later can't work with?
>-Tom.
>

Announcing the Analysis Services Stored Procedure Project

A few months ago, a few community-spririted Analysis Services guys (including me) got together to create some example Analysis Services stored procedures. I'm happy to announce that beta 1 of our project is now available to download here:
http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures
The idea was to create a set of useful extensions to MDX to help solve common problems and at the same time provide some example source code to help people writing their own stored procedures. Please take a look and tell us what you think!

Very Nice!

How about a function that takes care of divide-by-zero and returns NULL if the denominator is 0? Ie. ReturnDivide(division). That would clean up a lot of iif mdx.

|||

We discussed this exact problem, but Mosha explained that using a sproc in this way would do more harm than good. Basically, the problem is that there's no way of marking a sproc as being deterministic (ie will always return the same result for the same cell) and so that means that if you use a sproc in a calculated member then the value returned by that calculation will never be cached. As a result, it's probably better to use IIF instead so that subsequent requests for the result returned by a calculation for any given cell in the cube will be returned from the cache.

To answer your other question about IIF, if <mdxstatement> is a calculated measure whose result can be cached, then no, it will only be executed once and the second time it's evaluated the value will be returned from the cache. So it's good idea to create a calculated measure to hold the value of <mdxstatement> even if you don't intend to display the result to the user and set its Visible property to False.

HTH,

Chris

|||

Just so I understand:
You are saying that the following MDX script will make the server calculate [measures].[summation] once in the scope iif statement:

Create Member [Measures].[Summation] AS

Aggregate({[DimMember1], [DimMember2]});

Scope ([DimMemberX]);

this = iif([Measures].[Summation] = 0, NULL, [SomeSet] / [Mesures].[Summation];

End Scope;

Whereas this statement will make it calculate it twice:

Scope ([DimMemberX]);

this = iif(aggregate({[Dimmember1], [Dimmember2]}) = 0, NULL, [Someset] / aggregate([DimMember1], [DimMember2]));

End Scope;

|||

Yes, that's what I understand.

Chris

Analysis Services Roles - Dimension Data

Is there a way to assign dimension data member restrictions within AS 2005 Roles to all attribute hierarchies within a dimension

For example we have a dimension with 4 attribute hierarchies :

Office Code

Office Name

Office Group

Office Overseas

Office Code and Office Name roll up to Office Group (within Office Overseas)

We want to restrict certain Active Directory groups to only see their own office data eg Office Code TOR for Office Name Toronto (within Office Group Canada)

When an AD group is assigned to only see Office Code TOR (by using the Role Dimension Data member restrictions)

Then users in this AD group can still see measure data aggregated for all Office Names and Office Groups (though can only see Office Code TOR)

Do restrictions have to applied separately to all attribute hierarchies within a dimension? This will be difficult for us to maintain, since most of our dimensions are type 1 with members moving (eg the office groups change quite frequently)

Has anyone else come across this issue ?

>Then users in this AD group can still see measure data

>aggregated for all Office Names and Office Groups

>(though can only see Office Code TOR)

But the realy see Values for Toronto, not for all the offices, don't they?

|||

I'm not sure I'm 100% clear on the problem you are having. Are you saying the dimension members are appropriately restricted but the totals reflect non-visible members? For example, if you drill down to Office Code you see just TOR. The total for All Office Code is different than the TOR value?

If this is what you are trying to address, take a look at the Visual Totals setting in your dimension data security. By setting this value to True, SSAS calculate the totals based on just those members you have access to.

Thanks,
Bryan Smith

|||

thanks Bryan

setting the visual totals on the dimension data assignment has resolved this

Mark