Member security question

Hello!
In our AS2005 project we use two roles for two different group of users. First group included key account manager - each manager can see only his account sales. Second group - sales region managers - each sales region manager can see only sales in his region. We use custom clr callback method and everything works fine. Solution is based on dimension members and visual totals are enabled for both groups.

But now new requirement arrived: one manager is both key account AND sales region at the same time and therefore he should be able to see UNION of key account sales and region sales. Can this be accomplished somehow? I'm able to get the intersection, but never union. One solution is to turn visual totals off, but then other dimensions show all cube data, which is not acceptable.

Any help is appreciated.

Radim

I think you will need a third role for this person.

Basically I am guessing that your account security produces a set that looks something like:

Descendants([Account].[Account].[Account 1])

And that your Region security produces a set that looks something like:

Descendants([Region].[Region].[Region1])

This will result for an effective permission set of:

Descendants([Account].[Account].[Account 1]) * Descendants([Region].[Region].[Region1])

Which is effectively an intersection of the selected account with the selected region. Whereas, for the situation you describe, you would want an effective final set of:

{ {[Region].[Region].[All Region]} * Descendants([Account].[Account].[Account 1]) }

* { Descendants([Region].[Region].[Region1]) * { [Account].[Account].[All Account] } }

Which, instead of giving you a logical AND between the two sets (where members must be descendants of the Regions AND Account) will give you a logical OR (which will return members which are descendants of either the Account OR Region)

Hope this helps

|||Hi Darren!
That's good description of desired solution. But how could I implement such set? Right now callback function simply returns allowed members to role, but this solution doesn't seem to be that straitforward. New role should restrict cell data, instead of dimension members?

Radim|||

Hi Radim,

Since these users (given their dual role) should be able to see all members of both Account and Region, I think that the solution would have to restrict at the cell, rather than dimension, level. However, I'm not aware of a "Visual Totals" option for Cell Security, which is one of your requirements. Visual Totals behavior could conceivably be simulated via the MDX script, if that's a viable option for you. But this would entail introducing security-related logic into the cube script.

sql

medical records"SQL HELP!"

hi. im martin.. you guys are really amzing!!i really love this group
discussion..
i am new in sql and i am working in a certain project..specifically,about
medical records..
my tables are:
"Patients"
patient_ID int
Lname nvarchar
Fname nvarchar
Mname nvarchar
DateofBirth datetime
Weight int
Temp int
Address nvarchar
"Diseases"
Disease_ID int
Patient_ID int
Description nvarchar
Medication nvarchar
Classification nvarchar
"Check_Ups"
CheckUP_ID int
Patient_ID int
DateofCheckUp datetime
Diagnosis nvarchar
Results nvarchar
my problem is,i need to make a report, this is how it looks like:
sample only:
DiseaseDescription age range/sex
Total Total
1-4 5-15 15-40 40-65
65-up
M l F M l F M l F M l F
M l F M l F
fever 2 l 2 2 l 2 2 l 2 2 l 2
2 l 2 10 l 10 = 20
Flu 1 l 3 1 l 4 1 l 3 1 l 5
1 l 4 5 l 19 = 24
can u plz help me how to do this and suggest on how can i improve my
tables?plZZZZ..THANKS A LOT in advance..
seeyah guyz..!Martin
Please post DDL+ sample data+ expected result . What is your Primary keys
defined on tables?
"I'm Martin plz help me!!!" <I'm Martin plz help
me!!!@.discussions.microsoft.com> wrote in message
news:48E16803-6D86-422B-A206-F407C0E7D48A@.microsoft.com...
> hi. im martin.. you guys are really amzing!!i really love this group
> discussion..
> i am new in sql and i am working in a certain project..specifically,about
> medical records..
> my tables are:
> "Patients"
> patient_ID int
> Lname nvarchar
> Fname nvarchar
> Mname nvarchar
> DateofBirth datetime
> Weight int
> Temp int
> Address nvarchar
> "Diseases"
> Disease_ID int
> Patient_ID int
> Description nvarchar
> Medication nvarchar
> Classification nvarchar
>
> "Check_Ups"
> CheckUP_ID int
> Patient_ID int
> DateofCheckUp datetime
> Diagnosis nvarchar
> Results nvarchar
> my problem is,i need to make a report, this is how it looks like:
> sample only:
> DiseaseDescription age range/sex
> Total Total
> 1-4 5-15 15-40 40-65
> 65-up
> M l F M l F M l F M l F
> M l F M l F
> fever 2 l 2 2 l 2 2 l 2 2 l 2
> 2 l 2 10 l 10 = 20
> Flu 1 l 3 1 l 4 1 l 3 1 l 5
> 1 l 4 5 l 19 = 24
>
> can u plz help me how to do this and suggest on how can i improve my
> tables?plZZZZ..THANKS A LOT in advance..
> seeyah guyz..!
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>|||primary keys are patient_id, disease_ID, CheckUp_ID
my tables are:
"Patients"
patient_ID int
Lname nvarchar
Fname nvarchar
Mname nvarchar
DateofBirth datetime
Weight int
Temp int
Address nvarchar
"Diseases"
Disease_ID int
Patient_ID int
Description nvarchar
Medication nvarchar
Classification nvarchar
"Check_Ups"
CheckUP_ID int
Patient_ID int
DateofCheckUp datetime
Diagnosis nvarchar
Results nvarchar
expected output: DAy or WEEk
Disease age range/sex Total Total
1-4 5-15 15-40 40-65 65-up
M l F M l F M l F M l F M l F M l F
fever 2 l 2 2 l 2 2 l 2 2 l 2 2 l 2 10 l 10 = 20
Flu 1 l 3 1 l 4 1 l 3 1 l 5 1 l 4 5 l 19 = 24
M=male F=female
sex from "patient" table
disease from "disease" table
age from "patient" table
count by age sex and age range. then get the total male and female
and the overall total..|||By posting DDL I meant
CREATE TABLE balala
(
col INT NOT NULL,
...
)
INSERT INTO bababa VALUES (babababab)
My expected result is
......
"I''m Martin plz help me!!!" <ImMartinplzhelpme@.discussions.microsoft.com>
wrote in message news:15DD238C-CA08-4F9E-8844-A59219F5DA60@.microsoft.com...
>
> primary keys are patient_id, disease_ID, CheckUp_ID
> my tables are:
> "Patients"
> patient_ID int
> Lname nvarchar
> Fname nvarchar
> Mname nvarchar
> DateofBirth datetime
> Weight int
> Temp int
> Address nvarchar
> "Diseases"
> Disease_ID int
> Patient_ID int
> Description nvarchar
> Medication nvarchar
> Classification nvarchar
>
> "Check_Ups"
> CheckUP_ID int
> Patient_ID int
> DateofCheckUp datetime
> Diagnosis nvarchar
> Results nvarchar
>
> expected output: DAy or WEEk
> Disease age range/sex Total Total
> 1-4 5-15 15-40 40-65 65-up
> M l F M l F M l F M l F M l F M l F
> fever 2 l 2 2 l 2 2 l 2 2 l 2 2 l 2 10 l 10 = 20
> Flu 1 l 3 1 l 4 1 l 3 1 l 5 1 l 4 5 l 19 = 24
> M=male F=female
> sex from "patient" table
> disease from "disease" table
> age from "patient" table
> count by age sex and age range. then get the total male and female
> and the overall total..
>|||I'm so sorry Uri..i'm still designing my project on papers.still don't know
how to implement it on the sql..just wanna have ideas from you guyzz..|||Well, maybe you eant to look at this article
http://www.databaseanswers.com/data_models/index.htm -- examples
database design
"I''m Martin plz help me!!!" <ImMartinplzhelpme@.discussions.microsoft.com>
wrote in message news:5E103CC2-37F6-41B5-8777-7EC4326AEFC3@.microsoft.com...
> I'm so sorry Uri..i'm still designing my project on papers.still don't
> know
> how to implement it on the sql..just wanna have ideas from you guyzz..|||plz..help me..|||thanksss...|||On Tue, 2 Aug 2005 00:25:02 -0700, "I'm Martin plz help me!!!" <I'm
Martin plz help me!!!@.discussions.microsoft.com> wrote:
>my problem is,i need to make a report, this is how it looks like:
Hi Martin,
No -- your problem is that this design has several errors.
>"Patients"
>patient_ID int
>Lname nvarchar
>Fname nvarchar
>Mname nvarchar
>DateofBirth datetime
>Weight int
>Temp int
>Address nvarchar
This means that a patient can only have one weight and one temp. Most
doctors prefer to see a history. Also, they will want to store weight
and temp with at least one decimal position. Remove Weight and Temp from
this table and put them in a new one:
"Examinations"
patient_ID int (PK, FK)
ExaminationDate datetime (PK)
Weight decimal(4,1)
Temp decimal(3,1)
>"Diseases"
>Disease_ID int
>Patient_ID int
>Description nvarchar
>Medication nvarchar
>Classification nvarchar
Remove the patient_ID from this table. The flu will always be the flu,
regardless of who has suffered from it.
Instead, create a new table that links patients to diseases. You might
want a date on that table too (maybe two dates: date diagnosed and date
cured)
Not sure about mediaction, as I'm not a doctor. Will the same medication
always be used for a disease, then it's in the right table. But it the
medication can be different for each patient, move it to the new table
mentioned above as well. Or maybe to yet another new table, if multiple
medications can be given for one disease, or if one medication can be
given to treat multiple diseases at once
>"Check_Ups"
>CheckUP_ID int
>Patient_ID int
>DateofCheckUp datetime
>Diagnosis nvarchar
>Results nvarchar
Ah, so you do have a table for checkups. This is where the weight and
temp columns should go. You don't need an examinations table after all.
>can u plz help me how to do this and suggest on how can i improve my
>tables?
I think that the best place to start is here:
http://www.amazon.com/exec/obidos/external-search?keyword=data%20modeling&mode=blended
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Median query with Group by clause

Hi,

I have a view in which I have 3 cols...(pno,ptno,diff)..diff is the
difference in time in minutes.I want to calculate Median(diff) group
by pno,ptno...using a sql query for SQL server...

Any help is greatly appreciated..

Thanks

AJ"AJ" <aj70000@.hotmail.com> wrote in message
news:6097f505.0404211014.79b1e842@.posting.google.c om...
> Hi,
> I have a view in which I have 3 cols...(pno,ptno,diff)..diff is the
> difference in time in minutes.I want to calculate Median(diff) group
> by pno,ptno...using a sql query for SQL server...
> Any help is greatly appreciated..
> Thanks
> AJ

http://groups.google.com/groups?hl=...80%40ietsmet.nl

Simon

Median Function

I am trying to write a function to give me a median value. I have a matrix.
There is a column group/text box called loanbalance. Displayed in that
field is the SUM of all loan balances for the month.
I have another textbox called "Median". This textbox I want to display the
median value for the values in the "Loanbalance" field/textbox.
I don't have detail rows showing in the report, I am grouping.
I went out to the menu and selected: report, report properties, code, and
tried to write a function MEDIAN(reportitems!loanbalance.value), end
function. I know pretty basic, but I am not an advanced user.
Then in the "median" textbox, expression I have = code.MEDIAN(ReportItems!loanbalance.value).
It does not work. I don't get errors, I just don't get anything back for a
value.
Could someone help me out with this? Am I going about this the right way?
Thanks,Susan,
I have a report where I needed to get the Median time for documents
processed and ended up writing a routine from within a stored procedure to
perform this operation. As of yet I have not found a way to do it from w/in
RS - if anybody out there knows of way to accomplish this your help would be
appreciated.
Basically what I did was to get the row number corresponding to the number
of documents processed and divide that number by 2. I then took the
resulting number as parameter for my where clause-
Ex:
Set @.RowNumber = @.RowNumber / 2
Select @.MedianTime = DocTime From @.ReportDataTable
Where RowNumber = @.RowNumber
I know it's rudimentary but it does work.
Hope this helps.
Bill Youngman
Anexinet, Inc.
"Susan" <Susan@.discussions.microsoft.com> wrote in message
news:F2635C55-394A-44FD-A8C0-934C42260417@.microsoft.com...
> I am trying to write a function to give me a median value. I have a
matrix.
> There is a column group/text box called loanbalance. Displayed in that
> field is the SUM of all loan balances for the month.
> I have another textbox called "Median". This textbox I want to display
the
> median value for the values in the "Loanbalance" field/textbox.
> I don't have detail rows showing in the report, I am grouping.
> I went out to the menu and selected: report, report properties, code, and
> tried to write a function MEDIAN(reportitems!loanbalance.value), end
> function. I know pretty basic, but I am not an advanced user.
> Then in the "median" textbox, expression I have => code.MEDIAN(ReportItems!loanbalance.value).
> It does not work. I don't get errors, I just don't get anything back for
a
> value.
> Could someone help me out with this? Am I going about this the right way?
> Thanks,
>

Measure group related question

I have a cube with two fact tables, two measure groups. I also created few calculated measures.

On client side I see two measure groups and below the calculated measure.

Is there any way I can show that calculated measure in one of the measure group. It's easy for end user to see number measure and % measure both next to each other.

I hope this can be possible using MDX some how update mdx for measure group or some thing...

Thank you - Ashok

Ashok,

You can easily associate your calculated measure with any existing measure group. To do this, please follow these steps:

- Open your project in SQL Server Business Intelligence Development Studio & double click your cube

- In the Cube editor, go to the Calculations tab and double click your calculated member "Average Sale Price"

- In the toolbar, just right from the "Form View" and "Script View" toolbar buttons, click on the "Calculation Properties" toolbar button

- In the Calculation Properties dialog that comes up, select your calculated member "Average Sale Price" from the dropdown and set the associated measure group for it to be "Royalty Statement"

- Deploy the project and you are ready to go

Hope this helps,

Artur

|||

You are the man Thanks. Is there any way to move a measure from one group to another. I know If I change underline Table/View it can be done but is there any way in cube design time I can move measure from one group to another.

-Ashok

|||

Ashok, moving a measure from one measure group to another is not currently supported in AS 2005.

--Artur

Measure Group ID

Hi,

What is the thinking behind not allowing us to change the default value of the ID property of a measure group? I really don't like the default - I'd like to choose my own. (If thjere is a way to change it in BIDS then plesae let me know!)

This wouldn't be a problem except that the XML/A process command references the ID and I would rather have something in there that is intuitive - somehing that represents what the measure group is for.

Can I enter this as a feature request (i.e. allow us to change the ID property) at Microsoft Connect?

Thanks

-Jamie

OK, this was initially a minor irritation but its now become a pretty big problem.

I have split my measure group into partitions - one partition per year. I want to dynamically build a XMLA command at runtime that processes the current year. This would be easy if the partitions ID were all consistent e.g. :

"MyCubePartition 2004"|||

> What is the thinking behind not allowing us to change the default value of the ID property of a measure group?

In AS2000, objects only had names and because of dependencies between objects, changing a name was not possible after an object was created. In AS2005, to allow renamings, the ID was introduced. An object has a Name (used for browsing, localizable; also used in MDX) and an ID (used to define dependencies between objects and also used by the management API, commands like Alter, Create, Delete). The ID is immutable for the same reason as in AS2000: to not break the dependencies between objects.

> I really don't like the default - I'd like to choose my own. (If thjere is a way to change it in BIDS then plesae let me know!)

The wizards (for creating a DataSource, DataSourceView, Dimension, Cube, Partition etc) allow to specify the object name (usually at the last page). That name is also used as ID, a desired value can be used there.

But if you need to change later the IDs, there is no place in editors or wizards to do this, instead there is a limited work-around (I say limited because it's harder to do for objects with dependencies, but easier for partitions for example):

- script the Create statement for the object (in SQL Management Studio) and change the ID in the script

- delete the object (this is where it's harder for objects with dependencies)

- run the script

Adrian Dumitrascu

|||

Adrian,

Thanks for the reply.

To be honest, messing about with deployed objects give me the shivers so its not something I want to do. Not least because I want to make sure that EVERY deployment we do has the same IDs - so I want to make sure that the IDs that I want are stored in my BIDS project.

I accept your point about the reason for IDs being immutable but it doesn't solve my problem. What WOULD solve my problem would be if we could reference objects by name in the 'Process' command. Why can't we do that? Is it worth me raising a change request for this?

Regards

Jamie

|||

For changing the IDs in the BI project:

- you can edit the xml project files (right click and use 'View Code', for the .partitions file you need to use 'Show All Items' for the project, or just edit it outside Visual Studio); but this operation is easy for objects with no dependencies (like partitions); for objects with dependencies, you need to also edit the XML for their dependents

OR

- after you change the IDs for the deployed objects (in SQL Management Studio with scripting), you can re-create the BI project (File -> New Project -> Import Analysis Services 9.0 Database; please make sure you specify the same name for the project as the database, otherwise check the Target Database on project properties)

Adrian Dumitrascu

|||

Adrian Dumitrascu wrote:

For changing the IDs in the BI project:

- you can edit the xml project files (right click and use 'View Code', for the .partitions file you need to use 'Show All Items' for the project, or just edit it outside Visual Studio); but this operation is easy for objects with no dependencies (like partitions); for objects with dependencies, you need to also edit the XML for their dependents

Yeah I looked into hacking the XML earlier today and again - it scared me. A find and replace won't work because the ID of the measure group isn't always in an element called <MeasureGroupID>

Adrian Dumitrascu wrote:

- after you change the IDs for the deployed objects (in SQL Management Studio with scripting), you can re-create the BI project (File -> New Project -> Import Analysis Services 9.0 Database; please make sure you specify the same name for the project as the database, otherwise check the Target Database on project properties)

Not a bad idea. Thanks Adrian!

You're still skirting around my other suggestion though :)

All these nasty hacks and workarounds wouldn't be necassary if we could reference the object by name in the 'Process' command. It seems crazy to me that we can't do this.

Do you have any thoughts on that?|||

About referencing objects by name in the management API: we indeed don't have a way to do this now. But going back to the scenario of generating Process statement dynamically, I would say we don't need to change object IDs, nor to reference them by Name in the Process statement:

- if you are creating the partitions programatically (with AMO for example), then you have the control over the IDs, so you can generate the Process statements

- if you are creating the partitions manually with the wizard, then the Name you are specifying is also used as ID

- if at some point you don't know the ID, but only the Name, you can use AMO (Analysis Management Objects)to lookup the Partition by Name (myMeasureGroup.Partitions.GetByName("...")); once you have the Partition, you can generate the Process statement using the Scripter class (this thread mentions the Scripter class http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=476241&SiteID=1)

Adrian Dumitrascu

|||

Hi Adrian,

I know that its possible to get the IDs using AMO but I'm in the business of making life simple for myself and shelling out to get the IDs seperately is not my idea of making things simple.

Let me explain:

I have a collection of partitions named MyPartition2004, MyPartition2005, MyPartition2006...etc...|||

I've posted about this comment here: http://blogs.conchango.com/jamiethomson/archive/2006/06/20/4106.aspx

and there's an interesting reply from a guy about dynamically creating partitions as well.

-jamie

meassure groups with different amount of dimensions

Hi everybody,

I've got a Fact Data table with a value and 16 dimensions.

Now I want to create a second measure group Color with a value and 3 dimensions.

I've filled this table with values and id's for each dimension.

Bu when I make an mdx query with a measure from the FactData and a measure from the second measure group (Color), only the first measure has a value, the second (the measure from Color) is null.

Is these something I've forgotton to set in the cube?

thanks in advance

Filip

Hi,

If you run a simple query (below) do you get two columns of data in the results?

select {[Measures].[Measure Group 1],[Measures].[Measure Group Color]} on 0

from [Cube]

results:

Measure Group 1 Measure Group Color

123231 123213

If not I suspect something else is wrong, perhaps check that you have set up the joins between the dimensions and the facts correctly. If you do get values in both, perhaps it is worth while posting your query.

Hope it helps,
Matt

|||

Hi,

that query runs.

but I have another problem now, this measure group I've created for storing color information, only can't have a Sum or Count as aggregation.

I cannot see the 65280 or ... value for the color I want to use.

I didn't included a period dimension to this measure group.

Is that the cause?

Filip

|||

Hi,

Sorry for delay I was investigating. If the measure is not aggregatable then you will get Null in the measure unless you go down to the granularity in which the data is held at. This is an area not that familar with and finding it difficult to prove.

But if you don't include a dimension in measure and the measures are aggregatable it will not causes nulls to appear, you get funny results. e.g.

select {[Measures].[Measure Group 1],[Measures].[Measure group 2]} on 0,

[Dimension only on measure group 1] on 1

from [cube]

Results:

Measure Group 1 Measure Group 2

dim a 45 1234 1234 being the total in measure group 2

dim b 23 1234

dim c 78 1234

Sorry I could answer it more positively.

Matt

|||

thank you for your help!

it works at a certain level.

at the granularity level, it works fine,

but once it starts aggregating, the value 256 becomes a sum of x times 256.

I have to solve that problem...

thansk for you help

Filip

MDX Sum distinct across specific dimensions

I have a measure group that has a few measures that are not addittive across all dimensions. I need to be able to get the sum distinct across specific dimensions and am having problems getting it to work. I tried specifying that the aggregate function should be semiaddittive, but it complains that it is missing a time dimension. I have tried many variations and combinations of MDX functions including sum, aggregate, crossjoin, filter, distinct, etc. but am struggling with the right combination.

Here is a simplified example of what I am trying to do:

GroupID Title Role ConditionCount

11682 Director Operations Licensing Manager 2
11683 Director Operations Licensing Manager 3
11683 Director Operations Product Manager 3

When I group on role I need to see this:

Licensing Manager 5

Production Manager 3

When I group on Title, I need to see this:

Director Operations 5

Basically I want to do a sum distinct for the GroupID. I only want to add the ConditionCount once for each distinct GroupID since the value will be the same for all instances of an individual groupID. Is there a way to do this in MDX?

Thanks in advance for your suggestions!

Clayton

Could you create another fact table and measure group for ConditionCount? For example, if the original fact table has the fields above, a named query could be created like:

select GroupID, Avg(ConditionCount) as ConditionCount from OriginalFact group by GroupID

Then, a "sum" [Measure].[ConditionCount] measure created on this measure group, which only relates to the Group dimension, would work as above.

|||I don't quite understand your suggestion. I do have a table where GroupID is the primary key, but I can't figure out how to get it to count values once for each distinct iteration of Group ID. The rollups and sum distincts across specific dimensions works with Oracle analytic functions, but I can't figure out how to get it to work with MDX. Thanks for your response.|||

Here's a more detailed description of the suggested model, which uses many-many dimensions:

Suppose this is the "TitleRole" fact table and measure group, with dimensions Group, Title and Role:

GroupID Title Role ConditionCount

11682 Director Operations Licensing Manager 2
11683 Director Operations Licensing Manager 3
11683 Director Operations Product Manager 3

There is a 2nd "Group" fact table and measure group (as suggested above), with [ConditionCount] "sum" measure - this could be the Group dimension table:

GroupID ConditionCount

11682 2
11683 3

This measure group relates directly to the "Group" dimension, but has a many-many relation with the Title and Role dimensions, via the intermediate "TitleRole" measure group. Now, if the Title: "Director Operations" is selected, [Measures].[ConditionCount] in the "Group" measure group should be 5. And if the Role: "Product Manager" is selected, [Measures].[ConditionCount] in the "Group" measure group should be 3.

|||Ah. I understand now. Let me give that I try when I get back to it in a few days and I'll respond. I already have that table set up in the data warehouse, but didn't think about trying a many-to-many relationship. Thanks again!