mdx question

Hi,
I was wondering if anyone could help me with a bit of mdx.
We want to display something like this:
Year
+ Month
Company
Num of Cases
Num of Cases that haven't been paid
We want to show the number of cases for a company that were created in
certain year/month time slices but we also want to show the number of cases
that haven't been paid.
We find if the the case has not been paid or not from child table so we have
following situation:
Fact Table: Case - Measure is id (Count)
Dimension Tables: Case Company - Level is name
Case - level is created date
Case further details - paid date
We have tried many possibilities but can't seem to get the number of cases
for when the paid date is empty. I suppose our best try is:
Count(
Filter(
CrossJoin([Measures].members, [PaidDate].Members),
not IsEmpty([PaidDate].CurrentMember )
)
)
but this gives the wrong value. All the other attempts give us errors
(either syntactical or runtime).If I understand your message.
You can create a different cube called Cases that have this set of
dimension: Periods.Year.Month and Company and for measures that have value
of paid or code that show if it have been paid or not.
On principal cube you will use LookUpCube("Cases","(code)") where (code) is
equal to :
Count(<set>,measures.value of Cases cube) this <set> will be composed from
dimension that you have in Cases.
Remeber that dimensions in Cases cube must be exists in principal cube and
must be equal for name and level etc..
I hope that I help you.
Bye.
"FraserS" <FraserS@.discussions.microsoft.com> ha scritto nel messaggio
news:7899446A-6E43-4975-B1BD-16872EB93F93@.microsoft.com...
> Hi,
> I was wondering if anyone could help me with a bit of mdx.
> We want to display something like this:
> Year
> + Month
> Company
> Num of Cases
> Num of Cases that haven't been paid
>
> We want to show the number of cases for a company that were created in
> certain year/month time slices but we also want to show the number of
> cases
> that haven't been paid.
>
> We find if the the case has not been paid or not from child table so we
> have
> following situation:
> Fact Table: Case - Measure is id (Count)
> Dimension Tables: Case Company - Level is name
> Case - level is created date
> Case further details - paid date
>
> We have tried many possibilities but can't seem to get the number of cases
> for when the paid date is empty. I suppose our best try is:
>
> Count(
> Filter(
> CrossJoin([Measures].members, [PaidDate].Members),
> not IsEmpty([PaidDate].CurrentMember )
> )
> )
>
> but this gives the wrong value. All the other attempts give us errors
> (either syntactical or runtime).
>|||With OLAP date dimensions, you would typically set an arbitrary large value
(say, 12/31/9999) to indicate that an event (in this case, payment) had not
yet occurred. Note that IsEmpty([PaidDate].CurrentMember) does not test for
an empty date. So, the Num of Cases that haven't paid, for a selected
[CreatedDate], would then be:
([Measures].[CaseCount], [Paid Date].[12/31/9999])
"FraserS" wrote:
> Hi,
> I was wondering if anyone could help me with a bit of mdx.
> We want to display something like this:
> Year
> + Month
> Company
> Num of Cases
> Num of Cases that haven't been paid
>
> We want to show the number of cases for a company that were created in
> certain year/month time slices but we also want to show the number of cases
> that haven't been paid.
>
> We find if the the case has not been paid or not from child table so we have
> following situation:
> Fact Table: Case - Measure is id (Count)
> Dimension Tables: Case Company - Level is name
> Case - level is created date
> Case further details - paid date
>
> We have tried many possibilities but can't seem to get the number of cases
> for when the paid date is empty. I suppose our best try is:
>
> Count(
> Filter(
> CrossJoin([Measures].members, [PaidDate].Members),
> not IsEmpty([PaidDate].CurrentMember )
> )
> )
>
> but this gives the wrong value. All the other attempts give us errors
> (either syntactical or runtime).
>|||Hi, thanks for your replies.
In order to achieve this, we tried to set the default member to an arbitrary
large value like you said. This didn't work. How should we go about doing
this?
"Deepak" wrote:
> With OLAP date dimensions, you would typically set an arbitrary large value
> (say, 12/31/9999) to indicate that an event (in this case, payment) had not
> yet occurred. Note that IsEmpty([PaidDate].CurrentMember) does not test for
> an empty date. So, the Num of Cases that haven't paid, for a selected
> [CreatedDate], would then be:
> ([Measures].[CaseCount], [Paid Date].[12/31/9999])
>
> "FraserS" wrote:
> > Hi,
> >
> > I was wondering if anyone could help me with a bit of mdx.
> >
> > We want to display something like this:
> >
> > Year
> >
> > + Month
> >
> > Company
> >
> > Num of Cases
> >
> > Num of Cases that haven't been paid
> >
> >
> >
> > We want to show the number of cases for a company that were created in
> > certain year/month time slices but we also want to show the number of cases
> > that haven't been paid.
> >
> >
> >
> > We find if the the case has not been paid or not from child table so we have
> > following situation:
> >
> > Fact Table: Case - Measure is id (Count)
> >
> > Dimension Tables: Case Company - Level is name
> >
> > Case - level is created date
> >
> > Case further details - paid date
> >
> >
> >
> > We have tried many possibilities but can't seem to get the number of cases
> > for when the paid date is empty. I suppose our best try is:
> >
> >
> >
> > Count(
> >
> > Filter(
> >
> > CrossJoin([Measures].members, [PaidDate].Members),
> >
> > not IsEmpty([PaidDate].CurrentMember )
> >
> > )
> >
> > )
> >
> >
> >
> > but this gives the wrong value. All the other attempts give us errors
> > (either syntactical or runtime).
> >

MDX Query Governor ?

Hi,

There are 2 problematic cases that would require some kind of MDX Query Governor or time out:

- User leave an endless query running forever

- User cancel an endless query on the client and it does not get cancelled on the server

I looked into the server properties and saw a couple items like Admin Time out, External Connection Time out etc.

Which one of these properties should I use to get the SSAS server kill run-away queries that where either let go or canceled on the client side but still run and eat the CPU on the server? My take is that any Client issued MDX lasting over 30 minutes should be automatically cancelled on the server.

Right now I restart the server when it get too bad but it is not a solution, one cannot work with a server that need restart every single day.

It is also unpracticable to have anyone spending valuable time chasing down useless connections/queries and cancelling them manually.

Thanks,

Philippe

For one I suggest you take a look at the ActivityViewer sample application to see long running queries. You can also use it to cancel user sessions and connections to free up server resources.

Governing resources is not a simple problem to solve. We are working on it and hope that in the next version your will see greater responcivencess of Cancel and you will see better ways to detect and cancel runaway queries.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.