Re: how to use partitions once created?

Thank you very much again for your reply and for the explanations and
examples. Your example queries worked perfectly. I just have to keep doing
these queries until I see the pattern/logic of MDX queries and functions.
And yes, I partitioned by States - Conneticut and Vermont. Is that why this
query worked?

SELECT
{[Measures].[Amount],[Measures].[Sh Num]} ON COLUMNS,
{[Fund].[Fund Name].Members}
ON ROWS
FROM [Investments]
WHERE [Account].[State Abbr].[CT]

BTW, is a Where clause (in MDX) and a slice the same thing - semantically
speaking?

Thanks,
Rich

"Akshai Mirchandani [MS]" wrote:

> What you really want to do is:
>
> SELECT
> {[Measures].[Amount],[Measures].[Sh Num]} ON COLUMNS,
> {Crossjoin([Fund].[Fund Name].Members, { [Account].[State Abbr].[CT] } )}
> ON ROWS
> FROM [Investments]
>
> i.e. specify which member you want in the set itself. The WHERE clause in
> MDX is not meant for scenarios like this -- it is more for something like
> this:
>
> SELECT
> {[Measures].[Amount],[Measures].[Sh Num]} ON COLUMNS,
> {[Fund].[Fund Name].Members}
> ON ROWS
> FROM [Investments]
> WHERE [Account].[State Abbr].[CT]
>
> i.e you want all the funds but sliced for the CT state.
>
> Btw, you typically specify a slice as an MDX expression when you create the
> partition -- e.g. if you partitioned by states, it might be an expression:
> [Account].[State Abbr].[CT]
>
> Thanks,
> Akshai
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
> Please do not send email directly to this alias. This alias is for newsgroup
> purposes only.
>
> "Rich" <Rich@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:788FD303-6476-4EC3-9EED-30BE4871881A@xxxxxxxxxxxxxxxx
> > Thank you for your reply to my question. May I ask, how do you specify a
> > slice ona partition? For example, my cube is called "Investments". One
> > of
> > the partitions is called "Conneticut"
> >
> > SELECT
> > {[Measures].[Amount],[Measures].[Sh Num]} ON COLUMNS,
> > {Crossjoin([Fund].[Fund Name].Members, [Account].[State Abbr].Members )}
> > ON ROWS
> > FROM [Investments]
> > --Where
> >
> > This query yields something like (without any where clause):
> >
> > Amount Sh Num
> > 20th-Century Select CA $30,000.00 1,111.10
> > CT $38,000.00 1,421.62
> > FL
> > Acorn International CA
> > CT $245,841.30
> > FL
> >
> >
> > I tried adding something to the Where Clause
> >
> > Where [Account].[State Abbr].CT
> >
> > but got this error message "Unable to open cellset
> > Formula error - duplicate dimensions across (independent) axes - when
> > calculating a query axis"
> >
> > Well, even though this is probably not a partition slice, how could I get
> > this slice to work?
> >
> > Thanks,
> > Rich
> >
> >
> >
> > "Akshai Mirchandani [MS]" wrote:
> >
> >> Partitions are physical objects -- they allow better management of the
> >> data
> >> in a cube and in some ways improve performance. But you don't (and can't)
> >> query them directly -- the cube is the object that you care about.
> >>
> >> The "better management and performance" include:
> >> - creating rolling partitions so that old partitions don't need to be
> >> re-processed and new data can be incrementally added into the "current"
> >> partition
> >> - specifying slices on partitions so that partitions outside the slice in
> >> the query don't need to scanned
> >> - etc.
> >>
> >> Thanks,
> >> Akshai
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights
> >> Please do not send email directly to this alias. This alias is for
> >> newsgroup
> >> purposes only.
> >>
> >> "Rich" <Rich@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> news:D8600CDE-4B62-4D37-BCA8-8C733EC5340A@xxxxxxxxxxxxxxxx
> >> > Hello,
> >> >
> >> > I have a data warehouse/datamart database (please correct me what the
> >> > correct name is for the database containing the cubes) containing 2
> >> > cubes
> >> > called "Dividends" and "Investments". I created 3 partitions in the
> >> > "Investments" cube. I understand that partitions are a way to group
> >> > data
> >> > in
> >> > a cube that is getting large. My question is how is a partition used
> >> > once
> >> > created (and processed and so forth)? the "Investments" cube has 2
> >> > partitions "Conneticut", "Vermont".
> >> >
> >> > I opened up the MDX application and can only see the 2 cubes in this
> >> > database for querying. Is it possible to query either of the
> >> > partitions
> >> > "Conneticut" or "Vermont"?
> >> >
> >> > Any explanations appreciated on the usage/purpose of cube partitions.
> >> >
> >> > Thanks,
> >> > Rich
> >> >
> >> >
> >>
> >>
> >>
>
>
>
.

Relevant Pages

  • Re: Use expression/calculated fields as criteria?
    ... If the question is about using an alias in the WHERE clause when the alias ... In more complex queries I use the ... I know I could write a VBA function to return that value, ...
    (microsoft.public.access.queries)
  • Re: how to use partitions once created?
    ... I'm not sure what the question is here -- the physical partitions that you ... is a Where clause and a slice the same thing - semantically ... but a WHERE clause does more things like change the default ... Please do not send email directly to this alias. ...
    (microsoft.public.sqlserver.olap)
  • Re: JOIN on multiple conditions
    ... You must use the OUTER JOIN syntax, of course, ... >but if you learn only the INNER JOIN syntax does it change the way you ... More complicated queries should not be written by ... condition is placed in the ON or in the WHERE clause. ...
    (microsoft.public.sqlserver.programming)
  • Re: error -2147217904 - how to fix :o( ?
    ... If the saved queries don't have WHERE clauses, yes, the saved ... When I say on-the-fly SQL, ... use the base saved query and add a WHERE clause. ...
    (comp.databases.ms-access)
  • Re: problem getting a query to recognize "Not" and "<>" in a value list combo box
    ... When I think of queries, ... over for a while before I can really grasp it conceptually. ... The WHERE clause is just a Boolean algebra expression which evaluates to ... if I want to explicitly retrieve all records. ...
    (microsoft.public.access.forms)