February 8, 2010 at 9:32 am
Hi everyone,
I am having SSAS cube in which, in many of MDXs it is making use of hidden dimension called [Small Vs Big] , i.e., physically this dimension is not existing in any of the measure groups or dimension/hierarchy. And i want to replace this [Small Vs Big] dim with some alternative logic. for that, I need to get access to this dimension. (mainly this dimension is used within calculated members/set)
So, can anybody tell how to query such kind of hidden dimnesion/ OR even how to make it visible.
Any help would be appreciated.
Thanks
VT
February 8, 2010 at 9:43 am
Are you saying that, when viewed through BIDS/VS, the dimension does not exist in the dimension list (and is not an attribute in any other existing dimension)?
Possibly there is a calculated member being created in the MDX script of the cube. TO determine if this is the case, you need to go to the calculations tab and view the MDX script.
Steve.
February 8, 2010 at 9:59 am
Steve,
Yes that is the case. it is not listed in any dimensions/measures list.
I am viewing it from SSMS (once the cube has been deployed to the server). And I need to fix this issue within SSMS only as I do not have access to actual cube within BIDS/VS.
Thanks,
VT
February 8, 2010 at 10:05 am
For example-- below is a piece of code which make use of [Small Vs Big] dimension which is hidden within cube (i.e. not listed in dim lists) and I want to remove [Small Vs Big] part from the code with some alternate logic. So, to do such replacement I might need to know where this dim is actually located OR how to access it.
WITH
MEMBER [Measures].[Report Value All] AS (
[Measures].[Report Value]
,[Small Vs Big].[Small Vs Big].[All]
)
February 8, 2010 at 10:19 am
I think you've answered some of your own question - you need to have access to the cube to modify it in any way (such as removing a dimension), or to determine what it references.
You obviously have a reason for wanting to do this, but it's not overly clear why you would do this if the cube (and MDX written against it) actually work?
If you can access the cube via BIDS you can change the visibility setting on the dimension to make it visible, however the original cube designer obviously set it to False for some reason, so you may want to investigate that with you user-base also.
Lastly, there are a few ways you *may* be able to work around this, however it will take some (limited) coding and you'd have to have the rights set up to create/edit databases and cubes on the SSAS service, which it seems you already don't have otherwise you'd be viewing and editing the cube structure from within BIDS now :hehe:
Steve.
February 8, 2010 at 10:49 am
As I said earlier, I cannot change anything from BIDS/VS as I dont have access, so there is no point of setting visibility prop to 'True' (or sumthin like tht within BIDS) I have 2 wrk around SSMS only.
Secondly, I am already having access to cube within SSMS, but here the point is tht if I wanna replace tht dimension thn, I guess, I might need to have access to tht dim (more or less)
Lastly, I came into this situation of replacing dim because I have to migrate cube structure from old MDX to new MDX. To accomodate this thing I have to replace it with some alternate new MDX which does not contain tht dim.
Hope my concerns are clear to you.
VT
February 8, 2010 at 11:47 am
Ok, i *think* i'm following....
I think, in reality, you're not looking to 'deal' with a hidden dimension. You're looking to re-write the MDX to not use/reference it. To be able to do this, you need to understand exactly what this dimension contains. Seeing as you don't have access to the database via BIDS, the best you can do is infer.
To start inferring what it contains/is based on, you'll want to run some selects over the dimension to determine all of the values it contains e.g. SELECT [Big Vs Small].[Big Vs Small].MEMBERS ON 1, <some_measure> ON 0 FROM <cube>
Once you know (well, have inferred, you don't know for sure until you see the source for the dim) that members it contains, then you can start re-writing your MDX to remove reliance on the dimension.
A major issue you may run into here is that 'big vs small' is potentially determined by some attribute of either a transaction or dimension that *isn't* brought into the cube at all. If this is the case, you're not going to be able to achieve what you're looking for as you'll have no other attribute to use to determine 'big vs small' status for a given member in a dimension.
Steve.
February 8, 2010 at 12:24 pm
Hi,
Your suggested solution is what I have tried earlier which is very true, and because of that I came to know there are 2 members (only) for this [Big vs Small] dim.
1) Long (which contains positive values only)
2) Short (which contains negative values only)
Now based on this distinguistion, I have to replace [Big vs Small] with some kind of alternative logic.
I have tried using the below logic:
MEMBER [Measures].[Alternative] AS
CASE
WHEN [Measures].[Report Value] >= 0 THEN 'Long'
WHEN [Measures].[Report Value] < 0 THEN 'Short'
END
--And I have put everywhere [Measures].[Alternative] where it was [Big vs Small] earlier.
But this does not seem to work fine. Do you think of any alternate solution to this?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply