en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Rasmus Remmer Nielsen  
#1 Posted : Monday, June 13, 2016 10:55:01 AM(UTC)
Rasmus Remmer Nielsen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 6/14/2011(UTC)
Posts: 22

Using Targit 2k14 build 6624 I am facing this problem.

I have a cube using two measure groups from two separate DWH fact tables. First measure group contains invoiced lines and second measure group contains ordered lines. On all lines in both groups there is a field called "TRANSSTATUS" which can contain a 0 for invoiced or a 1 for ordered. This field serves as a foreign key for a dimension "Sales transaction status".

This works well when using the dimension in the grid (crosstab, table, chart), however, when I put the dimension as a criterion and open it for selection, it shows only one member "Invoiced".

I have unticked the "Filter criteria" from both options in the user settings, but the problem persists.

If I connect to the cube from Excel and create a pivot table and use the dimension as a filter, it works without incident.

What do I do to make sure that I can select "Ordered" from the dimension when used as a criterion?

[img=http://imgur.com/PGi7D03]Missing dimension members[/img]

Edited by user Monday, June 13, 2016 11:36:41 AM(UTC)  | Reason: Not specified

Páll Bjarkason  
#2 Posted : Tuesday, June 14, 2016 9:23:39 AM(UTC)
Páll Bjarkason
Rank: Advanced Member

Groups: extranet\Forum
Joined: 1/13/2012(UTC)
Posts: 164

Have you tried to uncheck the “Cache criteria requests on server” option in ”Additional settings” of the cube connection in TARGIT Management?
Best regards

Páll, CTS
Rasmus Remmer Nielsen  
#3 Posted : Tuesday, June 14, 2016 11:19:02 AM(UTC)
Rasmus Remmer Nielsen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 6/14/2011(UTC)
Posts: 22

Hi Páll,

Thanks for responding. I just tried it but with the same result as before.

/Raasmus
Páll Bjarkason  
#4 Posted : Tuesday, June 14, 2016 2:38:12 PM(UTC)
Páll Bjarkason
Rank: Advanced Member

Groups: extranet\Forum
Joined: 1/13/2012(UTC)
Posts: 164

Have you seen this post and the related posts?
https://www.targit.com/e...=posts&t=281#post711
Best regards

Páll, CTS
Rasmus Remmer Nielsen  
#5 Posted : Tuesday, June 14, 2016 7:15:27 PM(UTC)
Rasmus Remmer Nielsen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 6/14/2011(UTC)
Posts: 22

Thanks for the help. I set the "Non empty criteria" (my own translation from Danish) to "No" and now it returns the total dimension member list.

However, I still see this as a bug as the cube clearly has both members in it from the get-go.
Páll Bjarkason  
#6 Posted : Wednesday, June 15, 2016 9:32:41 AM(UTC)
Páll Bjarkason
Rank: Advanced Member

Groups: extranet\Forum
Joined: 1/13/2012(UTC)
Posts: 164

NP, you are welcome

Well it is probably related to the fact that cero and NULL, du to optimizing, in some extent, are treated the same.
If you are curious, you could use 1 and 2, instead of 0 and 1, to check if the default TARGIT criterion behavior is as changes.

Plz share if you go digging :)

Edited by user Wednesday, June 15, 2016 9:34:08 AM(UTC)  | Reason: Not specified

Best regards

Páll, CTS
Rasmus Remmer Nielsen  
#7 Posted : Wednesday, June 15, 2016 9:57:14 AM(UTC)
Rasmus Remmer Nielsen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 6/14/2011(UTC)
Posts: 22

I don't think zero/NULL have anything to do with it. When trying several times I saw that sometimes it was the dimension member with the zero/0 key that was shown and other times it was the dimension member with one/1 showing. I think it has more to do with TargIT querying only one measure group to find the dimension members available for the list.
Páll Bjarkason  
#8 Posted : Friday, June 17, 2016 9:25:10 AM(UTC)
Páll Bjarkason
Rank: Advanced Member

Groups: extranet\Forum
Joined: 1/13/2012(UTC)
Posts: 164

Using the SQL Server Profiler, you can follow the activity of a Microsoft SQL Server Analysis Services (SSAS) instance by capturing and then analyzing the trace events.

SQL Server Profiler can capture not only MDX queries fired from any OLAP application e.g. TARGIT, but also XMLA commands sent from applications such as BIDS.

In EventSubclass id “0 – MDXQuery” you can see the MDX in the TextData kolumn, and figure out whether TARGIT is querying only one measure group to find the dimension members available for the list.
Best regards

Páll, CTS
Rasmus Remmer Nielsen  
#9 Posted : Friday, June 17, 2016 9:49:34 AM(UTC)
Rasmus Remmer Nielsen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 6/14/2011(UTC)
Posts: 22

Again, thanks for helping.

This is the MDX for the Query that returns all members
Code:
SELECT [Sales transaction status].[Transaction status].AllMembers DIMENSION PROPERTIES PARENT_UNIQUE_NAME,MEMBER_TYPE,MEMBER_KEY ON 0, {} ON 1 FROM [CustomerSales]


This is the MDX for the Query that returns only members from one measure Group from the [CustomerSales] cube.
Code:
SELECT NON EMPTY [Sales transaction status].[Transaction status].AllMembers DIMENSION PROPERTIES PARENT_UNIQUE_NAME,MEMBER_TYPE,MEMBER_KEY ON 0 FROM [CustomerSales]


I'm not an MDX Expert, but if I remove the "NON EMPTY" clause from the second MDX statement, it returns only the data from one fact table of the cube with values, and the other shows a [null] value - even though plenty of transactions reside in both fact tables.

Colour me confused :)
Rasmus Remmer Nielsen  
#10 Posted : Tuesday, June 21, 2016 1:20:38 PM(UTC)
Rasmus Remmer Nielsen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 6/14/2011(UTC)
Posts: 22

Some more useful information.

The cube consists of two hidden measures (one from each fact table) and a calculated measure that is defined as "measure1"+"measure2".

As long as the two measures from each fact table remains hidden in the cube, TargIT does not Query both to find the unioned dimension members.

If I unhide the two standard measures in the cube, TargIT will Query both measures and return a unioned list of dimension members.
Páll Bjarkason  
#11 Posted : Tuesday, June 21, 2016 1:33:26 PM(UTC)
Páll Bjarkason
Rank: Advanced Member

Groups: extranet\Forum
Joined: 1/13/2012(UTC)
Posts: 164

I still suspect SSAS to over-optimize 0 (cero).
If you are fiddling try to change your MDX calculation to re-define the calculated measure as
1+"measure1"+"measure2".
Thus you never get 0 (cero)
Best regards

Páll, CTS
Users browsing this topic
Anonymous
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Notification

Icon
Error