Showing posts with label member. Show all posts
Showing posts with label member. Show all posts

Thursday, March 29, 2012

Description for Calculated Members

You can add descriptions to measures that are built under the Cube Structure tab. But I cannot see how to add a description to a Calculated Member. Is it possible to add a description to a Calculated Member?
Looks like this has already been answered.

Anyone interested, here is the link.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=325816&SiteID=1
|||You might be interested in looking at BIDSHelper www.codeplex.com/bidshelper it's free addin for BI Development Studio that adds a number of useful features including the ability to add descriptions to calculated members (through the member properties dialog) http://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Calculation%20Helpers&referringTitle=Home

Sunday, March 25, 2012

Derive columns from cube member?

I have a cube that I read in a data flow section using OLE DB. To get this to work I had to do an ad hoc query through a sql server database ( this is problem that is described in post 219068).

So I now have the data and I want to derive new columns based on that data using an if statement:

if ( ISNULL(" [Facility].[REGION].[NATCODE].[MEMBER_CAPTION] " ) comptype = "NAT" else comptype = "REG"

comptype is the new derived column I am creating.

But when I try to save this I get an error: The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a requred element such as parenthesis

I selected the cube element from the columns list in derived element, when it initially put it in it looked like:

[[Facility].[REGION].[NATCODE].[MEMBER_CAPTION] ]

and gave error:

The token "[" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.

Below is the statement I use to get data back from AS

select * from openrowset('MSOLAP', 'DATASOURCE=local; Initial Catalog=Patient Demographics 2005;',
'with member [Measures].[TimeDisplayName] as [Calendar].CurrentMember.Name
SELECT NON EMPTY { [Measures].[TimeDisplayName],[Measures].[Case Count],
[Measures].[A100 Bathing],[Measures].[A100 Bed Chair Wheelchair],
[Measures].[A100 Bladder],[Measures].[A100 Bowel],
[Measures].[A100 Dressing Lower],[Measures].[A100 Dressing Upper],
[Measures].[A100 Eating],[Measures].[A100 Grooming],
[Measures].[A100 Stairs],[Measures].[A100 Toilet],
[Measures].[A100 Toileting],[Measures].[A100 Tub Shower],
[Measures].[A100 Walk Wheelchair],[Measures].[A200 Comprehension],
[Measures].[A200 Expression],[Measures].[A200 Interaction],
[Measures].[A200 Memory],[Measures].[A200 Problem Solving],
[Measures].[D100 Bathing],[Measures].[D100 Bed Chair Wheelchair],
[Measures].[D100 Bladder],[Measures].[D100 Bowel],
[Measures].[D100 Dressing Lower],[Measures].[D100 Dressing Upper],
[Measures].[D100 Eating],[Measures].[D100 Grooming],
[Measures].[D100 Stairs],[Measures].[D100 Toilet],
[Measures].[D100 Toileting],[Measures].[D100 Tub Shower],
[Measures].[D100 Walk Wheelchair],[Measures].[D200 Comprehension],
[Measures].[D200 Expression],[Measures].[D200 Interaction],
[Measures].[D200 Memory],[Measures].[D200 Problem Solving] } ON COLUMNS,
({[Facility].[REGION].[NATCODE], [Facility].[REGION].[REGCODE]} *
[RIC].[CMGGRPCD].ALLMEMBERS ) ON ROWS FROM [CMG Demographics]
WHERE [Calendar].[Quarter 2 (2007)]')

So can some one help me on how I can do this derived colum.

Try this...

isnull(<fieldname>) == true ? "NAT" : "REG"

To get <fieldname> expand the 'columns' folder in the derived column transformation editor.

|||Use a derived column to create new columns.

New Column Name: comptype
Expression: ISNULL([InputColumn]) ? "NAT" : "REG"|||

Anthony Martin wrote:

Try this...

isnull(<fieldname>) == true ? "NAT" : "REG"

Anthony, just simplify that statement. ISNULL() returns a boolean value, so there's no need to doubly check for a true status or not. Just drop the "== true" part.|||

Thanks, good point. Just a habit I guess.

|||

I have figured out what I had to do, but I do not know why I had to do this way, but first while I saw after I posted that used used bad assignment of derived column comptype = <value>. that was one of my many different attempts around the question I posted about.

1st) I could never get the if else statment to work in derived field, I had to use conditional if (?Smile logic for my ifs

2nd) I could not referenct the the MDX columns in my conditional if. It did not like all of the '[' and ']', so I had to convert all of the columns from MDX column name to a more standard sql column name, basically renamed column to not have '[', ']' in them

Does any one know why this does not work neatly, MDX is supposed to have been integrated with SSIS, but I had to do multiple tricks to get the data, right from start where I had to use ad hoc query though a sql database to execute the MDX.

This also results in fact that during selection of MDX it did not maintain any of the data types and converted everything to chararacter field of size 4000, which gave me trunctaion warnings in the selection of MDX into SSIS

Derive columns from cube member?

I have a cube that I read in a data flow section using OLE DB. To get this to work I had to do an ad hoc query through a sql server database ( this is problem that is described in post 219068).

So I now have the data and I want to derive new columns based on that data using an if statement:

if ( ISNULL(" [Facility].[REGION].[NATCODE].[MEMBER_CAPTION] " ) comptype = "NAT" else comptype = "REG"

comptype is the new derived column I am creating.

But when I try to save this I get an error: The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a requred element such as parenthesis

I selected the cube element from the columns list in derived element, when it initially put it in it looked like:

[[Facility].[REGION].[NATCODE].[MEMBER_CAPTION] ]

and gave error:

The token "[" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.

Below is the statement I use to get data back from AS

select * from openrowset('MSOLAP', 'DATASOURCE=local; Initial Catalog=Patient Demographics 2005;',
'with member [Measures].[TimeDisplayName] as [Calendar].CurrentMember.Name
SELECT NON EMPTY { [Measures].[TimeDisplayName],[Measures].[Case Count],
[Measures].[A100 Bathing],[Measures].[A100 Bed Chair Wheelchair],
[Measures].[A100 Bladder],[Measures].[A100 Bowel],
[Measures].[A100 Dressing Lower],[Measures].[A100 Dressing Upper],
[Measures].[A100 Eating],[Measures].[A100 Grooming],
[Measures].[A100 Stairs],[Measures].[A100 Toilet],
[Measures].[A100 Toileting],[Measures].[A100 Tub Shower],
[Measures].[A100 Walk Wheelchair],[Measures].[A200 Comprehension],
[Measures].[A200 Expression],[Measures].[A200 Interaction],
[Measures].[A200 Memory],[Measures].[A200 Problem Solving],
[Measures].[D100 Bathing],[Measures].[D100 Bed Chair Wheelchair],
[Measures].[D100 Bladder],[Measures].[D100 Bowel],
[Measures].[D100 Dressing Lower],[Measures].[D100 Dressing Upper],
[Measures].[D100 Eating],[Measures].[D100 Grooming],
[Measures].[D100 Stairs],[Measures].[D100 Toilet],
[Measures].[D100 Toileting],[Measures].[D100 Tub Shower],
[Measures].[D100 Walk Wheelchair],[Measures].[D200 Comprehension],
[Measures].[D200 Expression],[Measures].[D200 Interaction],
[Measures].[D200 Memory],[Measures].[D200 Problem Solving] } ON COLUMNS,
({[Facility].[REGION].[NATCODE], [Facility].[REGION].[REGCODE]} *
[RIC].[CMGGRPCD].ALLMEMBERS ) ON ROWS FROM [CMG Demographics]
WHERE [Calendar].[Quarter 2 (2007)]')

So can some one help me on how I can do this derived colum.

Try this...

isnull(<fieldname>) == true ? "NAT" : "REG"

To get <fieldname> expand the 'columns' folder in the derived column transformation editor.

|||Use a derived column to create new columns.

New Column Name: comptype
Expression: ISNULL([InputColumn]) ? "NAT" : "REG"|||

Anthony Martin wrote:

Try this...

isnull(<fieldname>) == true ? "NAT" : "REG"

Anthony, just simplify that statement. ISNULL() returns a boolean value, so there's no need to doubly check for a true status or not. Just drop the "== true" part.|||

Thanks, good point. Just a habit I guess.

|||

I have figured out what I had to do, but I do not know why I had to do this way, but first while I saw after I posted that used used bad assignment of derived column comptype = <value>. that was one of my many different attempts around the question I posted about.

1st) I could never get the if else statment to work in derived field, I had to use conditional if (?Smile logic for my ifs

2nd) I could not referenct the the MDX columns in my conditional if. It did not like all of the '[' and ']', so I had to convert all of the columns from MDX column name to a more standard sql column name, basically renamed column to not have '[', ']' in them

Does any one know why this does not work neatly, MDX is supposed to have been integrated with SSIS, but I had to do multiple tricks to get the data, right from start where I had to use ad hoc query though a sql database to execute the MDX.

This also results in fact that during selection of MDX it did not maintain any of the data types and converted everything to chararacter field of size 4000, which gave me trunctaion warnings in the selection of MDX into SSIS

Wednesday, March 21, 2012

Deployment Failed

Hi Team,

Happy New Year to you all,

When i try to deploy my project (SSAS Step by Step) with a calculated Member, the deployment fails.

I actually get the following error.

Error 1 The 'Database' with 'ID' = 'SSAS Step by Step ' doesn't exist in the collection. 0 0

When I try to change the transactional deployment property to true so as for me to overwrite the existing database i get the following error;

Error 1 Members belong to different hierarchies in the function. 0 0

Could you please advise me on how to go about this.

Any help will highly appreciated.

Regards,

Ronald

Please provide the expression of the calculated member. From the error message it seems like you are trying to build a set from the members in different hierarchies. Might be you only wanted to build a tuple instead of set - but it is hard to tell without actual expression.

Tuesday, February 14, 2012

Deny Create Database

User is an NT Administrator and has been made a member of Server Role sys
admin.
I'm not in the master database in query analyzer.
When I do this:
Deny Create Database
To User
I get the following message:
CREATE DATABASE PERMISSION CAN ONLY BE GRANTED IN THE MASTER DATABASE
How do I alter the SQL statement to refer to the master database?
Thanks for any help.Run:
use master
before you run the DENY.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Scotty" <scasti1@.cox.net> wrote in message
news:4B307CF8-97A3-41ED-9F3E-ABB130C8C525@.microsoft.com...
User is an NT Administrator and has been made a member of Server Role
sysadmin.
I'm not in the master database in query analyzer.
When I do this:
Deny Create Database
To User
I get the following message:
CREATE DATABASE PERMISSION CAN ONLY BE GRANTED IN THE MASTER DATABASE
How do I alter the SQL statement to refer to the master database?
Thanks for any help.|||Thanks, but trying to mimic a process all in one place, and the analyzer may
not be the best place to do this. I could do it from a test web page but f
amiliarizing myself with Analyzer environment.
In a web environment, I know I would have to connect to the master database
to issue this command.
However, is there an 'automated' way to switch to that database in the Query
Analyzer so that I can put the code
in the same test file rather than having to run another instance of the Anal
yzer just for that? I would rather comment out a few lines that I am not te
sting at that moment rather than switch back and forth from instance to inst
ance.
Is there a command that I can put in the body of the analyzer sql script to
switch? Sorry. Kind of new at this. All of the lines in this test file do
n't have to be run on Master anyway since they are adding users to specific
databases or they are addin
g server roles which can be added from anywhere, so if I used a separate tes
t file, it would have only these two lines in it anyway.
So is there a command to switch databases that I can place in the Analyzer s
cript? In the meantime, I'll go with your answer, and thanks very much for
your prior reply.|||If you are using Query Analyzer to run a script, then the only way to
guarantee that you are in a particular database is to issue a USE command.
So, if you are running a script from within QA, and you want to be sure you
are in master, then ensure you have the following:
use master
go
-- do something
I'm not sure I understand what the issue is. You don't need separate
instances of QA or anything like that.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Scotty" <scasti1@.cox.net> wrote in message
news:459B2889-43EB-4940-9D24-F74F7462ADCC@.microsoft.com...
Thanks, but trying to mimic a process all in one place, and the analyzer may
not be the best place to do this. I could do it from a test web page but
familiarizing myself with Analyzer environment.
In a web environment, I know I would have to connect to the master database
to issue this command.
However, is there an 'automated' way to switch to that database in the Query
Analyzer so that I can put the code
in the same test file rather than having to run another instance of the
Analyzer just for that? I would rather comment out a few lines that I am
not testing at that moment rather than switch back and forth from instance
to instance.
Is there a command that I can put in the body of the analyzer sql script to
switch? Sorry. Kind of new at this. All of the lines in this test file
don't have to be run on Master anyway since they are adding users to
specific databases or they are adding server roles which can be added from
anywhere, so if I used a separate test file, it would have only these two
lines in it anyway.
So is there a command to switch databases that I can place in the Analyzer
script? In the meantime, I'll go with your answer, and thanks very much for
your prior reply.|||I have been using separate instances of QA so I can use sa in one instanc
e to issue permissions and then see the effects of the issued permissions
by having a lower level user run sql commands in the second instance (i.e. s
econd instance logged in as
Low Level Windows user).
Thanks for the use Master go.
I'm sure it will work, and I appreciate it.
Scott|||Works great, but one thing I noticed:
Even though the commands take fine and the syspermission table record cou
nt changes as you would expect (master file), the command will not turn off
create database for a user in the sysadmin role or for a user who is not in
the sysadmin role but who i
s in the db_creator role.
So it doesn't seem to work at all. I know for a db_owner (non-sysadmin) you
have to turn on database creation after the fact, not turn it off, as I am
testing, so it does not apply in that instance.
So I'm trying to find a combination where Deny Create Database actually work
s where it would make sense. It makes sense that you might want to turn it
off for an administrator. Can't get it to work.|||Alternatively, you can send the default database to master.

>--Original Message--
>If you are using Query Analyzer to run a script, then the
only way to
>guarantee that you are in a particular database is to
issue a USE command.
>So, if you are running a script from within QA, and you
want to be sure you
>are in master, then ensure you have the following:
>use master
>go
>-- do something
>I'm not sure I understand what the issue is. You don't
need separate
>instances of QA or anything like that.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Scotty" <scasti1@.cox.net> wrote in message
>news:459B2889-43EB-4940-9D24-F74F7462ADCC@.microsoft.com...
>Thanks, but trying to mimic a process all in one place,
and the analyzer may
>not be the best place to do this. I could do it from a
test web page but
>familiarizing myself with Analyzer environment.
>In a web environment, I know I would have to connect to
the master database
>to issue this command.
>However, is there an 'automated' way to switch to that
database in the Query
>Analyzer so that I can put the code
>in the same test file rather than having to run another
instance of the
>Analyzer just for that? I would rather comment out a few
lines that I am
>not testing at that moment rather than switch back and
forth from instance
>to instance.
>Is there a command that I can put in the body of the
analyzer sql script to
>switch? Sorry. Kind of new at this. All of the lines
in this test file
>don't have to be run on Master anyway since they are
adding users to
>specific databases or they are adding server roles which
can be added from
>anywhere, so if I used a separate test file, it would
have only these two
>lines in it anyway.
>So is there a command to switch databases that I can
place in the Analyzer
>script? In the meantime, I'll go with your answer, and
thanks very much for
>your prior reply.
>|||Looks like you need to re-think your strategy. If you're in db_creator, you
can create a database. If you want to ensure they can't do it - keep them
out. A sysadmin role member can do anything, so a DENY isn't going to work
here. It's best to look at what they really need and give them only those
permissions or roles. That way, you won't need to use DENY.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Scotty" <scasti1@.cox.net> wrote in message
news:3E662297-270F-4F7B-AE8A-D0E9215E9E83@.microsoft.com...
Works great, but one thing I noticed:
Even though the commands take fine and the syspermission table record
count changes as you would expect (master file), the command will not turn
off create database for a user in the sysadmin role or for a user who is not
in the sysadmin role but who is in the db_creator role.
So it doesn't seem to work at all. I know for a db_owner (non-sysadmin) you
have to turn on database creation after the fact, not turn it off, as I am
testing, so it does not apply in that instance.
So I'm trying to find a combination where Deny Create Database actually
works where it would make sense. It makes sense that you might want to turn
it off for an administrator. Can't get it to work.