table ordering of MSTR's FROM clause 1

[138006]

hi all,
What is the logic followed by MSTR in generating a SQL specifically the From clause order.

Say I have 5 attributes from 5 tables. How will MSTR order these tables in the Where Clause? It seems it doesn't do so based on the order the attributes are chosen.

Is it possible to make the alias static for a table? i.e say i have a table Time. Can I make it mandatory that Time will always be the alias a11 in all MSTR reports??

There is an option in VLDB but that merely changes the order but keeps the alias same for that report SQL
Thanks

 

[JaC74]

1. What is the logic followed by MSTR in generating a SQL specifically the From clause order.

Here is the default order that MSTR uses for it's FROM clause:

Fact Tables
Metric Qualification (MQ) Tables
Relationship Tables
Lookup Tables

2. Is it possible to make the alias static for a table? i.e say i have a table Time. Can I make it mandatory that Time will always be the alias a11 in all MSTR reports??

No

 

[entaroadun]

Why does time need to be a11? It sounds like a misuse of pass-thru SQL...

 

[138006]

Hi,

Actually I want an attribute which is a decode from 2 different columns. But that is not supported by MSTR off the shelf.

What I tried then is Applysimple("decode(#0,-1, T1.C1, #1)" ,C2,C3)
where C2 and C3 are from Table T2.

As u see there are 2 diff tables T1 and T2.

Now since MSTR uses aliases in SQL. So may be the alias of T1 is a11.
If that is the case, then I cannot use T1.C1 in the sql, I have to use a11.C1.

Now if I can fix the table alias then I can define the attribute in that way. Otherwise I have ro create another similar attribute for another report where alias of t1 is a12 say.
Hope it clairifies

 

[Z3]

The fact table will always be a11, and MSI will never join two fact tables natively. Is T1 more like a fact table, or a lookup table?

 

[138006]

None of T1 and T2 are fact tables.
So if MSTR keeps a11 for a fact table what about the rest a values? Can in any way I can make the alias static for one table?

 

[Z3]

I don't think you can make the alias static -- others may correct me -- but it'd be a wonderful enhancement idea though. But if your column name is unique (you can make it unique by using a renaming- view), you can forget about the table aliases. Sorry I could not be more helpfully, but please do log the case with support and hopefully someday we will all get this.

 

[entaroadun]

How are T1 and T2 related? You need to use a subquery and we can write that.

Be careful with DECODE and CASE statements in attributes though. Most RDBMS's don't support DECODE or CASE in the GROUP BY clause. If this attribute form expression is for the ID attribute form and the attribute is the lowest level attribute in its hierarchy on your report, then MSTR will put the expression in the GROUP BY clause. This won't work.

 

[138006]

1) How will subquery work?

2)This is an ID expression but this attribute is not the lowest level. How is this important

 

[entaroadun]

1. MSTR doesn't support attribute form expressions or fact expressions using columns from different logical tables. Therefore, there are two ways around it; you need to create a view that pre-joins the two tables so the two columns are in the same table; or you can use a subquery to bring one column from one table into the other table. It depends on your data model which is better.

If you need more info on subqueries, look it up.

2. If in a report, for a given hierarchy, the lowest level attribute of that hierarchy in that report has a CASE or DECODE statement in its ID form, the SQL won't work. MSTR will put the ID form of that attribute in the GROUP BY clause, and most RDBMSs don't support CASE or DECODE statements in GROUP BY clauses.

 

[138006]

Hi the second option of subqueries -- how does it work? You wrote about correlated subqueries but that is not possible in the attribute definiton. Then where can I write this subquery??

Also, apart from Advance Filter option where i can use Applycomparison and pass through a SQL, how else can I write a subquery in MSTR?

Thanks,

 

[entaroadun]

Put the correlated subquery in the attribute form expression, and use ApplySimple the same way you have for DECODE.

What is the relationship you are trying to reflect?

 

[138006]

decode (t1.c1,-1,t2.c2,t3.c3)
or
decode (t1.c1,-1,t1.c2,t2.c3)
or
T1.C1 ||T2.C2

How can I write this??
Please elaborate where to write the subquery and where applysimple? How many attributes then?
You may remember I posted this and had tried it but it gave error.

I tired T1.C1||T2.C2
and for that i created 1 attribute as

Applysimple("#0||#1",T1.C1,(Select T2.C2 from T1 ))

But it gave errors
Please let me know the entire syntax.

 

[entaroadun]

How are T1 and T2 related? How do you join between them equally? Let us know and we can do one as an example.

 

[Z3]

I think you may want to make your thing a metric instead of an attribute, to avoid the group by problem as ent pointed out. With MS SQL, attributes most of the time are group-by-ed. You can easily make your thing an metric with applysimple, I believe.

If you do want to make it an attribute, then it's a special attribute. It'd work in some cases not in other. So just hard code it with the right table alias then. You may need one for each report.

 

[138006]

Hi
T1 and t2 are dim tables linked through the fact table.
T1.key1=Fact.key1
and t2.key2=Fact.key2
Thanks

 

[entaroadun]

I would bind the attribute form expression to the fact table instead:

ApplySimple(
  "(DECODE(
      (SELECT T2.C2 from T2 where T2.key2 = #1),
      -1,
      (SELECT T1.C1 from T1 where T1.key1 = #0),
      (SELECT T2.C3 from T2 where T2.key2 = #1)
   ))",
  Fact.key1,
  Fact.key2)

 

[138006]

Ok.
But then again u need to know for sure the alias names that MSTR generates for T1 and T2 for all reports that use this.

 

[138006]

2 questions regarding this:

1)again u need to know for sure the alias names that MSTR generates for T1 and T2 for all reports that use this

2)IS this the definition of an attribute? If yes then how will MSTR interpret a 'Selec' within a Select?

I think wit will generate something like:

Select
.....
decode(select T2.C2.............)

Will this nested select work?
Please enlighten
Thanks

 

[entaroadun]

Yes, the select will work. The point of the subquery is that you don't need the MSTR alias.

For example, let's say that T1 is in your FROM clause as a12. The subquery in the SELECT clause doesn't reference a12. You assign your own alias in the subquery that is independent. Notice that the subquery joins to the fact table independent of the FROM clause. This is a redundant join. The point of the subquery is so that the attribute will work even if T1 isn't in your FROM clause for any reason.

As for the SELECT within a SELECT, just use it. It will work. If you have any questions about subqueries, look it up.

Just try it...

ApplySimple(
  "(DECODE(
      (SELECT x.C2 from T2 x where x.key2 = #1),
      -1,
      (SELECT y.C1 from T1 y where y.key1 = #0),
      (SELECT z.C3 from T2 z where z.key2 = #1)
   ))",
  Fact.key1,
  Fact.key2)