Restricting a Dimension With Attributes (Essbase MDX)
Dear All,
We are trying to do exactly which is decribed in the Essbase MDX Designer Help Section as follows but we got some errors... (Please see Valerio's Post with errors details)
Restricting a Dimension With Attributes 1. Fill a vertical or horizontal hierarchy object with a dimension, and then fill a hierarchy menu with an associated attribute. 2. Draw a connection arrow from the hierarchy menu to the vertical or horizontal hierarchy object.
|
We can only test this in a customer enviroment because we don't have any other Essbase environtment, so we don't know if this is a bug of LV Analytics or a problem in the customer UDA definition in his Essbase database.
Could anyone please , confirm if this is working in your projects?
We would appreciate a lot if any of you could test in your Essbase environments.
Thanks in advance.
- 627 views
- 28 previews
- 1 version
- 5 replies
- 3 followers
- Posted By:
- Albert Aguilar Serramià
- January 15, 2021
About this forum
- 44,165 views
- 310 topics
- 8 followers
Looking for something you can't find? Have a suggestion on how we can improve? We want to hear from you!
Page Options
5 Replies
Hello Albert,
can you share the query generated in the restricted object?
I remember an issue we had working with UDAs in such a setup when the UDA name had a blank in it. A blank requires the UDA name to be enclosed in []within the query (like [UDA name] ) which Longview does not do. Therefore the generated query is invalid and does not return any result.
We could solve this by using the formula REPLACESTATEMENT.
Rgds,
Markus
Hello Markus,
Good news and bad news.
Good news: we have figured out that if only the menu, filled with the UDA, is applied with a dependency arrow to the dimension hierarchy, we could make it work changing the hierarchy format Requested levels to "Request all levels" and Initial state to "All requested levels are expanded". The UDA appears within the query generated within the SELECT:
SELECT UDA(Articoli, "MONTE") DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME], [MEMBER_CAPTION], [GEN_NUMBER], [LEVEL_NUMBER] ON COLUMNS FROM [acquisti].[ACQUISTI]
Bad news: If we apply more menus, filled with one or more other dimensions (or the same as the one of the hierarchy to be filtered), the dependency from the UDA loses its effect on the hierarchy (it disappears from the query generated).
SELECT DESCENDANTS([PASTE_COLORANTI], [PASTE_COLORANTI].LEVEL, AFTER) DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME], [MEMBER_CAPTION], [GEN_NUMBER], [LEVEL_NUMBER] ON COLUMNS FROM [acquisti].[ACQUISTI]
So, we still can't use UDAs.
Regards
Valerio
Hello Valerio,
1. As the UDA is a flat list I would suggst to always use menus instead of hierarchy menus - my experience is that you have less issues that way ;-)
2. Regarding the last query: are you sure you have both the UDA and another dimension connected as a restriction? I just did a quick test on the Essbase Sample Basic cube and came up with the following:
SELECT NON EMPTY UDA(Market, "Small Market") DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME], [MEMBER_CAPTION], [GEN_NUMBER], [LEVEL_NUMBER] ON COLUMNS FROM [Sample].[Basic] WHERE ([400-20])
I queried the market dimension and restricted it with the UDA of market (green part) and additionally with the product dimension (red part above).
Rgds,
Markus
Thank you Markus,
In my test, indeed, I am using a simple menu for the UDA and the dependencies from both menus are correctly dragged.
Anyway, your test made me see that I have no WHERE clause, if I filter a dimension using a menu filled with the same dimension. The selection will be placed in the SELECT clause (as you can see from my second example) and it looks like "there is not enough place" for UDA there.
Instead, if I filter a dimension using a menu filled with a different dimension, it will be placed on the WHERE clause and the UDA come back to the SELECT clause. Unfortunately, when I do it (and I have a query very similar to yours) many errors come up (1042013, 1006078, 1042006, Network Error [10054], Network error [10061]) and gives me as a result an empty object.
SELECT NON EMPTY UDA(Articoli, "CROVE") DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME], [MEMBER_CAPTION], [GEN_NUMBER], [LEVEL_NUMBER] ON COLUMNS FROM [acquisti].[ACQUISTI] WHERE ([Totale] , [Mese] , [TEMPO] , [Misure] , [BO] , [Input] , [Totale_magazzini] , [FORNITORI_ALL])
I will start searching for their meanings.
Kind Regards
Valerio
Hello Everybody,
I will share the diagnosis I have done, the temporary solution I have applied and an example.
Diagnosis: when the query auto-generates itself within a hierarchy object, it is able to place in the SELECT clause only one set from one dimension.
In our case, if I have two hierarchy objects (a hierarchy menu and a vertical hierarchy) filled with the same dimension (ex. Products) and I drag a dependency arrow from the menu to the vertical hierarchy to filter its descendants, the SELECT clause will show a set from that dimension; if in addition I drag a dependency arrow from a UDA of the same dimension (Products), it will not be able to put the UDA in the SELECT clause or as a slicer. Then it will be ignored.
Solution: in order to have within the SELECT clause both the required set of a dimension (ex. Product) and the related UDA (from the same dimension) I used the REPLACESTATEMENT Longview Analytics function in order to be able to add to the auto-generated query, the INTERSECT MDX function, that will allow to place in the SELECT clause both sets from the same dimension.
Example:
the SELECT clause will change from
SELECT DESCENDANTS([PASTE_COLORANTI], [PASTE_COLORANTI].LEVEL, AFTER)
to
SELECT INTERSECTION(
DESCENDANTS([PASTE_COLORANTI], [PASTE_COLORANTI].LEVEL, AFTER)
, UDA([Articoli], "CROVE")
)
Anyway, I think this issue should be addressed by the product developers side to simplify its use.
Regarding the Essbase Errors I have received, mentioned in the previous reply, I think they were related to how data is distributed within the cube (I am not sure anyway).
Kind Regards
Valerio