I want to get all the country names from a cube where another Key figure is NOT equal spaces or zeros. Please let me know if some one can help me.
My Cube is CFS_PERF/CFS_PERF_CFSPERF_GL
Want to get 0PAYER_COUNTRY where
[Measures].[3M1DTA8FO1HVYVOI6HUABEXHX] is NOT equal to zero
Please help !!!
Try this:
SELECT [Measures].[3M1DTA8FO1HVYVOI6HUABEXHX] ON 0,
NON EMPTY [OPAYER_COUNTRY].members ON 1
FROM CFS_PERF_CFSPERF_GL
Hi,
I need to find all dimensions in a cube that have calculated members but I can't afford to call GetMembers on every hierarchy level.
Visual Studio lists all the calculations on a cube (calculated members, named sets and script commands) - I'd like to find this information using AdomdClient
Thanks,
James
Background: Our product allows users to drag dimensions onto a report and filter them - we have to write the MDX on the fly to do this - in order to allow filtering of calculated members we need to distinguish if a dimension has calculated members.
> Visual Studio lists all the calculations on a cube (calculated members, named sets and script commands) - I'd like to find this information using AdomdClient
Not possible.
> Background: Our product allows users to drag dimensions onto a report and filter them - we have to write the MDX on the fly to do this - in order to allow filtering of calculated members we need to distinguish if a dimension has calculated members
Can you please clarify in more details what is the special about calculated members so that you want to know in advance whether hierarchy has them.
|||Hi Mosha,
Thanks for the reply, I believe our very own Dave Grant showed you some of RSinteract’s MDX at PASS in Seattle.
We decided that placing the filters in the sub-query allowed us to provide more user friendly totalling (i.e. when filtering row or column attributes). However calculated members are not allowed in sub-queries, so we need to know which Dimension Attributes contain Calc Members and place any filters inline or in a where clause. Our current solution is to have an administrator run an AMO application which saves dimensions with calc members to a config file (AMO must be run with admin privileges).
Cheers,
James
|||What I wonder is why do you want to exclude the entire hierarchy just because it may have calculated members. It seems like if you just wrap the sets that you put inside subselects with StripCalculatedMembers function call, both you and your users will be better of.Hi,
I'm designing a cube for analyzing financial data from an accounting application.
Basically, the facts table looks like:
(idAccount, idTime, nValue)
that shows every financial operation for any account, and it's very easy to build a cube for analysing the accounting movements.
But there is another important measure, that's the balance of every account in any time.
A tipical report would be, for a whole year, the sum of movements of any account and the balance at the end of the period.
I think adding a new column to the facts table like:
(idAccount, idTime, nValue, nBalance)
wouldn't work, because any account has a balance in any time, even thought there isn't movements in that time period.
What do you think would be a good design approach?
Why not break it into two (Seems like they are unrelated facts that happen to have common dimensions):
idAccount, idTime, nValue
idAccount, idTime, nBalance
|||Could work, but with that approach I would have a row in the fact table for every day and for every account, and that doesn't look very efficient.
Imagine a "sales" account, that has a balance value in day 10 of 10,000$, and there are more sales after that day:
(idAccount="sales", idTime=10, nBalance=10000)
(idAccount="sales", idTime=15,nValue=500)
so, (idAccount="sales", idTime=15, nBalance=10500)
That means, the balance value changes after any accounting operation.
And now, what happens if the user makes a report that has a row for every account with two more columns: nBalance and nValue, and chooses only the day 20?
For the "sales" account nValue = 0, that's clear, but, what happens with nBalance?
It should be 10500, becuase the balance didn't change after the day 15.
The problem is that I don't know how to create the nBalance measure in order to get that behaviour.
The only way I can figure out is filling the (idAccount, idTIme, nBalance) table with the balance of every account for every day, and that seems to be a waste of resources, because there're many accounts that have very few changes in their balance over the year.
Any suggestion?
P.S. Another problem is the calculation of aggregations for nBalance. Aggregations for nBalance in the account dimension must be a sum of nBalance for any account. But for time dimension, nBalance is the value of nBalance in the last time of the time dimension.
I can figure out many "real wolrd" problems similar to this. For example, imagine that you have a solution for identifying the stock in a warehouse. There are, at least, two main measures: the ins and outs of the warehouse and the actual stock in a certain value of time.
Hello,
I have built a report using a Cube (and not a relational database).
I have a date as a parameter and I would like to filter its content: the parameter goes from 1900 to 2090 and I would like the user to see only from 2006 to 2090.
Can you help me by giving me tricks to do it ? There may have several ways of doing it but I can't find them.
Thank you in advance !
Alexis
If you always want it to start from 2006( not change to 2007 to 2090 when it is 2008) it′s easy in MDX:SELECT {[Time].[Year].&[2006] : NULL } on rows, bla bla bla.
This will make it take every member from the time dimension from 2006 up til the end, in your case 2090.|||
Thank you, it works perfectly !
It would be interesting to make dynamic date range according to the actual year but.... next time ! 
I would appreciate any help with the following problem:
Other than cube redesign, which I know we can accomplish, if theres an actual way through MDX to filter structure between 2 dimensions. As of right now I can only accomplish filtering measures by 2 dimensions. Example:
SELECT { [Measures].Members } ON COLUMNS,
{ [Dimension1].[AnyHeirarchy].[AnyHeirarchyLevel].Members } ON ROWS
FROM [Planogram]
WHERE ( [Dimension2].[AnyHeirarchy].[AnyHeirarchyLevel].[AnyMember] )
What occurs is all members from Dimension1 is returned and the Measures are filtered accordingly. What I want is it to only bring back the rows which have a relationship with Dimension2. Is this possible?
But if I try to filter structure across 2 different heirarchies in the same dimension it works fine.
SELECT { [Measures].Members } ON COLUMNS,
{ [Dimension1].[Heirarchy2].[AnyHeirarchyLevel].Members } ON ROWS
FROM [Planogram]
WHERE ( [Dimension1].[Heirarchy1].[AnyHeirarchyLevel].[AnyMember] )
This only returns rows in which the two heirarchy members are related.
So obvisouly If I moved my 2nd Dimension under the 1st Dimesion and make it a heirachy it would work, but any other way around this?
Thanks a lot.
When you say: "only bring back the rows which have a relationship with Dimension2", are they only related via the fact table, or in some other way? In the former case, try Non Empty, like:
SELECT { [Measures].Members } ON COLUMNS,
Non Empty [Dimension1].[AnyHeirarchy].[AnyHeirarchyLevel].Members ON ROWS
FROM [Planogram]
WHERE ( [Dimension2].[AnyHeirarchy].[AnyHeirarchyLevel].[AnyMember] )
Thanks for the reply.
Yes, they are only related via the fact table. If I try non empty, and please correct me if im wrong, that will filter by measures (sales, units, etc), and only show members that have sales/units. In essence exluding the real relationships (members that have no sales won't be shown)
I would like to bring back every member that is related to Dimension 2's member, regardless of no sales or not.
Thanks.
|||The fact of sales is a relation between dimension1 and dimension2. There isn't another relation between dimension1 and dimension2.
If you have another one the it should exists as another fact table or anything else.
