KB483665: In Data Engine Version 11, CSI Changes for Subset Reports Run by Users with Security Filters

Community Admin

• Strategy

In MicroStrategy 2020 and previous versions, there are CSI changes after upgrading Data Engine Version to 11 (from 10) for Subset reports run by users with a security filter applied, but no data changes.

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.

Symptoms

Subset report returns correct data, but has a CSI mismatch after upgrading Data Engine Version to 11:

If using Strategy 10.11 or 11.0, Data Engine Version 11 is equivalent to turning on the Feature Flag "Defect fixes that may impact Data, SQL, MDX, CSI". This Feature Flag is deprecated in Strategy 2019 and later versions.

User running the Subset report has a security filter applied.

Glossary

CSI
Security Filter
Data Engine Version
Subset Report - Report that is created base on a Cube and contains a subset of the Cube attributes / metrics

Steps to Reproduce

Set the Data Engine Version as 10 (default setting) if it has been changed.

If using Strategy 10.11 or 11.0, turn off the Feature Flag "Defect fixes that may impact Data, SQL, MDX, CSI" (again, off by default).

Open Strategy Developer, and log in as a user with administrative privileges (e.g. Administrator). We use Strategy Tutorial objects to create this example:
Create a new Security Filter as below, and assign it to an user.

Create a new Intelligent Cube with attributes and metrics from different tables and hierarchies. As this issue only reproduces when the object is sufficiently complex, we created a cube and added all of the attributes and metrics from the Strategy Tutorial project.

Using the newly created cube, create a new Report. We created a simple report with just Item on the template:

Log out, and log back in as the user with the Security Filter assigned.
Run the report, and note down the data that is returned and the CSI that is generated.
Change the Data Engine Version to 11.

If using Strategy 10.11 or 11.0, turn on the Feature Flag "Defect fixes that may impact Data, SQL, MDX, CSI".

Restart the Intelligence Server.
Run the report. Note how the data has not changed, but the CSI has (changes in red):

Sign up for an instructor-led course or watch the course recording here.

This course is part of the Analytics Architect Learning Path, which focuses on creating, publishing, and optimizing a federated data layer as the enterprise’s single version of the truth.

Dossier of the Month

Togetherness is strength. Courage.

Congratulations to the May DossBoss Champions, team CrackerzIn, comprised of Rajavel Selvaraj Ganesan and Reema Mohanty. The judges raved about this team’s beautiful, functional free-form dossier submission. Their COVID-19 study encompassed everything from hospital bed availability to health and safety guidelines, but what really made these authors stand out was their inclusion of predictive insights coupled with a plethora of additional data from outside sources like John Hopkins University and the World Health Organization. Be sure to check out all of the winning dossiers on the DossBoss website for more inspiration and to enter in the June challenge. Do you have what it takes to become the next DossBoss?

Explore Together

The DossBoss Challenge

Together, we are an ocean.

Are you looking to share your dossier-building skills?

For the June competition, impress the DossBoss committee by using the provided dataset to tell a story about climate change. Pull in data about weather patterns, water levels, temperature, and any other natural statistics from across the globe. Be sure to take advantage of free-form layout and other new dossier features to show off your DossBoss-worthy skills.

As always, feel free to visit the Community for help with troubleshooting or getting inspiration. Submit your completed DossBoss visualization here as a .mstr file or video by EOD on Wednesday, June 24, 2020. Winners will be announced in July’s MicroMinute and on the Community DossBoss page.

Meet with Other Community Members

Quotes from the Experts

Successful data science projects build a bridge between data, algorithms, and end users. Strategy makes this easy because end users and data are centralized, and because data scientists aren't required to use vendor-specific tooling that is incompatible with their models. All of this is essential for building future-proof AI solutions that drive business value and avoid becoming shelf-ware.
- Scott Rigney, Principal Product Manager

That’s it for the June edition of the MicroMinute.
Stay tuned for the July edition!
Subscribe here to have it delivered to your inbox.

Sign me up

The MicroMinute | May 04, 2020

A note from the Education and Community teams-
As the world continues to change and adapt to ongoing situations, we feel honored and proud to see so much resilience in our Community. We have received an overwhelming response to our free education offering, so we’ve decided to extend this offer through May 15th! Take a look through our education brochure for more information on courses and certifications—don’t miss out on this great learning opportunity!

Want to sign up? Simply create an account in our Education Center, log in, and browse or search for courses of interest. You will be able to register for courses at no cost.

Amidst these challenging times, we’re dedicated to making your Community experience less challenging. Check out the new navigation experience we launched on Community last week!

Enjoy our May edition of The MicroMinute, and as always, feel free to reach out with any questions or ideas!

Featured Content

Best Practices for Creating a Responsive Free-Form Dossier
(READ TIME | Approximately 5 min)

Want to design functional yet aesthetic and responsive dossiers using free-form layout? Check out this blog by our free-form dossier authoring experts, to learn some best practices and tips on how to hone your skills!

Linking Dossiers to Create Workflows

Explore how to use dossier linking to navigate between different dossiers within the same project. Boost dossier performance by spreading out your data across multiple dossiers instead of having a multitude of unused datasets in a single project.

Check out more about dossier linking here!

2020 Vision: Next Decade for Enterprise Analytics
(READ TIME | Approximately 2 min)

While AI and ML are becoming increasingly important to all industries, these skills are rare for many organizations to find. Despite this, there are still alternative ways to integrate your enterprise analytics with AI and ML. Peek into the (near) future to see how soon that hope may become your reality!

Education Spotlight

11.415: Advanced Dashboarding with Dossiers
Want to build better and more complex dossiers for your organization? In this class, learn to import complex data to enhance visually compelling, interactive data representations. Create new objects by grouping attribute elements and creating new attributes and metrics based on dataset objects. Build a story about your data to help your users make accurate, timely business decisions.

Sample class exercise:

This course is part of the Strategy Certified Architect (MCA) Learning Path.

Visit the Education Center to sign up for classes and explore our course catalog.

Community Across the Globe

Omar Vidaure: Strategy Community Master, Principal Analytics Engineer
Location: San Francisco Bay Area, California

How did you get into the technology field?
As a teen, I was always intrigued with organizing information and deriving insights that weren’t obvious. After earning a degree in Biological Science, I realized I preferred working with data and moved into a role focused in SQL development.

To see Omar’s full 5-question interview, take a look at the article on Community!

Dossier of the Month

The Force Runs Strong in this Dossier

Congrats to the April DossBoss Champion, Sharad Suman from Aerete! The judges suspect he may be force sensitive—how else could he have built such a stupendous Star Wars dossier? Filled with analysis of the different movies, comparisons of the famous planets within the galaxy, and stats of the main characters across the force spectrum, this dossier is a blast to experience for any Star Wars fan! Be sure to check out Sharad’s dossier along with other past winners on the DossBoss website for more inspiration and to enter in the May challenge. Do you have what it takes to become the next DossBoss?

Sense the Force

The DossBoss Challenge

Hope.

Do you want to be a DossBoss?
It’s about time to enter Strategy’s May DossBoss Visualization Challenge! For the May competition, the DossBoss committee has created a starter dataset revolving around the COVID-19 pandemic. Show off your dossier prowess by creating an informational dossier to show the virus’s impact across the globe. To really impress the judges, expand on this dataset with other healthcare or industry data revolving around current events.

As always, feel free to visit the Community for help with troubleshooting or getting inspiration. Submit your completed DossBoss visualization here as a .mstr file or video by EOD on Wednesday, May 22, 2020. Winners will be announced in the June MicroMinute and in the Community DossBoss group.

Meet with Other Community Members

Quotes from the Experts

The product that I find most exciting in Strategy is Platform Analytics. With Platform Analytics, Administrators have all the information they need right at their fingertips to ensure their environment is running optimally. From identifying unused subscriptions/managed objects and inactive users to troubleshooting underperforming reporting objects, Platform Analytics is the key tool for any Administrator.
Rick Gardner, Product Manager, VP

That’s it for the May edition of the MicroMinute.
Stay tuned for the June edition!
Subscribe here to have it delivered to your inbox.

Sign me up

CapabilityDossierDocumentOpen CanvasAs of 2020, use Free-form layout to build dossiers.Design documents pixel-by-pixel.FormattingUse responsive design for formatting and out-of-the-box color palettes to brand your dossier. Use themes to format and color your document or specific objects.Responsive DesignDossiers automatically adjust to fit your screen, but you can group objects to customize the mobile layout.You must build a specific view for every device a user will view the document on.LinkingLink from visualizations, text boxes, or images to other dossiers while preserving filtering criteria.Link from visualizations, text boxes, or images to other documents while preserving filtering criteria.Creating VisualizationsUse a variety of out-of-the-box visualizations or add third party visualizations.Use grids or graphs to illustrate data.Creating GridsTwo grid options: grid and compound grid.One grid option.ThresholdsApply advanced thresholds.Apply advanced thresholds.SortingAdvanced sort is always absolute.Easy to set a hierarchical sort based on attributes and metrics.PromptsSupport prompts.Support prompts.Offline Authoring and ConsumptionSupport for offline consumption and authoring.Support for offline consumption, but not authoring.Speed of AuthoringQuick start to finish delivery. Requires meticulous editing at the benefit of a more precise, highly-formatted document.Data Engine Version 10


Tables Accessed:
Table49 [L_ITEM]: Item, LOOKUP_TABLE
Table97 [REL_CALL_CENTER_EMPLOYEE]: Call Center, Employee, RELATIONSHIP_TABLE, ONE_TO_MANY 
Table119 [REL_CUSTOMER_CITY_CUSTOMER]: Customer City, Customer, RELATIONSHIP_TABLE, ONE_TO_MANY 
Table127 [F_ORDER_DETAIL]: Promotion, Employee, Item, Day, Phone Usage, Customer, Order, Unit Cost, Unit Price, 
     Gross Revenue, Discount, Revenue, Units Sold, Profit, Cost, Unit Profit, Item Count, FACT_TABLE 
Table131 [F_CITY_CTR_SLS]: Call Center, Customer City, Gross Revenue, Revenue, Units Sold, Profit, Cost, FACT_TABLE 

select [Item]@[ITEM_ID], [Item]@[ITEM_NAME] 
from Tutorial EMMA 
with Table Join Tree: [F_CITY_CTR_SLS]<([Call Center]@[CALL_CTR_ID] not in (12) 
or (not [Customer City]@[CUST_CITY_ID] in (19, 22)))> 
Join [REL_CALL_CENTER_EMPLOYEE] with output level Tuple([Customer City]@[CUST_CITY_ID], [Employee]@[EMP_ID]) 
Join [REL_CUSTOMER_CITY_CUSTOMER] with output level Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID]) 
Join (Set of distinct Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID], [Item]@[ITEM_ID]) 
     where Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID], [Item]@[ITEM_ID]) 
     in [F_ORDER_DETAIL]) with output level Tuple([Item]@[ITEM_ID])

Data Engine Version 11


Tables Accessed: 
Table49 [L_ITEM]: Item, LOOKUP_TABLE 
Table97 [REL_CALL_CENTER_EMPLOYEE]: Call Center, Employee, RELATIONSHIP_TABLE, ONE_TO_MANY 
Table109 [REL_CUSTOMER_ORDER]: Customer, Order, RELATIONSHIP_TABLE, ONE_TO_MANY 
Table119 [REL_CUSTOMER_CITY_CUSTOMER]: Customer City, Customer, RELATIONSHIP_TABLE, ONE_TO_MANY 
Table127 [F_ORDER_DETAIL]: Promotion, Employee, Item, Day, Phone Usage, Customer, Order, Unit Cost, Unit Price, 
     Gross Revenue, Discount, Revenue, Units Sold, Profit, Cost, Unit Profit, Item Count, FACT_TABLE 
Table131 [F_CITY_CTR_SLS]: Call Center, Customer City, Gross Revenue, Revenue, Units Sold, Profit, Cost, FACT_TABLE 

select [Item]@[ITEM_ID], [Item]@[ITEM_NAME] 
from Tutorial EMMA 
with Table Join Tree: [F_CITY_CTR_SLS]<([Call Center]@[CALL_CTR_ID] not in (12) 
or (not [Customer City]@[CUST_CITY_ID] in (19, 22)))> 
Join [REL_CALL_CENTER_EMPLOYEE] with output level Tuple([Customer City]@[CUST_CITY_ID], [Employee]@[EMP_ID]) 
Join [REL_CUSTOMER_CITY_CUSTOMER] with output level Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID]) 
Join [REL_CUSTOMER_ORDER] with output level Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID], [Order]@[ORDER_ID]) 
Join (Set of distinct Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID], [Item]@[ITEM_ID], [Order]@[ORDER_ID]) 
     where Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID], [Item]@[ITEM_ID], [Order]@[ORDER_ID]) 
     in [F_ORDER_DETAIL]) with output level Tuple([Item]@[ITEM_ID])


Tables Accessed:
Table49 [L_ITEM]: Item, LOOKUP_TABLE
Table97 [REL_CALL_CENTER_EMPLOYEE]: Call Center, Employee, RELATIONSHIP_TABLE, ONE_TO_MANY 
Table119 [REL_CUSTOMER_CITY_CUSTOMER]: Customer City, Customer, RELATIONSHIP_TABLE, ONE_TO_MANY 
Table127 [F_ORDER_DETAIL]: Promotion, Employee, Item, Day, Phone Usage, Customer, Order, Unit Cost, Unit Price, 
     Gross Revenue, Discount, Revenue, Units Sold, Profit, Cost, Unit Profit, Item Count, FACT_TABLE 
Table131 [F_CITY_CTR_SLS]: Call Center, Customer City, Gross Revenue, Revenue, Units Sold, Profit, Cost, FACT_TABLE 

select [Item]@[ITEM_ID], [Item]@[ITEM_NAME] 
from Tutorial EMMA 
with Table Join Tree: [F_CITY_CTR_SLS]<([Call Center]@[CALL_CTR_ID] not in (12) 
or (not [Customer City]@[CUST_CITY_ID] in (19, 22)))> 
Join [REL_CALL_CENTER_EMPLOYEE] with output level Tuple([Customer City]@[CUST_CITY_ID], [Employee]@[EMP_ID]) 
Join [REL_CUSTOMER_CITY_CUSTOMER] with output level Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID]) 
Join (Set of distinct Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID], [Item]@[ITEM_ID]) 
     where Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID], [Item]@[ITEM_ID]) 
     in [F_ORDER_DETAIL]) with output level Tuple([Item]@[ITEM_ID])

Data Engine Version 11


Tables Accessed: 
Table49 [L_ITEM]: Item, LOOKUP_TABLE 
Table97 [REL_CALL_CENTER_EMPLOYEE]: Call Center, Employee, RELATIONSHIP_TABLE, ONE_TO_MANY 
Table109 [REL_CUSTOMER_ORDER]: Customer, Order, RELATIONSHIP_TABLE, ONE_TO_MANY 
Table119 [REL_CUSTOMER_CITY_CUSTOMER]: Customer City, Customer, RELATIONSHIP_TABLE, ONE_TO_MANY 
Table127 [F_ORDER_DETAIL]: Promotion, Employee, Item, Day, Phone Usage, Customer, Order, Unit Cost, Unit Price, 
     Gross Revenue, Discount, Revenue, Units Sold, Profit, Cost, Unit Profit, Item Count, FACT_TABLE 
Table131 [F_CITY_CTR_SLS]: Call Center, Customer City, Gross Revenue, Revenue, Units Sold, Profit, Cost, FACT_TABLE 

select [Item]@[ITEM_ID], [Item]@[ITEM_NAME] 
from Tutorial EMMA 
with Table Join Tree: [F_CITY_CTR_SLS]<([Call Center]@[CALL_CTR_ID] not in (12) 
or (not [Customer City]@[CUST_CITY_ID] in (19, 22)))> 
Join [REL_CALL_CENTER_EMPLOYEE] with output level Tuple([Customer City]@[CUST_CITY_ID], [Employee]@[EMP_ID]) 
Join [REL_CUSTOMER_CITY_CUSTOMER] with output level Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID]) 
Join [REL_CUSTOMER_ORDER] with output level Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID], [Order]@[ORDER_ID]) 
Join (Set of distinct Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID], [Item]@[ITEM_ID], [Order]@[ORDER_ID]) 
     where Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID], [Item]@[ITEM_ID], [Order]@[ORDER_ID]) 
     in [F_ORDER_DETAIL]) with output level Tuple([Item]@[ITEM_ID])


Tables Accessed:
Table49 [L_ITEM]: Item, LOOKUP_TABLE
Table97 [REL_CALL_CENTER_EMPLOYEE]: Call Center, Employee, RELATIONSHIP_TABLE, ONE_TO_MANY 
Table119 [REL_CUSTOMER_CITY_CUSTOMER]: Customer City, Customer, RELATIONSHIP_TABLE, ONE_TO_MANY 
Table127 [F_ORDER_DETAIL]: Promotion, Employee, Item, Day, Phone Usage, Customer, Order, Unit Cost, Unit Price, 
     Gross Revenue, Discount, Revenue, Units Sold, Profit, Cost, Unit Profit, Item Count, FACT_TABLE 
Table131 [F_CITY_CTR_SLS]: Call Center, Customer City, Gross Revenue, Revenue, Units Sold, Profit, Cost, FACT_TABLE 

select [Item]@[ITEM_ID], [Item]@[ITEM_NAME] 
from Tutorial EMMA 
with Table Join Tree: [F_CITY_CTR_SLS]<([Call Center]@[CALL_CTR_ID] not in (12) 
or (not [Customer City]@[CUST_CITY_ID] in (19, 22)))> 
Join [REL_CALL_CENTER_EMPLOYEE] with output level Tuple([Customer City]@[CUST_CITY_ID], [Employee]@[EMP_ID]) 
Join [REL_CUSTOMER_CITY_CUSTOMER] with output level Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID]) 
Join (Set of distinct Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID], [Item]@[ITEM_ID]) 
     where Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID], [Item]@[ITEM_ID]) 
     in [F_ORDER_DETAIL]) with output level Tuple([Item]@[ITEM_ID])

Data Engine Version 11


Tables Accessed: 
Table49 [L_ITEM]: Item, LOOKUP_TABLE 
Table97 [REL_CALL_CENTER_EMPLOYEE]: Call Center, Employee, RELATIONSHIP_TABLE, ONE_TO_MANY 
Table109 [REL_CUSTOMER_ORDER]: Customer, Order, RELATIONSHIP_TABLE, ONE_TO_MANY 
Table119 [REL_CUSTOMER_CITY_CUSTOMER]: Customer City, Customer, RELATIONSHIP_TABLE, ONE_TO_MANY 
Table127 [F_ORDER_DETAIL]: Promotion, Employee, Item, Day, Phone Usage, Customer, Order, Unit Cost, Unit Price, 
     Gross Revenue, Discount, Revenue, Units Sold, Profit, Cost, Unit Profit, Item Count, FACT_TABLE 
Table131 [F_CITY_CTR_SLS]: Call Center, Customer City, Gross Revenue, Revenue, Units Sold, Profit, Cost, FACT_TABLE 

select [Item]@[ITEM_ID], [Item]@[ITEM_NAME] 
from Tutorial EMMA 
with Table Join Tree: [F_CITY_CTR_SLS]<([Call Center]@[CALL_CTR_ID] not in (12) 
or (not [Customer City]@[CUST_CITY_ID] in (19, 22)))> 
Join [REL_CALL_CENTER_EMPLOYEE] with output level Tuple([Customer City]@[CUST_CITY_ID], [Employee]@[EMP_ID]) 
Join [REL_CUSTOMER_CITY_CUSTOMER] with output level Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID]) 
Join [REL_CUSTOMER_ORDER] with output level Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID], [Order]@[ORDER_ID]) 
Join (Set of distinct Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID], [Item]@[ITEM_ID], [Order]@[ORDER_ID]) 
     where Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID], [Item]@[ITEM_ID], [Order]@[ORDER_ID]) 
     in [F_ORDER_DETAIL]) with output level Tuple([Item]@[ITEM_ID])


Tables Accessed:
Table49 [L_ITEM]: Item, LOOKUP_TABLE
Table97 [REL_CALL_CENTER_EMPLOYEE]: Call Center, Employee, RELATIONSHIP_TABLE, ONE_TO_MANY 
Table119 [REL_CUSTOMER_CITY_CUSTOMER]: Customer City, Customer, RELATIONSHIP_TABLE, ONE_TO_MANY 
Table127 [F_ORDER_DETAIL]: Promotion, Employee, Item, Day, Phone Usage, Customer, Order, Unit Cost, Unit Price, 
     Gross Revenue, Discount, Revenue, Units Sold, Profit, Cost, Unit Profit, Item Count, FACT_TABLE 
Table131 [F_CITY_CTR_SLS]: Call Center, Customer City, Gross Revenue, Revenue, Units Sold, Profit, Cost, FACT_TABLE 

select [Item]@[ITEM_ID], [Item]@[ITEM_NAME] 
from Tutorial EMMA 
with Table Join Tree: [F_CITY_CTR_SLS]<([Call Center]@[CALL_CTR_ID] not in (12) 
or (not [Customer City]@[CUST_CITY_ID] in (19, 22)))> 
Join [REL_CALL_CENTER_EMPLOYEE] with output level Tuple([Customer City]@[CUST_CITY_ID], [Employee]@[EMP_ID]) 
Join [REL_CUSTOMER_CITY_CUSTOMER] with output level Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID]) 
Join (Set of distinct Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID], [Item]@[ITEM_ID]) 
     where Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID], [Item]@[ITEM_ID]) 
     in [F_ORDER_DETAIL]) with output level Tuple([Item]@[ITEM_ID])

Data Engine Version 11


Tables Accessed: 
Table49 [L_ITEM]: Item, LOOKUP_TABLE 
Table97 [REL_CALL_CENTER_EMPLOYEE]: Call Center, Employee, RELATIONSHIP_TABLE, ONE_TO_MANY 
Table109 [REL_CUSTOMER_ORDER]: Customer, Order, RELATIONSHIP_TABLE, ONE_TO_MANY 
Table119 [REL_CUSTOMER_CITY_CUSTOMER]: Customer City, Customer, RELATIONSHIP_TABLE, ONE_TO_MANY 
Table127 [F_ORDER_DETAIL]: Promotion, Employee, Item, Day, Phone Usage, Customer, Order, Unit Cost, Unit Price, 
     Gross Revenue, Discount, Revenue, Units Sold, Profit, Cost, Unit Profit, Item Count, FACT_TABLE 
Table131 [F_CITY_CTR_SLS]: Call Center, Customer City, Gross Revenue, Revenue, Units Sold, Profit, Cost, FACT_TABLE 

select [Item]@[ITEM_ID], [Item]@[ITEM_NAME] 
from Tutorial EMMA 
with Table Join Tree: [F_CITY_CTR_SLS]<([Call Center]@[CALL_CTR_ID] not in (12) 
or (not [Customer City]@[CUST_CITY_ID] in (19, 22)))> 
Join [REL_CALL_CENTER_EMPLOYEE] with output level Tuple([Customer City]@[CUST_CITY_ID], [Employee]@[EMP_ID]) 
Join [REL_CUSTOMER_CITY_CUSTOMER] with output level Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID]) 
Join [REL_CUSTOMER_ORDER] with output level Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID], [Order]@[ORDER_ID]) 
Join (Set of distinct Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID], [Item]@[ITEM_ID], [Order]@[ORDER_ID]) 
     where Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID], [Item]@[ITEM_ID], [Order]@[ORDER_ID]) 
     in [F_ORDER_DETAIL]) with output level Tuple([Item]@[ITEM_ID])

Cause

This is a known regression issue in Strategy 10.11 and later versions:

Data Engine Version 11 contains the fix for another issue (detailed here) that occurs when joining a fact table with duplicated rows. This fix enhanced Data Engine logic but removed an optimization and caused unnecessary tables in the join path.

In the example above, an additional relationship table is used in the join path, but it is not necessary to fetch the correct data.

Solution

Upgrade to Strategy 2020 or above. We recommend the latest version.
Set Data Engine Version setting to 12 or above.

The project level setting Data Engine Version can only be enabled through Workstation. For more details on enabling the setting, see Change Data Engine Version.
After changing the Data Engine Version setting, you will need to republish your datasets to see the changes.

In the new fix, the Data Engine optimizes CSI and removes unnecessary relationship tables in the join tree. After the fix in Data Engine Version 12, the new CSI will be the same as the CSI for Data Engine Version 10. The CSI for Data Engine Version 11 remains un-optimized.
Other reports may be affected by this fix, and cases previously were not optimized will be now, so a CSI change may be seen. Again, there will not be any data change.

Strategy Internal Reference Number for this technical note is KB483665 and DE119566.

Comment

0 comments

Details

Knowledge Article

Published:

September 27, 2019

Last Updated:

March 21, 2024

Tables Accessed: Table49 [L_ITEM]: Item, LOOKUP_TABLE Table97 [REL_CALL_CENTER_EMPLOYEE]: Call Center, Employee, RELATIONSHIP_TABLE, ONE_TO_MANY Table119 [REL_CUSTOMER_CITY_CUSTOMER]: Customer City, Customer, RELATIONSHIP_TABLE, ONE_TO_MANY Table127 [F_ORDER_DETAIL]: Promotion, Employee, Item, Day, Phone Usage, Customer, Order, Unit Cost, Unit Price, Gross Revenue, Discount, Revenue, Units Sold, Profit, Cost, Unit Profit, Item Count, FACT_TABLE Table131 [F_CITY_CTR_SLS]: Call Center, Customer City, Gross Revenue, Revenue, Units Sold, Profit, Cost, FACT_TABLE select [Item]@[ITEM_ID], [Item]@[ITEM_NAME] from Tutorial EMMA with Table Join Tree: [F_CITY_CTR_SLS]<([Call Center]@[CALL_CTR_ID] not in (12) or (not [Customer City]@[CUST_CITY_ID] in (19, 22)))> Join [REL_CALL_CENTER_EMPLOYEE] with output level Tuple([Customer City]@[CUST_CITY_ID], [Employee]@[EMP_ID]) Join [REL_CUSTOMER_CITY_CUSTOMER] with output level Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID]) Join (Set of distinct Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID], [Item]@[ITEM_ID]) where Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID], [Item]@[ITEM_ID]) in [F_ORDER_DETAIL]) with output level Tuple([Item]@[ITEM_ID])

Tables Accessed: Table49 [L_ITEM]: Item, LOOKUP_TABLE Table97 [REL_CALL_CENTER_EMPLOYEE]: Call Center, Employee, RELATIONSHIP_TABLE, ONE_TO_MANY Table109 [REL_CUSTOMER_ORDER]: Customer, Order, RELATIONSHIP_TABLE, ONE_TO_MANY Table119 [REL_CUSTOMER_CITY_CUSTOMER]: Customer City, Customer, RELATIONSHIP_TABLE, ONE_TO_MANY Table127 [F_ORDER_DETAIL]: Promotion, Employee, Item, Day, Phone Usage, Customer, Order, Unit Cost, Unit Price, Gross Revenue, Discount, Revenue, Units Sold, Profit, Cost, Unit Profit, Item Count, FACT_TABLE Table131 [F_CITY_CTR_SLS]: Call Center, Customer City, Gross Revenue, Revenue, Units Sold, Profit, Cost, FACT_TABLE select [Item]@[ITEM_ID], [Item]@[ITEM_NAME] from Tutorial EMMA with Table Join Tree: [F_CITY_CTR_SLS]<([Call Center]@[CALL_CTR_ID] not in (12) or (not [Customer City]@[CUST_CITY_ID] in (19, 22)))> Join [REL_CALL_CENTER_EMPLOYEE] with output level Tuple([Customer City]@[CUST_CITY_ID], [Employee]@[EMP_ID]) Join [REL_CUSTOMER_CITY_CUSTOMER] with output level Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID]) Join [REL_CUSTOMER_ORDER] with output level Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID], [Order]@[ORDER_ID]) Join (Set of distinct Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID], [Item]@[ITEM_ID], [Order]@[ORDER_ID]) where Tuple([Customer]@[CUSTOMER_ID], [Employee]@[EMP_ID], [Item]@[ITEM_ID], [Order]@[ORDER_ID]) in [F_ORDER_DETAIL]) with output level Tuple([Item]@[ITEM_ID])