[prev in list] [next in list] [prev in thread] [next in thread]
List: mondrian
Subject: Re: [Mondrian] non empty cross join missing data
From: Pedro Alves <pmgalves () gmail ! com>
Date: 2016-05-02 18:28:45
Message-ID: CAC_LyUiLzjehoSiHPd+if2PMT0rCjVVdc90qfhQ_eUYAvSWCSw () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/related)]
[Attachment #4 (multipart/alternative)]
Thanks man!
On May 2, 2016 18:10, "Adam Brusselback" <adambrusselback@gmail.com> wrote:
> Done: http://jira.pentaho.com/browse/MONDRIAN-2487
>
> On Mon, May 2, 2016 at 1:03 PM, Pedro Alves <pmgalves@gmail.com> wrote:
>
>> That's what we use for triage. Can you create it and link the pull
>> request?
>> On May 2, 2016 18:00, "Adam Brusselback" <adambrusselback@gmail.com>
>> wrote:
>>
>>> I did not create one, and didn't see one in searching for the problem,
>>> so i'd wager "no".
>>>
>>> On Mon, May 2, 2016 at 12:12 PM, Pedro Alves <pmgalves@gmail.com> wrote:
>>>
>>>> Is there a jira on this?
>>>> On May 2, 2016 17:09, "Adam Brusselback" <adambrusselback@gmail.com>
>>>> wrote:
>>>>
>>>>> Alright, since I got no feedback, I fixed it how I thought best, and
>>>>> have tested it for the past week or so. No issues found so far. I submitted
>>>>> it back to the fork that Saiku keeps of Mondrian, but I thought i'd link to
>>>>> it here so you guys can get it fixed upstream.
>>>>>
>>>>> Here is the pull request:
>>>>>
>>>>> https://github.com/OSBI/mondrian/commit/3744676b7a3388d99825f427cb7e3b530ee86cbe
>>>>>
>>>>> On Tue, Apr 26, 2016 at 2:28 PM, Adam Brusselback <
>>>>> adambrusselback@gmail.com> wrote:
>>>>>
>>>>>> So, sorry. I was a bit tired when I sent this last night. I should
>>>>>> add a bit more info so it's possible to look into.
>>>>>>
>>>>>> Here is the mdx for the first picture:
>>>>>>
>>>>>> WITH
>>>>>> SET [~COLUMNS] AS
>>>>>> {[Time].[Fiscal].[Fiscal Year].Members}
>>>>>> SET [~ROWS] AS
>>>>>> {[Direct-Indirect].[Direct-Indirect].[Direct-Indirect].Members}
>>>>>> SELECT
>>>>>> CrossJoin([~COLUMNS], {[Measures].[Net Sales]}) ON COLUMNS,
>>>>>> NON EMPTY [~ROWS] ON ROWS
>>>>>> FROM [Pinnacle - Sale]
>>>>>>
>>>>>>
>>>>>> mdx for the second picture:
>>>>>>
>>>>>> WITH
>>>>>> SET [~COLUMNS] AS
>>>>>> {[Time].[Fiscal].[Fiscal Year].Members}
>>>>>> SET [~ROWS] AS
>>>>>> {[Direct-Indirect].[Direct-Indirect].[Direct-Indirect].Members}
>>>>>> SELECT
>>>>>> NON EMPTY CrossJoin([~COLUMNS], {[Measures].[Net Sales]}) ON COLUMNS,
>>>>>> NON EMPTY [~ROWS] ON ROWS
>>>>>> FROM [Pinnacle - Sale]
>>>>>>
>>>>>>
>>>>>> mdx for the third picture:
>>>>>>
>>>>>> WITH
>>>>>> SET [~COLUMNS] AS
>>>>>> {[Direct-Indirect].[Direct-Indirect].[Direct-Indirect].Members}
>>>>>> SET [~ROWS] AS
>>>>>> {[Time].[Fiscal].[Fiscal Year].Members}
>>>>>> SELECT
>>>>>> NON EMPTY CrossJoin([~COLUMNS], {[Measures].[Net Sales]}) ON COLUMNS,
>>>>>> NON EMPTY [~ROWS] ON ROWS
>>>>>> FROM [Pinnacle - Sale]
>>>>>>
>>>>>>
>>>>>>
>>>>>> I have been trying to debug this myself all today, and made a little
>>>>>> progress...
>>>>>>
>>>>>> My issue lies in the RolapMemberBase equals overrride.
>>>>>>
>>>>>> It doesn't take into account different keys for the same member. So
>>>>>> I have a [Time].[Fiscal].[2015] with a key of (1, 2015), and when it goes
>>>>>> through the RolapEvaluator.same method (called in setContext) it sees them
>>>>>> as the same thing and doesn't do a replace in the currentMember list when
>>>>>> my next member with a key of (2, 2015) comes in.
>>>>>>
>>>>>> So I am not sure where the fix really needs to go for this, if it's
>>>>>> in the RolapMemberBase.equals method (don't know how far reaching that
>>>>>> could be), or in the RolapEvaluator.same method. I could use some
>>>>>> direction on this, as I do need it fixed for my own use, but would much
>>>>>> rather do it in a way that could be submitted back to the project.
>>>>>>
>>>>>> On Tue, Apr 26, 2016 at 3:33 AM, Adam Brusselback <
>>>>>> adambrusselback@gmail.com> wrote:
>>>>>>
>>>>>>> Hey all, I seem to have found a bug in the non empty cross join
>>>>>>> optimization.
>>>>>>> Mondrian version: 4.3.0.1
>>>>>>>
>>>>>>> I have a data warehouse that is multi-tenant, and has a date
>>>>>>> dimension with different fiscal information per client (all tables
>>>>>>> segregated by a client_sk). Each client has their own fiscal calendar, so
>>>>>>> it's necessary to separate them out like that.
>>>>>>>
>>>>>>> The issue seems to be, when doing the cross join non empty with my
>>>>>>> fiscal year as a column, if the first result that comes back for that
>>>>>>> member is empty, even though it is for a totally different client, it will
>>>>>>> throw out that whole member.
>>>>>>>
>>>>>>> If the fiscal year is in the row, and another member in the column,
>>>>>>> the query returns as expected.
>>>>>>>
>>>>>>> I'll attach a few example query images:
>>>>>>> Example of why it seems to be removing the members (not first in the
>>>>>>> list):
>>>>>>> [image: Inline image 5]
>>>>>>>
>>>>>>> Here is my problem query:
>>>>>>> [image: Inline image 2]
>>>>>>>
>>>>>>> Here it is with the column/row flipped:
>>>>>>> [image: Inline image 4]
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> Mondrian mailing list
>>>>> Mondrian@pentaho.org
>>>>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>>>>
>>>>>
>>>> _______________________________________________
>>>> Mondrian mailing list
>>>> Mondrian@pentaho.org
>>>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>>>
>>>>
>>>
>>> _______________________________________________
>>> Mondrian mailing list
>>> Mondrian@pentaho.org
>>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>>
>>>
>> _______________________________________________
>> Mondrian mailing list
>> Mondrian@pentaho.org
>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>
>>
>
> _______________________________________________
> Mondrian mailing list
> Mondrian@pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>
[Attachment #7 (text/html)]
<p dir="ltr">Thanks man!</p>
<div class="gmail_quote">On May 2, 2016 18:10, "Adam Brusselback" <<a \
href="mailto:adambrusselback@gmail.com">adambrusselback@gmail.com</a>> wrote:<br \
type="attribution"><blockquote class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Done: <a \
href="http://jira.pentaho.com/browse/MONDRIAN-2487" \
target="_blank">http://jira.pentaho.com/browse/MONDRIAN-2487</a></div><div \
class="gmail_extra"><br><div class="gmail_quote">On Mon, May 2, 2016 at 1:03 PM, \
Pedro Alves <span dir="ltr"><<a href="mailto:pmgalves@gmail.com" \
target="_blank">pmgalves@gmail.com</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><p dir="ltr">That's what we use for triage. Can you \
create it and link the pull request?</p><div><div> <div class="gmail_quote">On May 2, \
2016 18:00, "Adam Brusselback" <<a \
href="mailto:adambrusselback@gmail.com" \
target="_blank">adambrusselback@gmail.com</a>> wrote:<br \
type="attribution"><blockquote class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">I did not create \
one, and didn't see one in searching for the problem, so i'd wager \
"no".</div><div class="gmail_extra"><br><div class="gmail_quote">On Mon, \
May 2, 2016 at 12:12 PM, Pedro Alves <span dir="ltr"><<a \
href="mailto:pmgalves@gmail.com" target="_blank">pmgalves@gmail.com</a>></span> \
wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px \
#ccc solid;padding-left:1ex"><p dir="ltr">Is there a jira on this?</p> <div \
class="gmail_quote"><div><div>On May 2, 2016 17:09, "Adam Brusselback" \
<<a href="mailto:adambrusselback@gmail.com" \
target="_blank">adambrusselback@gmail.com</a>> wrote:<br \
type="attribution"></div></div><blockquote class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div><div dir="ltr">Alright, \
since I got no feedback, I fixed it how I thought best, and have tested it for the \
past week or so. No issues found so far. I submitted it back to the fork that Saiku \
keeps of Mondrian, but I thought i'd link to it here so you guys can get it fixed \
upstream.<div><br></div><div>Here is the pull request:</div><div><a \
href="https://github.com/OSBI/mondrian/commit/3744676b7a3388d99825f427cb7e3b530ee86cbe" \
target="_blank">https://github.com/OSBI/mondrian/commit/3744676b7a3388d99825f427cb7e3b530ee86cbe</a><br></div></div><div \
class="gmail_extra"><br><div class="gmail_quote">On Tue, Apr 26, 2016 at 2:28 PM, \
Adam Brusselback <span dir="ltr"><<a href="mailto:adambrusselback@gmail.com" \
target="_blank">adambrusselback@gmail.com</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div dir="ltr">So, sorry. I was a bit tired when I sent this \
last night. I should add a bit more info so it's possible to look \
into.<div><br></div><div>Here is the mdx for the first picture:</div><blockquote \
style="margin:0px 0px 0px \
40px;border:none;padding:0px"><div><div>WITH</div></div><div><div>SET [~COLUMNS] \
AS</div></div><div><div> {[Time].[Fiscal].[Fiscal \
Year].Members}</div></div><div><div>SET [~ROWS] AS</div></div><div><div> \
{[Direct-Indirect].[Direct-Indirect].[Direct-Indirect].Members}</div></div><div><div>SELECT</div></div><div><div>CrossJoin([~COLUMNS], \
{[Measures].[Net Sales]}) ON COLUMNS,</div></div><div><div>NON EMPTY [~ROWS] ON \
ROWS</div></div><div><div>FROM [Pinnacle - \
Sale]</div></div></blockquote><div><br></div><div>mdx for the second \
picture:</div><blockquote style="margin:0px 0px 0px \
40px;border:none;padding:0px"><div><div>WITH</div></div><div><div>SET [~COLUMNS] \
AS</div></div><div><div> {[Time].[Fiscal].[Fiscal \
Year].Members}</div></div><div><div>SET [~ROWS] AS</div></div><div><div> \
{[Direct-Indirect].[Direct-Indirect].[Direct-Indirect].Members}</div></div><div><div>SELECT</div></div><div><div>NON \
EMPTY CrossJoin([~COLUMNS], {[Measures].[Net Sales]}) ON \
COLUMNS,</div></div><div><div>NON EMPTY [~ROWS] ON ROWS</div></div><div><div>FROM \
[Pinnacle - Sale]</div></div></blockquote><div><br></div><div>mdx for the third \
picture:</div><blockquote style="margin:0px 0px 0px \
40px;border:none;padding:0px"><div><div>WITH</div></div><div><div>SET [~COLUMNS] \
AS</div></div><div><div> \
{[Direct-Indirect].[Direct-Indirect].[Direct-Indirect].Members}</div></div><div><div>SET \
[~ROWS] AS</div></div><div><div> {[Time].[Fiscal].[Fiscal \
Year].Members}</div></div><div><div>SELECT</div></div><div><div>NON EMPTY \
CrossJoin([~COLUMNS], {[Measures].[Net Sales]}) ON COLUMNS,</div></div><div><div>NON \
EMPTY [~ROWS] ON ROWS</div></div><div><div>FROM [Pinnacle - \
Sale]</div></div></blockquote><div><br></div><div><br></div><div>I have been trying \
to debug this myself all today, and made a little \
progress...</div><div><br></div><div>My issue lies in the RolapMemberBase equals \
overrride.</div><div><br></div><div>It doesn't take into account different keys \
for the same member. So I have a [Time].[Fiscal].[2015] with a key of (1, 2015), \
and when it goes through the RolapEvaluator.same method (called in setContext) it \
sees them as the same thing and doesn't do a replace in the currentMember list \
when my next member with a key of (2, 2015) comes in.<br></div><div><br></div><div>So \
I am not sure where the fix really needs to go for this, if it's in the \
RolapMemberBase.equals method (don't know how far reaching that could be), or in \
the RolapEvaluator.same method. I could use some direction on this, as I do need it \
fixed for my own use, but would much rather do it in a way that could be submitted \
back to the project.</div></div><div><div><div class="gmail_extra"><br><div \
class="gmail_quote">On Tue, Apr 26, 2016 at 3:33 AM, Adam Brusselback <span \
dir="ltr"><<a href="mailto:adambrusselback@gmail.com" \
target="_blank">adambrusselback@gmail.com</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div dir="ltr">Hey all, I seem to have found a bug in the non \
empty cross join optimization.<div>Mondrian version: \
4.3.0.1</div><div><div><br></div><div>I have a data warehouse that is multi-tenant, \
and has a date dimension with different fiscal information per client (all tables \
segregated by a client_sk). Each client has their own fiscal calendar, so it's \
necessary to separate them out like that.</div><div><br></div><div>The issue seems to \
be, when doing the cross join non empty with my fiscal year as a column, if the \
first result that comes back for that member is empty, even though it is for a \
totally different client, it will throw out that whole \
member.</div><div><br></div><div>If the fiscal year is in the row, and another member \
in the column, the query returns as expected.</div><div><br></div><div>I'll \
attach a few example query images:</div><div>Example of why it seems to be removing \
the members (not first in the list):</div><div><img src="cid:ii_154517b1899776f2" \
alt="Inline image 5" style="margin-right:0px" width="1162" \
height="365"><br></div><div><div \
style="display:inline-block"><br></div></div><div>Here is my problem \
query:</div><div><img src="cid:ii_15451789f6b5f492" alt="Inline image 2" width="444" \
height="472"><br></div><div><br></div><div>Here it is with the column/row \
flipped:</div><div><img src="cid:ii_15451796ef6c1b66" alt="Inline image 4" \
width="455" height="475"><br></div></div></div> </blockquote></div><br></div>
</div></div></blockquote></div><br></div>
<br></div></div>_______________________________________________<br>
Mondrian mailing list<br>
<a href="mailto:Mondrian@pentaho.org" target="_blank">Mondrian@pentaho.org</a><br>
<a href="http://lists.pentaho.org/mailman/listinfo/mondrian" rel="noreferrer" \
target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br> \
<br></blockquote></div> <br>_______________________________________________<br>
Mondrian mailing list<br>
<a href="mailto:Mondrian@pentaho.org" target="_blank">Mondrian@pentaho.org</a><br>
<a href="http://lists.pentaho.org/mailman/listinfo/mondrian" rel="noreferrer" \
target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br> \
<br></blockquote></div><br></div> \
<br>_______________________________________________<br> Mondrian mailing list<br>
<a href="mailto:Mondrian@pentaho.org" target="_blank">Mondrian@pentaho.org</a><br>
<a href="http://lists.pentaho.org/mailman/listinfo/mondrian" rel="noreferrer" \
target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br> \
<br></blockquote></div> \
</div></div><br>_______________________________________________<br> Mondrian mailing \
list<br> <a href="mailto:Mondrian@pentaho.org" \
target="_blank">Mondrian@pentaho.org</a><br> <a \
href="http://lists.pentaho.org/mailman/listinfo/mondrian" rel="noreferrer" \
target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br> \
<br></blockquote></div><br></div> \
<br>_______________________________________________<br> Mondrian mailing list<br>
<a href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</a><br>
<a href="http://lists.pentaho.org/mailman/listinfo/mondrian" rel="noreferrer" \
target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br> \
<br></blockquote></div>
["image.png" (image/png)]
["image.png" (image/png)]
["image.png" (image/png)]
_______________________________________________
Mondrian mailing list
Mondrian@pentaho.org
http://lists.pentaho.org/mailman/listinfo/mondrian
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic