March 18, 2015 at 11:55 am
Hi to all.
I use SSAS 2008 R2.
I have a SALES Cube.
In this cube i have (scenario simple to understand what i cannot do )
Dimension STORES (about 100)
Dimension CUSTOMERS
Dimension CHIEFSTORE (A person who is the boss of the store).
Dimension Calendar (Dimension Time)
Measures [Sales Amount]
Policy of company says that a CHIEFSTORE can view only its own store and not all stores.
and that a CUSTOMER can buy in every stores.
So for each CHIEFSTORE i applied a specific role to permit to view only data about its store.
PROBLEM:
If i do an analysis about Total Sales , no problem , every CHIEFSTORE view only sales of its own store,BECAUSE IN ITS ROLE I Specified that he can view only its own store data
If i do (that is my problem) an analisys about A specific CUSTOMER , i'd like to view alla sales in all stores...even if this customer buys something in stores different form its own store set in the role.
I'd like to have this
CUSTOMER STORE SALES AMOUNT TOTAL SALES AMOUNT OF ALL STORES
James Rome 300 15670
15670 EURO are the sum sold to this customer in Rome and in all other stores...
So mantaing the role of the users , i tried to create a new calculted measure to have this :
IIF([Measures].[Sales Amount] > 0
, SUM (
( [Customer].currentmember,
[Stores].[Stores].[All Stores]),
[Measures].[Sales Amount] )
, Null)
But i obtain the same value of measures SALES AMOUNT.;-)
Is it possible to sum sales of all stores , without considering the filter in the role of the CHIEFSTORE ???
Regards in advance.
March 18, 2015 at 12:29 pm
Can you share more information on how you've implemented the roles?
March 18, 2015 at 4:38 pm
Hi thanks for your kind reply...
I try to explain better...
I have a role for each chief of store.
In this role i set Dimension Store selecting only a store has default.
Others dimensions in the role has no settings that is ALL for all hierarchies and attributes..
Dimension CUSTOMERS has ALL members selected..
i.e
Role ROME has selected in Dimenson STORE only member 'STORE ROME'
Role MILAN has selected in Dimension STORE only member 'STORE MILAN'
Role VENICE has selected in Dimension STORE only member 'STORE VENICE'
I.E Chief of ROME STORE view only sales of ROME STORE.
Now Store's Chief need to view sum of sales of a customer that can buy in ALL stores...
CUSTOMER ROME STORE ALL STORES
James Untd. 100 750
I try to understand if it possible to create a calculated measure that sum all sales of a customer that bought in all stores.
Regard in advance.
March 19, 2015 at 9:51 am
There should be no need to create a calculated measure in my opinion.
Have a look at the attached image. By default, if you select a dimension member to allow access to...the "All" member is also selected. As long as your MDX query includes the "All" member you should see the total across all stores for a customer. The syntax would be something like:
select{[Measures].[Sales]} on 0
,{[Customer].[Customer Name].members} on 1
from[Cube]
The ".members' part is important as it includes the "All" level.
March 19, 2015 at 11:21 am
Thanks for reply.
So you tested with a ROLE that use Dimension City with the same meaning i need.
Okay now i am out of office ...i will try as sonn as possibile.
So i have not to change the user ROLE ...it should be magic.
I will write you again to tell if this solution works for me..
Thank you very much !!!! 🙂
March 19, 2015 at 5:27 pm
Hi i have tested your reply.
Unfortunately it does not work as i need.
I obtain a value too big ... i also did not understand what it is...
I created with your expression a calculated measure because i need to put it in a OLAP CUBE.
I tried also with this expression :
SUM ([Stores].[Stores].[Cod Store],[Measures].[Sales Amount])
But the result works good only if i browse cube with a role that can work with all stores.
---------------------------------------
Customer JAMES UNITED
Sales Amount Total All Stores by customer
rome 100 800
bologna 300 800
turin 400 800
===
800
------------------------------------------
On the other hand, if i browsw cube with a role that can permit to see only a store it does not work
---------------------------------------
Customer JAMES UNITED
Sales Amount Total All Stores by customer
bologna 300 300 (Instead 800)
===
300
------------------------------------------
I am little desolated...
Thanks again...:-)
March 20, 2015 at 9:12 am
maretix (3/19/2015)
SUM ([Stores].[Stores].[Cod Store],[Measures].[Sales Amount])
But the result works good only if i browse cube with a role that can work with all stores.
The syntax of your calculated measure is incorrect. You need to "step up" to the [All] level in your store dimension. The syntax would be something like:
sum(([Stores].[Stores].&[All],[Measures].[Sales Amount]))
March 20, 2015 at 1:39 pm
Hi thanks a lot for your answer ..
But nothing i cannot solve problem.
You were right to tell me my expression was wrong.
I corrected it in :
SUM([Codice_Filiale].[Codice_Filiale].[All CodiceFiliale],[Measures].[Importo Venduto])
It works but ONLY if i run a pivot table with a User ROLE that can every store..
If i run a pivot table with a User ROLE that is restricted in one only store...i receive the same value..
ROME 100 Total STORES 100 that is not true for that customer i selected...
I think i need something different ...
AGGREGATE ???
SCOPE ASSIGNMENTS ???
Regards have a good week-end
March 20, 2015 at 3:45 pm
With all due respect, I think you're doing something wrong. There is no need for scope assignments here...
The attached images show two exact queries, one executed with a role that has permission to all plants (plant is synonymous with store in my sample), and it shows that the customer shopped in two locations. It also shows the calculated measure for the total sales at the "All Plants" level.
The second image shows the same query, but executed within the context of a role that only has permission to the "New York" location. As you can see, the total of the calculated measure is still the same and accurate.
If you're still struggling to get this right, please post more information about the structure of your dimension(s), details of how you've implemented the role security (images will help), and also your complete query (not just snippets please).
An incorrect reference to an attribute, or the exclusion of the Plant dimension on the query axis (with the way my calculated member is defined), could all have resulted in a different outcome.
March 20, 2015 at 7:20 pm
Dimension Codice_Filiale (It is like for you Dimension Stores..)
I attach dimension structure and image about Role User FullStore and Role SelectedSpecificStore
Stat_Resp_Age_Cli is dimension like Customer
Analisys is about customer named B.DORIANO
CED.role can view all stores
CO16.role can view only store CO16...
[Importo Venduto Cliente Totale Azienda] is the calculated measure that should be sum all sales of all stores.
[Importo Venduto] is the measure that sum sales
You can see same result.
Thanks in advance..:-)
March 23, 2015 at 10:05 am
maretix (3/20/2015)
Dimension Codice_Filiale (It is like for you Dimension Stores..)I attach dimension structure and image about Role User FullStore and Role SelectedSpecificStore
Stat_Resp_Age_Cli is dimension like Customer
Analisys is about customer named B.DORIANO
CED.role can view all stores
CO16.role can view only store CO16...
[Importo Venduto Cliente Totale Azienda] is the calculated measure that should be sum all sales of all stores.
[Importo Venduto] is the measure that sum sales
You can see same result.
Thanks in advance..:-)
Ok, the good news is that there is nothing wrong with your roles or the way you've assigned them (although in my opinion you don't have to define dimension-level security for a role which will have permissions on everything).
What I think is happening here, is that the designer is generating a "bad" query for the view you are selecting. If you look at the source of your browsing view, you will most likely see that the designer is making use of sub-queries (or derived tables) to facilitate the filter.
The designer is known for doing this sort of thing, and I will recommend that you try and write an MDX query (without using the cube browser) like I have shown to see if you get the correct result.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply