logical table source selection

[Robert Tooker]

Hi Gurus,

I have some dimensions that can come from different sources depending on the context. The reason behind this is we use a 3rd party aggregation engine as well as normal oracle tables as data sources.

All works well, but I can't control which LTS is used when only dimension information is required. In some dimensions it goes to the oracle table, and in others it goes to the aggregation engine. I require it to only go to the oracle table if no facts are selected.

Levels have been set up correctly and I've tried re-ordering the sources to no avail. Any ideas how OBIEE decides?

Thanks,

Robert Tooker

[Stewart Bryson]

OBIEE does not support "dimension-only" reporting, per se. That's why it will "grab" at least one measure when you don't specify one, because that is simply how the BI Server works: dimensional attributes and measures.

What I would recommend is configuring a logical fact table that is a "factless fact" table that is used for doing dimension-only reporting, with a mock measure that always equals "1". This can be done in the LTS, by the way... you don't need this actual table to exist in the database. Then, when you want to do dimension-only reporting, select the attributes you are interested in, and the mock measure from the logical fact table. This will allow you to control how the BI Server navigates... and not be at it's mercy.

Stewart Bryson

--
You received this message because you are subscribed to the Google
Groups "OBIEE Enterprise Methodology Group" group.
To post to this group, send email to
obiee-enterpri...@googlegroups.com
To unsubscribe from this group, send email to
obiee-enterprise-met...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/obiee-enterprise-methodology?hl=en
 
All content to the OBIEE EMG lies under the Creative Commons Attribution 3.0 Unported License (http://creativecommons.org/licenses/by/3.0/). Any content sourced must be attributed back to the OBIEE EMG with a link to the Google Group (http://groups.google.com/group/obiee-enterprise-methodology).

[Robert Tooker]

Sorry, I didn't make that quite clear. I'm only talking about querying one dimension at a time - i.e. the query that might be produced for a dashboard prompt. Because it's from a single dimension there is no explicit or implicit measure and the logical and physical sqls generated are dimension only. The question is can you control the order of selection of the logical table source or is it arbitrary?

Regards,

Robert Tooker

[Jeff McQuigg]

Robert,

Stewart's answer still applies.

You need to first have OBI pick a Fact LTS, and then from there it
will pick the Dimensional LTS. You can do this with the specific
fields as Stewart mentions. You could then make a separate subject
area for prompts, set an implicit fact column, and tie the fact column
to a FACT LTS which in turn ties to the dimensional source you want.

Jeff M.

On Jan 4, 1:31 pm, Robert Tooker <robert.too...@gmail.com> wrote:
> Sorry, I didn't make that quite clear. I'm only talking about querying one
> dimension at a time - i.e. the query that might be produced for a dashboard
> prompt. Because it's from a single dimension there is no explicit or
> implicit measure and the logical and physical sqls generated are dimension
> only. The question is can you control the order of selection of the logical
> table source or is it arbitrary?
>
> Regards,
>
> Robert Tooker
>

> On Tue, Jan 4, 2011 at 7:30 PM, Stewart Bryson <stewartbry...@gmail.com>wrote:
>
> > OBIEE does not support "dimension-only" reporting, per se. That's why it
> > will "grab" at least one measure when you don't specify one, because that is
> > simply how the BI Server works: dimensional attributes and measures.
>
> > What I would recommend is configuring a logical fact table that is a
> > "factless fact" table that is used for doing dimension-only reporting, with
> > a mock measure that always equals "1". This can be done in the LTS, by the
> > way... you don't need this actual table to exist in the database. Then, when
> > you want to do dimension-only reporting, select the attributes you are
> > interested in, and the mock measure from the logical fact table. This will
> > allow you to control how the BI Server navigates... and not be at it's
> > mercy.
>
> > Stewart Bryson
>

> > On Tue, Jan 4, 2011 at 12:29 PM, Robert Tooker <robert.too...@gmail.com>wrote:
>
> >> Hi Gurus,
>
> >> I have some dimensions that can come from different sources depending on
> >> the context. The reason behind this is we use a 3rd party aggregation engine
> >> as well as normal oracle tables as data sources.
>
> >> All works well, but I can't control which LTS is used when only dimension
> >> information is required. In some dimensions it goes to the oracle table, and
> >> in others it goes to the aggregation engine. I require it to only go to the
> >> oracle table if no facts are selected.
>
> >> Levels have been set up correctly and I've tried re-ordering the sources
> >> to no avail. Any ideas how OBIEE decides?
>
> >> Thanks,
>
> >> Robert Tooker
>
> >> --
> >> You received this message because you are subscribed to the Google
> >> Groups "OBIEE Enterprise Methodology Group" group.
> >> To post to this group, send email to
> >> obiee-enterpri...@googlegroups.com
> >> To unsubscribe from this group, send email to

> >> obiee-enterprise-met...@googlegroups.com<obiee-enterprise-methodology%2Bunsu...@googlegroups.com>

> >> For more options, visit this group at
> >>http://groups.google.com/group/obiee-enterprise-methodology?hl=en
>
> >> All content to the OBIEE EMG lies under the Creative Commons Attribution
> >> 3.0 Unported License (http://creativecommons.org/licenses/by/3.0/). Any
> >> content sourced must be attributed back to the OBIEE EMG with a link to the
> >> Google Group (http://groups.google.com/group/obiee-enterprise-methodology
> >> ).
>
> >  --
> > You received this message because you are subscribed to the Google
> > Groups "OBIEE Enterprise Methodology Group" group.
> > To post to this group, send email to
> > obiee-enterpri...@googlegroups.com
> > To unsubscribe from this group, send email to

> > obiee-enterprise-met...@googlegroups.com<obiee-enterprise-methodology%2Bunsu...@googlegroups.com>

[Adrian Ward]

IF you are talking about multiple LTS for a single dimension then the engine has a few 'rules' which it applies.  These can be found with trial and error, but are generally relaled to Levels, joins in the LTS and where conditions.  

The trick is to have a Logical column which is connected to only one of the LTS, so if you have three LTS then created three logical columns individually linked.  You then use which ever column you want to for use of that LTS.

Adrian Ward

Principal Consultant

Rittman Mead 

M: 07545 922344

E: adria...@rittmanmead.com

Winners of the UKOUG BI Partner of the Year Award 2010/11

Latest news and expert insight on OBIEE 11g at the Rittman Mead OBIEE 11g Resource Centre

[Adrian Ward]

BTW  There is a bug in 10g (not checked 11g yet), on Multi select prompts.  

You can set the source of a prompt to be based upon a SQL statement.  That way your prompted field can be from a fact table (I know you really want to use a dimension but sometimes when reporting on OLTP you have no choice!), but the list in the prompt is derived from a much smaller dimension.  The bug comes in when you try to use the filter in the Prompt dialogue. OBIE ignores your SQL and applies a query directly against the fact table.

Adrian Ward

Principal Consultant

Rittman Mead 

M: 07545 922344

E: adria...@rittmanmead.com

Winners of the UKOUG BI Partner of the Year Award 2010/11

Latest news and expert insight on OBIEE 11g at the Rittman Mead OBIEE 11g Resource Centre

[Robert Tooker]

If that's the case then there is no indication of which fact is chosen. If you select a single dimension field in Answers and check the logical request, execution plan, and physical sql you will see that no fact LTS is present.

[Robert Tooker]

Thanks Adrian. I have tried that and it works but unfortunately I need it to be transparent to the end user - i.e. whichever attribute they choose in that dimension it hits LTS "A" unless a measure is involved in which case choose LTS "A" or "B" as appropriate. I'm coming to the conclusion that because both LTS "A" and "B" are at the same level of detail there is no reason for OBIEE to choose one of the other and therefore no way for me to control it.

Regards,

Robert

[Jeff McQuigg]

Yes, but thats why you configure an Implicit Fact on the Presentation
Catalog - it will force the fact you chose to be there. By doing
this, you control the Fact LTS and therefore the Dim LTS.

On Jan 5, 1:30 am, Robert Tooker <robert.too...@gmail.com> wrote:
> If that's the case then there is no indication of which fact is chosen. If
> you select a single dimension field in Answers and check the logical
> request, execution plan, and physical sql you will see that no fact LTS is
> present.
>

> > <obiee-enterprise-methodology%2Bunsu...@googlegroups.com<obiee-enterprise-methodology%252Buns...@googlegroups.com>

>
> > > >> For more options, visit this group at
> > > >>http://groups.google.com/group/obiee-enterprise-methodology?hl=en
>
> > > >> All content to the OBIEE EMG lies under the Creative Commons
> > Attribution
> > > >> 3.0 Unported License (http://creativecommons.org/licenses/by/3.0/).
> > Any
> > > >> content sourced must be attributed back to the OBIEE EMG with a link
> > to the
> > > >> Google Group (
> >http://groups.google.com/group/obiee-enterprise-methodology
> > > >> ).
>
> > > >  --
> > > > You received this message because you are subscribed to the Google
> > > > Groups "OBIEE Enterprise Methodology Group" group.
> > > > To post to this group, send email to
> > > > obiee-enterpri...@googlegroups.com
> > > > To unsubscribe from this group, send email to
> > > > obiee-enterprise-met...@googlegroups.com<obiee-enterprise-methodology%2Bunsu...@googlegroups.com>

> > <obiee-enterprise-methodology%2Bunsu...@googlegroups.com<obiee-enterprise-methodology%252Buns...@googlegroups.com>

[Adrian Ward]

Interesting related post 

http://total-bi.com/2011/01/obiee-11g-dimension-browse/

Adrian 

[Adrian Ward]

Interesting related post 

http://total-bi.com/2011/01/obiee-11g-dimension-browse/

Adrian 

On 5 Jan 2011, at 09:30, Robert Tooker wrote:

[Mark Rittman]

Yes, I spotted that. I think Venkat mentioned in a previous conversation that this was for future functionality (i.e. it wasn't enabled in this release), but it looks like it might be worth checking out now.

Mark

Mark Rittman
Director
M:+44 (0) 7866 568 246
F: +44 (0) 1273 784 960
E: mark.r...@rittmanmead.com

Winners of the UKOUG Business Intelligence Partner of the Year Award 2010/11

www.rittmanmead.com

Registered Office : Third Floor South Suite, 1 Jubilee Street Brighton, BN1 1GE, United Kingdom
Company No. : 6032852
VAT No. : 900 3839 48

Please note that this email communication is intended only for the addressee and may contain confidential or privileged information. The contents of this email may be circulated internally within your organisation only and may not be communicated to third parties without the prior written permission of Rittman Mead Consulting.  This email is not intended nor should it be taken to create any legal relations, contractual or otherwise.

[Robert Tooker]

I also checked support and there is a log from maybe a year ago which has been put as an 'enhancement request' (although it was around LTS selection for facts and not dimensions). Looks like I just need an HP-UX port of 11g ...

Robert

[Robert Tooker]

Jeff, implicit facts are not relevant when only selecting from one dimension. From the documentation:

"If you set an implicit fact column this column will be added to a query when it contains columns from
two or more dimension tables and no measures. The column is not visible in the results. It is used
to specify a default join path between dimension tables when there are several possible alternatives."

Regards,

Robert

[Adrian Ward]

My best advice is to 'trick' the system into using your LTS A by using the 'rules' it applies to make sure that LTS A is preferred.  This may take some trial and error, and the only person I know who probably knows the rules is Kurt.  As an extreme you could make LTS Z (a copy of A) and set it to a dimension hierarchy level above the 'Detail', but hopefully you can get the other settings to do the work for you.

If I get time later I'll have a play

- Anyone on copy have a theory as to the order in which the rules are applied?

Adrian

Rittman Mead Consulting

UKOUG Business Intelligence Partner of the Year 2008/2009

Mobile:+44 (0) 7545 922 344
Fax:    +44 (0) 1273 784 960
Email: adria...@rittmanmead.com
Skype: adyward
Web: http://www.rittmanmead.com

[Jeff McQuigg]

I've actually done this before, setting up some dummy hierarchies with
differing level counts in order to establish a bit of a priority
system - Dim Priority. It had to be a dummy fact table. I consider
it a 'stupid RPD trick' at best.

Jeff M.

On Jan 5, 2:16 pm, Adrian Ward <adrian.w...@rittmanmead.com> wrote:
> My best advice is to 'trick' the system into using your LTS A by using the
> 'rules' it applies to make sure that LTS A is preferred.  This may take some
> trial and error, and the only person I know who probably knows the rules is
> Kurt.  As an extreme you could make LTS Z (a copy of A) and set it to a
> dimension hierarchy level above the 'Detail', but hopefully you can get the
> other settings to do the work for you.
>
> If I get time later I'll have a play
>
> - Anyone on copy have a theory as to the order in which the rules are
> applied?
>
> Adrian
>

> On 5 January 2011 09:23, Robert Tooker <robert.too...@gmail.com> wrote:
>
>
>
> > Thanks Adrian. I have tried that and it works but unfortunately I need it
> > to be transparent to the end user - i.e. whichever attribute they choose in
> > that dimension it hits LTS "A" unless a measure is involved in which case
> > choose LTS "A" or "B" as appropriate. I'm coming to the conclusion that
> > because both LTS "A" and "B" are at the same level of detail there is no
> > reason for OBIEE to choose one of the other and therefore no way for me to
> > control it.
>
> > Regards,
>
> > Robert
>

> > On Wed, Jan 5, 2011 at 8:17 AM, Adrian Ward <adrian.w...@rittmanmead.com>wrote:
>
> >> IF you are talking about multiple LTS for a single dimension then the
> >> engine has a few 'rules' which it applies.  These can be found with trial
> >> and error, but are generally relaled to Levels, joins in the LTS and where
> >> conditions.
> >> The trick is to have a Logical column which is connected to only one of
> >> the LTS, so if you have three LTS then created three logical columns
> >> individually linked.  You then use which ever column you want to for use of
> >> that LTS.
>
> >>    Adrian Ward
> >> Principal Consultant
> >> Rittman Mead
> >> M: 07545 922344

> >> *E: adrian.w...@rittmanmead.com*

>
> >> Winners of the UKOUG BI Partner of the Year Award 2010/11
>
> >> Latest news and expert insight on OBIEE 11g at the Rittman Mead OBIEE 11g

> >> Resource Centre <http://www.rittmanmead.com/obiee11g/>

>
> >> On 4 Jan 2011, at 21:31, Robert Tooker wrote:
>
> >> Sorry, I didn't make that quite clear. I'm only talking about querying one
> >> dimension at a time - i.e. the query that might be produced for a dashboard
> >> prompt. Because it's from a single dimension there is no explicit or
> >> implicit measure and the logical and physical sqls generated are dimension
> >> only. The question is can you control the order of selection of the logical
> >> table source or is it arbitrary?
>
> >> Regards,
>
> >> Robert Tooker
>

> >> On Tue, Jan 4, 2011 at 7:30 PM, Stewart Bryson <stewartbry...@gmail.com>wrote:
>
> >>> OBIEE does not support "dimension-only" reporting, per se. That's why it
> >>> will "grab" at least one measure when you don't specify one, because that is
> >>> simply how the BI Server works: dimensional attributes and measures.
>
> >>> What I would recommend is configuring a logical fact table that is a
> >>> "factless fact" table that is used for doing dimension-only reporting, with
> >>> a mock measure that always equals "1". This can be done in the LTS, by the
> >>> way... you don't need this actual table to exist in the database. Then, when
> >>> you want to do dimension-only reporting, select the attributes you are
> >>> interested in, and the mock measure from the logical fact table. This will
> >>> allow you to control how the BI Server navigates... and not be at it's
> >>> mercy.
>
> >>> Stewart Bryson
>

> >>> On Tue, Jan 4, 2011 at 12:29 PM, Robert Tooker <robert.too...@gmail.com>wrote:
>
> >>>> Hi Gurus,
>
> >>>> I have some dimensions that can come from different sources depending on
> >>>> the context. The reason behind this is we use a 3rd party aggregation engine
> >>>> as well as normal oracle tables as data sources.
>
> >>>> All works well, but I can't control which LTS is used when only
> >>>> dimension information is required. In some dimensions it goes to the oracle
> >>>> table, and in others it goes to the aggregation engine. I require it to only
> >>>> go to the oracle table if no facts are selected.
>
> >>>> Levels have been set up correctly and I've tried re-ordering the sources
> >>>> to no avail. Any ideas how OBIEE decides?
>
> >>>> Thanks,
>
> >>>> Robert Tooker
>
> >>>> --
> >>>> You received this message because you are subscribed to the Google
> >>>> Groups "OBIEE Enterprise Methodology Group" group.
> >>>> To post to this group, send email to
> >>>> obiee-enterpri...@googlegroups.com
> >>>> To unsubscribe from this group, send email to

> >>>> obiee-enterprise-met...@googlegroups.com<obiee-enterprise-methodology%2Bunsu...@googlegroups.com>

> >>>> For more options, visit this group at
> >>>>http://groups.google.com/group/obiee-enterprise-methodology?hl=en
>
> >>>> All content to the OBIEE EMG lies under the Creative Commons Attribution
> >>>> 3.0 Unported License (http://creativecommons.org/licenses/by/3.0/). Any
> >>>> content sourced must be attributed back to the OBIEE EMG with a link to the
> >>>> Google Group (
> >>>>http://groups.google.com/group/obiee-enterprise-methodology).
>
> >>> --
> >>> You received this message because you are subscribed to the Google
> >>> Groups "OBIEE Enterprise Methodology Group" group.
> >>> To post to this group, send email to
> >>> obiee-enterpri...@googlegroups.com
> >>> To unsubscribe from this group, send email to

> >>> obiee-enterprise-met...@googlegroups.com<obiee-enterprise-methodology%2Bunsu...@googlegroups.com>

> >> obiee-enterprise-met...@googlegroups.com<obiee-enterprise-methodology%2Bunsu...@googlegroups.com>

> >> For more options, visit this group at
> >>http://groups.google.com/group/obiee-enterprise-methodology?hl=en
>
> >> All content to the OBIEE EMG lies under the Creative Commons Attribution
> >> 3.0 Unported License (http://creativecommons.org/licenses/by/3.0/). Any
> >> content sourced must be attributed back to the OBIEE EMG with a link to the
> >> Google Group (http://groups.google.com/group/obiee-enterprise-methodology
> >> ).
>
> >  --
> > You received this message because you are subscribed to the Google
> > Groups "OBIEE Enterprise Methodology Group" group.
> > To post to this group, send email to
> > obiee-enterpri...@googlegroups.com
> > To unsubscribe from this group, send email to

> > obiee-enterprise-met...@googlegroups.com<obiee-enterprise-methodology%2Bunsu...@googlegroups.com>

> > For more options, visit this group at
> >http://groups.google.com/group/obiee-enterprise-methodology?hl=en
>
> > All content to the OBIEE EMG lies under the Creative Commons Attribution
> > 3.0 Unported License (http://creativecommons.org/licenses/by/3.0/). Any
> > content sourced must be attributed back to the OBIEE EMG with a link to the
> > Google Group (http://groups.google.com/group/obiee-enterprise-methodology
> > ).
>

> --
> Adrian Ward
> Principal Consultant
>
> Rittman Mead Consulting
>
> UKOUG Business Intelligence Partner of the Year 2008/2009
>
> Mobile:+44 (0) 7545 922 344
> Fax:    +44 (0) 1273 784 960

> Email: adrian.w...@rittmanmead.com

[Jeff McQuigg]

Ok, then force it via another technique - use security group filters
on either a fact field or another dimension. somethin along the lines
of:

On Dim Table A, filter Facts.Force-A-Table-LTS > 0
or
On Dim Table A, filter Dim B.Field <> 'XXXXXXX'

This is done only in the prompts subject area, so it won't interfere
with other queries.

Jeff M.

On Jan 5, 10:46 am, Robert Tooker <robert.too...@gmail.com> wrote:
> Jeff, implicit facts are not relevant when only selecting from one
> dimension. From the documentation:
>
> "If you set an implicit fact column this column will be added to a query
> when it contains columns from
> two or more dimension tables and no measures. The column is not visible in
> the results. It is used
> to specify a default join path between dimension tables when there are
> several possible alternatives."
>
> Regards,
>
> Robert
>

> > > > <obiee-enterprise-methodology%2Bunsu...@googlegroups.com<obiee-enterprise-methodology%252Buns...@googlegroups.com>
> > <obiee-enterprise-methodology%252Buns...@googlegroups.com<obiee-enterprise-methodology%25252Bun...@googlegroups.com>

>
> > > > > >> For more options, visit this group at
> > > > > >>http://groups.google.com/group/obiee-enterprise-methodology?hl=en
>
> > > > > >> All content to the OBIEE EMG lies under the Creative Commons
> > > > Attribution
> > > > > >> 3.0 Unported License (http://creativecommons.org/licenses/by/3.0/
> > ).
> > > > Any
> > > > > >> content sourced must be attributed back to the OBIEE EMG with a
> > link
> > > > to the
> > > > > >> Google Group (
> > > >http://groups.google.com/group/obiee-enterprise-methodology
> > > > > >> ).
>
> > > > > >  --
> > > > > > You received this message because you are subscribed to the Google
> > > > > > Groups "OBIEE Enterprise Methodology Group" group.
> > > > > > To post to this group, send email to
> > > > > > obiee-enterpri...@googlegroups.com
> > > > > > To unsubscribe from this group, send email to
> > > > > > obiee-enterprise-met...@googlegroups.com<obiee-enterprise-methodology%2Bunsu...@googlegroups.com>
> > <obiee-enterprise-methodology%2Bunsu...@googlegroups.com<obiee-enterprise-methodology%252Buns...@googlegroups.com>
>

> > > > <obiee-enterprise-methodology%2Bunsu...@googlegroups.com<obiee-enterprise-methodology%252Buns...@googlegroups.com>
> > <obiee-enterprise-methodology%252Buns...@googlegroups.com<obiee-enterprise-methodology%25252Bun...@googlegroups.com>

[Mark Rittman]

There's a new feature in 11g called "Logical Table Source Priority Ordering", which allows you to assign priority numbers to each LTS. The higher the priority number, the less chance it'll be used in a query. So for a set of given LTS's, you can explicitly tell the BI Server to use one over the other with this feature. It's detailed in the documentation here:

http://download.oracle.com/docs/cd/E14571_01/bi.1111/e10540/lts.htm#CIHFACJA

If we're looking to force the choice of LTS, and if we're using 11g (a big "if", I know), then this may be worth looking at.

Mark

All content to the OBIEE EMG lies under the Creative Commons Attribution 3.0 Unported License (http://creativecommons.org/licenses/by/3.0/).  Any content sourced must be attributed back to the OBIEE EMG with a link to the Google Group (http://groups.google.com/group/obiee-enterprise-methodology).

[Robert Tooker]

Thanks everyone for your help. I'll keep Jeff's work around as a last resort - it would mean users would have to work differently with a prompt SA and it will cause issues with constrained prompts across multiple dimensions which should be using the implicit fact of a particular SA.

I've already tried faking an extra level of detail on both LTS "A" and LTS "B" in turn but it doesn't change the selection. I read somewhere that all things being equal it's the lowest internal ID that gets selected (just a comment in a forum, may be true or not).

I also found this comment in support log 795347.1:

"The server does not support multiple sources at the same level for a measure. They can defined in the metadata, but the server will choose only one."

I would think this also applies to dimensions.

This and the fact that obiee 11g brings this functionality in a clear and defined way leads me to believe it's recognised as a deficiency of 10g and so we'll just have to wear it until such time that we can upgrade.

Thanks again,

Robert

[Palaniappan Chidambaram]

I have worked in the obiee 11g dev team in the past and we did
recommend for LTS ordering .
In fact we use to do the dirty trick what Jeff mentioned. If you have
two LTS (A&B) all being same, we add a column say LTS_Indicator and
assign 'A' for LTS A and 'B' for LTS B.
Add the fragmentation content(since all the levels being same) and
apply the security filter and create two diff presentation folder ..so
the users know if am querying from PF A it goes to LTS A and vice.

in your case, you dont have to create security filter, but mention the
fragmentation content and in the prompt ensure its based on Logical
SQL which has the appropriate filter.
On the constraint part , you have to chk.

Now with OBIEE 11g LTS ordering exist (its more for to switch between
real-time LTS source and DWH Sources).
Dimension Browse feature in 11g doesn't use any fact table to query

Thanks
Palani

On Jan 6, 5:37 pm, Robert Tooker <robert.too...@gmail.com> wrote:
> Thanks everyone for your help. I'll keep Jeff's work around as a last resort
> - it would mean users would have to work differently with a prompt SA and it
> will cause issues with constrained prompts across multiple dimensions which
> should be using the implicit fact of a particular SA.
>
> I've already tried faking an extra level of detail on both LTS "A" and LTS
> "B" in turn but it doesn't change the selection. I read somewhere that all
> things being equal it's the lowest internal ID that gets selected (just a
> comment in a forum, may be true or not).
>
> I also found this comment in support log 795347.1:
>
> "The server does not support multiple sources at the same level for a
> measure. They can defined in the metadata, but the server will choose only
> one."
>
> I would think this also applies to dimensions.
>
> This and the fact that obiee 11g brings this functionality in a clear and
> defined way leads me to believe it's recognised as a deficiency of 10g and
> so we'll just have to wear it until such time that we can upgrade.
>
> Thanks again,
>
> Robert
>
> On Thu, Jan 6, 2011 at 7:26 AM, Mark Rittman

> <mark.ritt...@rittmanmead.com>wrote:

>
> > There's a new feature in 11g called "Logical Table Source Priority
> > Ordering", which allows you to assign priority numbers to each LTS. The
> > higher the priority number, the less chance it'll be used in a query. So for
> > a set of given LTS's, you can explicitly tell the BI Server to use one over
> > the other with this feature. It's detailed in the documentation here:
>

> >http://download.oracle.com/docs/cd/E14571_01/bi.1111/e10540/lts.htm#C...

>
> > If we're looking to force the choice of LTS, and if we're using 11g (a big
> > "if", I know), then this may be worth looking at.
>
> > Mark
>

> >> > > > > <obiee-enterprise-methodology%2Bunsu...@googlegroups.com<obiee-enterprise-methodology%252Buns...@googlegroups.com>
> >> <obiee-enterprise-methodology%252Buns...@googlegroups.com<obiee-enterprise-methodology%25252Bun...@googlegroups.com>
>
> >> > > <obiee-enterprise-methodology%252Buns...@googlegroups.com<obiee-enterprise-methodology%25252Bun...@googlegroups.com>
> >> <obiee-enterprise-methodology%25252Bun...@googlegroups.com<obiee-enterprise-methodology%2525252Bu...@googlegroups.com>

> ...
>
> read more »

[Jeff McQuigg]

One more idea that may in fact be the safest and easiest: (I love this
topic!)

Build a separate logical dimension called "Dim A - Prompts Only" using
your table that you want - with only that table as a source. Link it
in as normal - set up a hierarchy, the content tabs (including the a
fact LTS) and business model as if it were a new dimension. Add it to
a prompts subject area and rename the presentation table to be the
same as the dimension you will ultimately use. As prompt work based
on table.name, you can force your prompts to the correct, specialized
prompts-only table and they will pass and work with your real
dimension table.

Jeff M.

On Jan 6, 1:37 am, Robert Tooker <robert.too...@gmail.com> wrote:
> Thanks everyone for your help. I'll keep Jeff's work around as a last resort
> - it would mean users would have to work differently with a prompt SA and it
> will cause issues with constrained prompts across multiple dimensions which
> should be using the implicit fact of a particular SA.
>
> I've already tried faking an extra level of detail on both LTS "A" and LTS
> "B" in turn but it doesn't change the selection. I read somewhere that all
> things being equal it's the lowest internal ID that gets selected (just a
> comment in a forum, may be true or not).
>
> I also found this comment in support log 795347.1:
>
> "The server does not support multiple sources at the same level for a
> measure. They can defined in the metadata, but the server will choose only
> one."
>
> I would think this also applies to dimensions.
>
> This and the fact that obiee 11g brings this functionality in a clear and
> defined way leads me to believe it's recognised as a deficiency of 10g and
> so we'll just have to wear it until such time that we can upgrade.
>
> Thanks again,
>
> Robert
>
> On Thu, Jan 6, 2011 at 7:26 AM, Mark Rittman

> <mark.ritt...@rittmanmead.com>wrote:

>
> > There's a new feature in 11g called "Logical Table Source Priority
> > Ordering", which allows you to assign priority numbers to each LTS. The
> > higher the priority number, the less chance it'll be used in a query. So for
> > a set of given LTS's, you can explicitly tell the BI Server to use one over
> > the other with this feature. It's detailed in the documentation here:
>

> >http://download.oracle.com/docs/cd/E14571_01/bi.1111/e10540/lts.htm#C...

>
> > If we're looking to force the choice of LTS, and if we're using 11g (a big
> > "if", I know), then this may be worth looking at.
>
> > Mark
>

> >> > > > > <obiee-enterprise-methodology%2Bunsu...@googlegroups.com<obiee-enterprise-methodology%252Buns...@googlegroups.com>
> >> <obiee-enterprise-methodology%252Buns...@googlegroups.com<obiee-enterprise-methodology%25252Bun...@googlegroups.com>
>
> >> > > <obiee-enterprise-methodology%252Buns...@googlegroups.com<obiee-enterprise-methodology%25252Bun...@googlegroups.com>
> >> <obiee-enterprise-methodology%25252Bun...@googlegroups.com<obiee-enterprise-methodology%2525252Bu...@googlegroups.com>
>

> ...
>
> read more »