Hi,
I am having an issue with Multiple Fact Tables..
I have 2 fact tables with some common dimensions and some independent to each fact table.
When I execute a mdx query which fetches data only from the 1st fact table, for some reason it look’s to the second fact table and display the results accordingly.
For Example
Record-Id Fact table 1 Fact table 2
Measure -[Cut In] Measure -[Zro Cut In]
1 1 -
2 12 10
3 10 5
4 2 -
5 20 -
6 0 -
7 Null -
8 0 -
If I execute Query 1; I get record-Id 2 & 3 only ( I use Nonempty function), but when I execute query 2 (Without Nonempty) , I get all the 8 record-Id’s. Any idea what it could be and why it’s dependent on measure from another fact table which is not being used in the query ?
Query 1
SELECT
{
[Measures].[Cut In]
} ON COLUMNS,
{
ORDER(
nonempty([Dim Product].[Principal Id].children)
,[Measures].[Cut In],
desc
)
} ON ROWS
FROM [SIFDW]
WHERE (
{ [Dim Period].[Period].&[200601] },
{ [Dim Period].[Period Type].&[M] },
{ [Dim Store].[organization name].&[CORERP]},
{ [Dim Product].[Team ID].&[1]}
)
Query 2
SELECT
{
[Measures].[Cut In]
} ON COLUMNS,
{
ORDER (
[Dim Product].[Principal Id].children
,[Measures].[Cut In],
desc
)
} ON ROWS
FROM [SIFDW]
WHERE (
{ [Dim Period].[Period].&[200601] },
{ [Dim Period].[Period Type].&[M] },
{ [Dim Store].[organization name].&[CORERP]},
{ [Dim Product].[Team ID].&[1]}
)
Thank you,
Manish
ManishNShah wrote:
If I execute Query 1; I get record-Id 2 & 3 only ( I use Nonempty function), but when I execute query 2 (Without Nonempty) , I get all the 8 record-Id’s. Any idea what it could be and why it’s dependent on measure from another fact table which is not being used in the query ?
Since you didn't explictly specify a measure for the NonEmpty(), it may have defaulted to a measure in the other measure, group, so you could try adding the measure in Query 1:
Code Snippet
SELECT
{
[Measures].[Cut In]
} ON COLUMNS,
{
ORDER(
nonempty([Dim Product].[Principal Id].children,
{[Measures].[Cut In]})
,[Measures].[Cut In],
desc
)
} ON ROWS
FROM [SIFDW]
WHERE (
{ [Dim Period].[Period].&[200601] },
{ [Dim Period].[Period Type].&[M] },
{ [Dim Store].[organization name].&[CORERP]},
{ [Dim Product].[Team ID].&[1]}
|||Hi Deepak,
Thank you for your quick response, but still have couple of issues with it.
If a cube has multiple measure groups, and each group has few measures; but the mdx query is just using measures from one measure group, they why it's looking for measures in other measure group which is not mentioned in the MDX Query?
Also, let's say we have 2 measure group (Each measure group has 1 measure in it), sometime it's empty in first and at time it's empty in 2nd, how can we mimic Left outer join, right outer join and Inner join considering 2 measure groups as 2 tables?
Thank you,
Manish
|||
but still have couple of issues with it.
But did it work - that would be useful to know, as well?
but the mdx query is just using measures from one measure group, they why it's looking for measures in other measure group
The query applies to the cube, not just to a specific measure group. So if a measure is not specified, explicitly or by context, the default cube measure would be used, which could be from another measure group:
SQL Server 2005 Books Online
Key Concepts in MDX (MDX)
...
The default measure is the first measure specified in the cube, unless a default measure is explicitly defined. For more information, see Defining a Default Member and DefaultMember (MDX).
...
Also, let's say we have 2 measure group (Each measure group has 1 measure in it), sometime it's empty in first and at time it's empty in 2nd, how can we mimic Left outer join, right outer join and Inner join considering 2 measure groups as 2 tables?
You might want to start a separate thread for this - but keep in mind that extrapolating from SQL may not always be the best approach in MDX.
No comments:
Post a Comment