advanced data analyzer techniques.pdf
TRANSCRIPT
-
7/30/2019 advanced data analyzer techniques.pdf
1/39
6/24/20
Dorene Lofgren
Consultant, GSUSA
July 11, 2010
Advanced Data AnalyzerTechniques
What is Web Intelligence?
Business Objects Web Intelligence is an ad hoc reporting toolfor business users that provides self-service access to data.
Business users can interact with data without having to knowthe complexities of their database.
What are Universes?
A Universe is a watered-down version of a relationaldatabase. The Universe translates the complexitiesof the database into business-friendly terms for end-
users and allows for correct SQL generation.
http://hubpages.com/hub/BusinessObjects_Universe :
A Universe is the interfacing layer between theclient and the actual database. The Universedefines the relationships among the various tables inthe database and contains the connectionparameters to the database.
http://www.geekinterview.com
http://hubpages.com/hub/BusinessObjects_Universehttp://hubpages.com/hub/BusinessObjects_Universehttp://www.geekinterview.com/http://www.geekinterview.com/http://hubpages.com/hub/BusinessObjects_Universe -
7/30/2019 advanced data analyzer techniques.pdf
2/39
6/24/20
Universes make life easier
But it can never be too easy
The data objects I need for myreport are in two differentuniverses!
But I dont want the 9 digitpostal code
Yesterday it was a #DATASYNCerror, today its a #MULTIVALUEerror. Why am I getting all of
these errors?
I need the data joined with anouter joinnot an inner join!Wheres the Find Unmatchedfeature?
Session Objective
This workshop is designed to empower userswith the knowledge and skil ls to manipulateuniverse data to achieve desired results.
Major takeaways include:
How to merge data across multiple data
providers with success
How to combine queries within a universe indifferent ways
How to build powerful expressions.
-
7/30/2019 advanced data analyzer techniques.pdf
3/39
6/24/20
Instructional Methods
This isnt apresentation- its a
hand-on workshop!
Buddy up and startproblem-solving!
Data AnalyzerWarm Up!
Lets get everyone on the same page!
Terminology Review
How is a Universe organized?
Objects - Named component that maps
to data in the database. You
use objects in a query to
retrieve data for your reports.
Classes - Logical grouping of objects.
-
7/30/2019 advanced data analyzer techniques.pdf
4/39
6/24/20
Universe Objects Defined
Object Example Definition
Dimension Retrieves data that will provide thebasis for analysis in a report.
Measure Retrieves numeric data based on acalculation performed on data in the
database.
Detail Refers to pieces of information that do notform the basis for a query but, nonetheless,are needed for the results of a query. ADetail is always attached to a dimension.Details come into play when you mergedata providers, so stay tuned!
Girl Scouts Universes of Interest
All Order Entry
Whats in it?
All Order Entry contains a record for every order linein Personify, segmented by org. It includesinformation about the customers and productsassociated with each order as well.
Query this Universe to
generate a list of active members
create a basic meeting roster
retrieve a list of donors
Accounting Setup
Whats in it?
The Accounting Setup universe contains a list of
your orgs GL accounts in Personify. It also shows the
accounts associated with each product in your org.
Query this Universe to
See the revenue accounts for meeting products
Review your Personify chart of accounts
Access an account name (other universes
typically include account numbersnot names)
Girl Scouts Universes of Interest
Customers
Whats in it?
Tracks information on every customer in the
database, regardless if theyve placed an order or
not. This universe is not segmented by org.
Query this Universe to
access non-primary communication methods,
aliases, special needs, non-mem ber employers,
position codes, credit statuses and purchasing
group information.
Committee Personify Technical Universe
Whats in it?
The Committee Universe is segmented by org. Thisuniverse tracks :
every committee (e.g. award, troop, and serviceunit ) in your org
the positions held by committee members
customer information about each committeemember
Query this Universe to
generate a troop or service unit roster
see a list of customers who currently hold aposition (e.g. 01s or 02s)
retrieve a list of award recipients
-
7/30/2019 advanced data analyzer techniques.pdf
5/39
6/24/20
Girl Scouts Universes of Interest
Meetings
Whats in it?
Meeting registrations and meeting products. Meetingproduct nformation exceeds what is available in AllOrder Entry. Only includes meetings that havecustomers registered to them.
Query this Universe to
produce a meeting roster with detailed productinformation
Generic Product Setup
Whats in it?
Contains information about every product set up in
your org, including rates and pricing, GL accounts,
cancellation fees, and package components for eBiz.
Query this Universe to
Generate a list of meeting products with or without
registrants.
Marketing Universe
Whats in it?
The Marketing Universe is the only universe that
currently holds Contact Tracking data.
Query this Universe to
generate a list of contact tracking records by topic or
staff assigned.
Girl Scouts Universes of Interest
System Setup
Whats in it?
Contains setup information for your org and
products. You typically will not need to query this
universe unless you cannot find a products
attribute anywhere else. For example, this is the only
universe that contains eCommerce controls for web
products.
Query this Universe to
check setup information for your web products.
Security Setup
Whats in it?
Contains user login information and user group
information.
Query this Universe to
See the last time your users logged into Personify
See the groups assigned to your users in Personify
Warm Up Team Activity
Which Universe would you select to create1. An adult member listing?
All Order Entry
2. A troop roster for the current year? Committee
3. A list of programs coming up in the next three months,including programs where no one is registered? Generic Product Setup
4. A list of users with the last time they logged on? Security Setup
5. A list of customers in the database that have an Alias? Customer
-
7/30/2019 advanced data analyzer techniques.pdf
6/39
6/24/20
Lets Build One Together
A Troop Roster for 2010
Additional Requirements
Include a user prompt for troop name
Begin Date and End Date Filters
Inclusions
Test Case BeginDates
End Dates
Annual Members
(positions added frombatch)
10/01/09 09/30/10
Mid-year transfers
(to troop/council)
11/30/09
06/15/10
09/30/10
Annual mid-year
positions added
manually
11/30/09
06/15/10
09/30/10
09/30/10
L if et ime memb er s 1 1/ 30 /0 0
10/01/09
09/30/90
09/30/10
Exclusions
Test Case BeginDates
EndDates
Annual Members (past
or future)
10/01/08
10/01/10
09/30/09
09/30/11
Mid-year transfers
(from troop/council)
10/01/09
11/01/09
12/15/09
06/01/10
Annual mid-year
positions that wereterminated
10/01/09
11/01/09
12/15/09
06/01/10
[Begin Date] = 9/30/10
Result Objects and Query Filters
-
7/30/2019 advanced data analyzer techniques.pdf
7/39
6/24/20
Merged Queries
Introduction
What is a Merged Query?
A merged query draws related data fromdifferent data providers.
Data come from Query
2 which is based on theCustomers universe
Data comes from
Query 1 which isbased on the AllOrder Entry universe
Why merge?
When the data you need comes from two differentdata providers, Web Intelligence does not knowthat it is related.
You tell Web Intelligence that the data is related bymerging the two data providers on the commondimension.
-
7/30/2019 advanced data analyzer techniques.pdf
8/39
6/24/20
Merged query example
Query 1 - Members Query 2Customer Aliases
Common
Dimension
Merged Objects
Customer
Aliases arestored in theCustomers
Universe
All Order Entry isneeded to
display activemembers.
ButCustomer Aliasesare not stored in
the All Order EntryUniverse
Examples of Merged Queries
1. Position Codes forLifetime Members
Positions arestored in theCommittee
Universe.
But
You cannot tell
from theCommittee
Universe that acustomer is also
a lifetimemember
All Order EntryUniverse isneeded to
display
customers thatpurchased a
Lifemembership
product
Examples of Merged Queries
2. Non-Primary Email Addresses forMembers
Non-Primarycontact records
are in theCustomersUniverse.
But
You cannot tellfrom the
CustomersUniverse that
customer is alsoa member
All Order EntryUniverse isneeded to
displaycustomers thathave an active
membershipproduct
-
7/30/2019 advanced data analyzer techniques.pdf
9/39
6/24/20
Examples of Merged Queries
3. Adult Member Training History
The first data setfilters for all
adult members
The seconddata set filtersfor all training
order lines andincludes the
customers whoattended them.
All Order Entrycontains all the
Data Objects youneed for this report.
But
Data Objects must
be contained intwo separate
queries becauseyoure dealing withtwo separate datasets with different
filters
Common Mistake
And condition cancels out everything!
You are filtering for order lines that sell an Adultmembership product and products classified astrainings. Each order line can sell either,however, not both!
Better Solution:Create 2 queries and merge the fields you need
Active Order Lines that sellproducts classified as Trainings Active Adult Members for 2010
-
7/30/2019 advanced data analyzer techniques.pdf
10/39
6/24/20
4. Meeting Roster that shows the SU of each participant
Examples of Merged Queries
The first data set
filters for all activeorder lines that sell
a specifiedmeeting product.
The second dataset filters for eachmembers current
service unitAll Order Entry technically contains all the Data
Objects you need for this report.
But
Data Objects must be contained in two separatequeries because youre dealing with two separate
data sets with different filters
Common Mistake
And condition cancels out everything!
You are filtering for order lines that sell aspecified meeting product and are linked to aHierarchy Year. However, only membership
order lines are linked to a hierarchy record!
Better Solution:Create 2 queries and merge the fields you need
Active Order Lines for aspecified Meeting Product Name Active Members for 2010 withtheir 2010 membership hierarchy
-
7/30/2019 advanced data analyzer techniques.pdf
11/39
6/24/20
Team Activity
To Merge or Not to Merge
Ex 1A List of Members Associated witha List Demographic
To Merge or Not to Merge
No, you do not need to merge!
List Demographics are associated with Ship-to
Customers (e.g. members) in ALL Order Entry.
Ex. 2Adult member award Listing byService Unit
To Merge or Not to Merge
Yes, you need merge!
Service Units are related to troop records, notawards. You first will need to generate an
award listing, then a member listing by serviceunit. Then, connect the lists by Customer ID
and merge whats missing!
-
7/30/2019 advanced data analyzer techniques.pdf
12/39
6/24/20
Ex 3A Statistical Report that counts residentcamp participants by year and race
To Merge or Not to Merge
No, you do not need to merge, although you
can!
Camp orders are found in ALL Order Entry. TheRace is an attribute of the Ship -to Customer
and can be found in All Order Entry also.
Ex 4An Employer Listing for Adult Members
To Merge or Not to Merge
No, you do not need to merge!
Adult member employers are found in the
Ship-to Customer All Relationships folder of AllOrder Entry.
Ex. 5Girl Guardian Employers
To Merge or Not to Merge
Yes, you need merge!
You can get the guardian Customer IDs fromAll Order Entry, but not the employers of theguardians. Instead, you will need to merge
guardian IDs with the Customers Universe tograb their employer info.
-
7/30/2019 advanced data analyzer techniques.pdf
13/39
6/24/20
How to Merge
A Step-by-Step Guide
Merged Query Demo #1
Member Roster with WinPCMS IDs
Merged Query Demo #2
Meeting Roster that shows each memberstroop and Service Unit
-
7/30/2019 advanced data analyzer techniques.pdf
14/39
6/24/20
Components of a Merged Query
Query 1 and 2
Query 1- Main query Query 2- Detail Query
Contains the records youwant to see in your finalreport, and includes the
Merged Dimension
Contains the MergedDimension and the fieldsyou will merge into your
final report
Components of a Merged QueryReport 1 and 2
Report 1- Main report Report 2- Detail Report
Contains the output ofQuery 1. This report wil l be
your final report thatcontains merged data.
Contains the output ofQuery 2. You can discard
this report if youd like.
Components of a Merged QueryMerged Dimension & Detail Objects
Merged Dimension Detail Objects
Common field in Query 1 and
Query 2 you need to join in orderto bridge data from Query 2 toQuery 1. The object s name inboth queries can be different as
long as the data is the same.
Fields in Query 2 that you will
bridge over to your final report.
-
7/30/2019 advanced data analyzer techniques.pdf
15/39
6/24/20
Components of a Merged Query
Putting it all together
Merged Dimension
Detail Objects
Source Dimensionsfor Detail Objects
Creating a Merged QueryStep-by-step (1)
Step 1: Build a new Webi based on the universe you need forQuery 1
Creating a Merged QueryStep-by-step (2-3)
Step 2: Construct Query 1. Add as many fields as possible thatyou want to show on the final report, including your mergeddimension.
Step 3: Right-click the Query 1 tab and choose Add Queryto build Query 2. Select the Universe that represents the datasource for Query 2.
-
7/30/2019 advanced data analyzer techniques.pdf
16/39
6/24/20
Creating a Merged Query
Step-by-step (4-5)
Step 4: Click the Query 2 tab and construct Query 2. In thisquery include the merged dimension and all of the objectsnot available in Query 1 that you will merge in your final report.
Step 5: ClickRun Queries to generate the reports. At the NewQuery message, opt to Insert a table in a new report.
Creating a Merged QueryStep-by-step (6-7)
Step 6: Click the tab for Report 1. You are ready to build yourmerged dimension. Click the Merged Dimension icon in the
toolbar.
Step 7: Under Query 1 and Query 2, select the fields you willuse to join the queries. Typically these fields are IDs of somekind that are the same field in the underlying database. ThenclickMerge.
Creating a Merged QueryStep-by-step (8)
Step 8: Create a name for the merged dimension and clickOK. Back in the Merged Dimensions window, click OK as well.
-
7/30/2019 advanced data analyzer techniques.pdf
17/39
6/24/20
Creating a Merged Query
Step-by-step (9-10)
Step 9: Now you are ready to buildyour Detail objects. Open the VariableEditor in the toolbar.
Step 10: In the Variable Editor,
1. Double-click the first field you want to
merge into Report 1 so that it displaysin the Formula box.
2. Give the field a name. (The name
must be different than the originalobjects name.)
3. Under Qualification, select Detail.
4. Click the ellipsis next to AssociatedDimension. Expand the mergeddimension and select the originaldimension from Query 1. This links theDetail object to Query 1. ClickOK
5. Click OK in the Variable Editor. Repeatthe process for all the fields y ou wantto merge into Report 1.
12
3
4
5
Creating a Merged QueryStep-by-step (11)
Step 11: From Report 1, drag the detail objects you justcreated onto the report. Save your work. You have just
created a merged query!
Team Activity
Plan your merges
-
7/30/2019 advanced data analyzer techniques.pdf
18/39
6/24/20
Activity #1:
Adult Members and their Awards
Adult Members and their AwardsStep 1: Choose your universes
1. What Universe do youquery to retrieve adultmembers?(Becomes Query 1)
__________________
2. What Universe do youquery to retrieve theawards customers havereceived?(Becomes Query 2)
_____________________
Answer: All Order Entry Answer: Committee
Adult Members and their AwardsStep 2: Choose your Result Objects
1. What are the Result
Objects in Query 1?(List them below)
2. What are the Result
Objects in Query 2?(List them below)
Answer:Answer:
-
7/30/2019 advanced data analyzer techniques.pdf
19/39
6/24/20
Adult Members and their Awards
Step 3: Choose your filters
1. What filters do you needfor Query 1?
(List them below)
2. What filters do you needfor Query 2?
(List them below)Answer: Answer:
Adult Members and their AwardsStep 4: Create the Merged Dimension
1. Which object from Query 1will become the mergeddimension?
(Circle your answer)
2. Which object from Query 2will become the mergeddimension?
(Circle your answer)
Adult Member AwardsStep 5: Create your Detail Objects
1. Which objects from Query 2 will become Detail Objects? (Circleyour answer below.)
-
7/30/2019 advanced data analyzer techniques.pdf
20/39
6/24/20
Adult Member AwardsStep 5: Create your Detail Objects
Name: _________________
Associated Dimension:________________________
Formula: _______________
Name: _________________
Associated Dimension:________________________
Formula: _______________
For each Detail, identify its Name, Associated Dimension and Formula.
Answer: Master Customer ID
Begin Date Detail
Label Name Detail
Ship to Customer ID
Ship to Customer ID
=[Begin Date]
=[Label Name]
Merged Query Joins
Utilize the common dimension to select the records you want
Merged query joins
Data is merged on the common dimension in a left- orright-outer join, depending onwhich side of the merged dimension you insert i n your final report. You can j oindata with an inner join also.
-
7/30/2019 advanced data analyzer techniques.pdf
21/39
6/24/20
Join Definitions
A left outer join selects all of the IDs from Query 1 and showsdata from Query 2 if theres a match.
A right outer join selects all of the IDs from Query 2 and shows
data from Query 1 if theres a match.
An inner join only selects IDs that exist in both queries.
Left Outer Join Example
Selecting the Ship-to Customer ID from Query 1 displ ays all members (i.e. Ship to
Customers), whether they have awards or not.
Query 1Current Adult MembersQuery 2Customers with awards
Inner Join Example
Only members with an awards show in the
results.
Selecting the Master Customer ID from Query2 creates an inner join if the property Showrows with empty dimension values is set toNo. (It is set to No by default)
Query 1Current Members
Query 2Customers with awards
-
7/30/2019 advanced data analyzer techniques.pdf
22/39
6/24/20
Right Outer Join Example
Selecting the Master Customer ID from Query 2 dis plays all customers with awards,regardless if they are members or not if the property Show rows with empty dimensionvalues is set to Yes. (It is set to No by default)
Query 1Current Adult MembersQuery 2Customers with awards
How to change joins in a Webi
To create a LeftOuter Join
Drag mergeddimension from
Query 1 onto report
To create an InnerJoin
Drag mergeddimension from
Query 2 onto report
To create a RightOuter join
Drag merged dimensionfrom Query 2 onto report.
Set Show rows with emptydimension values to Yes.
Team Activity
Select the records you want using Inner and Outer joins
-
7/30/2019 advanced data analyzer techniques.pdf
23/39
6/24/20
Activity #1a:
Lifetime Member Positions Codes
Circle the dimension you would drag to show all lifetime members,regardless if they hold a position or not.
Query 1 contains lifetime membersQuery 2 contains customers with active positions
Activity #1b:Lifetime Member Position Codes
Circle the dimension you would drag to only show lifetime memberswith positions.
Query 1 contains lifetime members
Query 2 contains customers with active positions
Activity #2a:Non-Primary Email Addresses for Members
Circle the dimension you would drag to show all members, whetherthey have a non-primary email address or not.
Query 1 contains all current membersQuery 2 contains customers with a non-primaryemail address
-
7/30/2019 advanced data analyzer techniques.pdf
24/39
6/24/20
Activity #2b:
Lifetime Member Position Codes
Circle the dimension you would drag to only show members thathave a non-primary email address.
Query 1 contains all current membersQuery 2 contains customers with a non-primaryemail address
Which query becomes Query 1?
The importance of starting off the way you want to finish
FAQ:I know which queries I need.
Which query should I make Query 1?
Contains your final record countin otherwords, the records that should display onthe final report.
Query 1
Includes information you want to seerelating to the records in your final report,but not the actual filters that will produceyour final record set.
Query 2
This recommendation only applies if data needs to be related with anouter join (all records from one data provider show in the final report).
If data ultimately needs to be related in an Inner join, it doesnt matterwhich query becomes Query 1.
-
7/30/2019 advanced data analyzer techniques.pdf
25/39
6/24/20
Team Activity
#1
Award Listing by Service Unit
The final report displays award recipients by their 2010 membership
Service Unit (SU). If they do not have a 2010 SU (because they arentmembers), they should display a Null value in the SU column.
Here are the two queries you will need. Which should become Query 1?(Circle your answer)
A list of al l of the award recipients at the council w ith the award name andBegin Date
A list of acti ve members in 2010 with their current SU.
Team Activity#2
Girl Guardian Employers
The final report displays a list of guardian customers for 2010 that areemployees of the specified employer, in this example Conoco Phill ips.
Here are the two queries you will need. Which should become Query 1?
A lists all guardian 1 and guardian 2 customers related to girl members
registered for the current membership year.
A list of adult customers related to the specified employer.
It doesnt
matter! Thequeries must
be joinedwith an inner
join.
Common Errors associatedwith Merged Queries
And how to avoid them!
-
7/30/2019 advanced data analyzer techniques.pdf
26/39
6/24/20
#DATATSYNC Errors
Cause
Associated Dimension forthe Detail Object is not
linked to the MergedDimension.
Fix
Link the AssociatedDimension to the Merged
Dimension from Query 1.
#MULTIVALUE Errors
Cause
A Detail object can have onevalue only for each value of itsassociated dimension.
If the detail object containsmultiple values for an
associated dimension, WebIntelligence places the#MULTIVALUE error in the detailcell.
Fix
Select the table.
Under Properties set the AvoidDuplicate Row Aggregationproperty to Yes
#MULTIVALUE Error Explained:Lifetime Member Position Codes
Merged dimension repeats in
Position Code query. Each customer can hold more
than one position.
Merged dimension is unique in
lifetime member query. Theres one membership
product per customer.
-
7/30/2019 advanced data analyzer techniques.pdf
27/39
6/24/20
#MULTIVALUE Error Explained:
Lifetime Member Position Codes
Query 2Query 1
After merging data from position code query into Lifetimemember query
#MULTIVALUE Error Explained:Lifetime Member Position Codes
Query 1
After changing the Avoid Duplicate Row Aggregationproperty to Yes
Checkpoint #1
Merging queries becomes necessary if the data you need comesfrom two different data providers.
To merge two queries you need a common dimension in each query.The merged dimension tells BO how to match up data across queries.
Dimensions merging to Query 1 must be made into Detail objectsbefore they can be merged.
You can change the way data is jo ined across queries by draggingeither half of the merged dimension to your main query.
Data Sync errors can be resolved by ensuring Detail objects areassociated with the merged dimension
Multi-value errors can be resolved by setting the Avoid DuplicateRow Aggregation property to Yes
What youve learned so far
-
7/30/2019 advanced data analyzer techniques.pdf
28/39
6/24/20
Combined Queries
Multiple queries on the same universe
About Combined Queries
You can combine queries in three relationships
CombinedQueries
Intersection
Minus
Union
Union Queries
Query 1
Query 2
Union
A union combination takes the all the data from both queries, eliminates
duplicate rows, and builds a combined data set
-
7/30/2019 advanced data analyzer techniques.pdf
29/39
6/24/20
Intersection Queries
Query 1 Query2
An intersection combination returns the data common to both queries.
Intersection Query Example:Returning Leaders
Query 1:CurrentLeaders
Query 2:Last YearsLeaders
Minus Queries
A minus combination returns data from the first query that does not
appear in the second query.
Query 1Query 2
-
7/30/2019 advanced data analyzer techniques.pdf
30/39
6/24/20
3
Query 1:CurrentLeaders
Query 2 :Last YearsLeaders
Minus Query Example:
New Leaders
Query 1:Last YearsLeaders
Query 2:CurrentLeaders
What data would this Minus QueryReturn?
How to Build Combined Queries
A step-by-step guide
-
7/30/2019 advanced data analyzer techniques.pdf
31/39
6/24/20
3
Intersection Query Demos
Returning Leaders
New Leaders
Combined Query Structure
The queries within a combined query must returnthe same number of objects of the same data typeand the objects must be in the same order.
Likely the result objects for each query within acombined query will be exactly the same.
To Build a Combined QueryStep 1
1. Create Query 1, selecting your Result Objectsand Filters. Run the query if youd like.
-
7/30/2019 advanced data analyzer techniques.pdf
32/39
6/24/20
3
To Build a Combined Query
Steps 2-3
2. Back in the query, click the CombinedQuery icon in the toolbar.
3. The query transforms to a combined query,
giving you access to two queries. TheCombined Query type defaults to Union.Double-click Union to change it to aMinus or Intersection query.
To Build a Combined QueryStep 4
4. So far youve completed Combined Query 1.You now need to add filters for Query 2. ClickCombined Query 2 to access the query.
To Build a Combined QueryStep 5
5. Select the filters necessary for Query 2. Thenrun the query.
-
7/30/2019 advanced data analyzer techniques.pdf
33/39
6/24/20
3
Combining more than 2 queries
What if you wanted to compare more than2 queries in your combined query?
New Leaders(checking 3 years of data)
New Leaders(checking 3 years of Data)
Combined Query PrecedenceRound 1 Results
Query Results
Combined Query 1
(2010 Leaders)
Carol, Sue, Tina, Wendy
Combined Query 2(2009 Leaders)
Barbara, Kim, Tina
Minus Round 1 Results:(CQ1- CQ2)
Carol, Sue, Wendy
-
7/30/2019 advanced data analyzer techniques.pdf
34/39
6/24/20
3
Combined Query Precedence
Round 2 Results
Query Results
Minus Round 1 Results:
(CQ1-CQ2)
Carol, Sue, Wendy
Combined Query 3(2008 Leaders)
Amanda, Barbara, Carol
MinusRound 2 Results:(Round 1 ResultsCQ 3)
Sue, Wendy
Combined Query PrecedenceRound 3 Results
Query Results
MinusRound 2 Results:(Minus Round 1Query 3)
Sue, Wendy
Combined Query 4(2007 Leaders)
Amanda, Tina, Wendy
Minus Round 3 Results:Round 2CQ 4)
Sue (Final results)
Summary of Combined Query Logic
Web Intelligence first finds theset of data that represents theunion/intersection/minusbetween Combined Query nand Combined Query n+ 1.
Web Intelligence then finds theunion/intersection/minusbetween that data set and thedata returned by CombinedQuery n + 2.
Web Intelligence continues inthis way through all the queriesin the relationship.
Intersection,Minus or
Union
Query 1
Query 2
Query 3
-
7/30/2019 advanced data analyzer techniques.pdf
35/39
6/24/20
3
Team Activity
Combined Queries
Activity #1Answer the questions below
You would like to produce a list of girls who have beenregistered to your councils for the past 3 years (in 2010,2009, and 2008).
1. What kind of combined query could you use to generate thisresult?_____________
2. How many combined queries would you need? ________
3. What filters would each combined query have? Write yourresponses in the space below.
Intersection
3
Query 1:
2010 Girl members
Product Code = Girl_2010
Line Status Code = AQuery 2:
2009 Girl Members
Product Code = Girl_2009
Line Status Code = A
Query 2:2008 Girl Members
Product Code = Girl_2009Line Status Code = A
Activity #2Answer the questions below
You would like to produce a list of lapsed girls whohavent reregistered from last year.
1. What kind of combined query could you use to generate thisresult?_____________
2. How many combined queries would you need? ________
3. What filters would each combined query have? Write yourresponses in the space below.
Minus
2
Query 1:2009 Girl members
Product Code = Girl_2009Line Status Code = A
Query 2:2010 Girl Members
Product Code = Girl_2010Line Status Code = A
-
7/30/2019 advanced data analyzer techniques.pdf
36/39
6/24/20
3
Super Combined Query on Steroids
(for super advanced users)
Lapsed girls ( Going back 2 years)
2008 Girl
Members
2010 GirlMembers
Minus
2009 GirlMembers
Minus
2010 GirlMembers
Combines results of both minusqueries, selecting Distinct
Records to avoid duplicatesfrom both record sets
Checkpoint #2
Combined queries allow you to answer questions that areotherwise difficult or impossible to frame in a single WebIntelligence query:
A minus combination returns data from the first query that does notappear in the second query.
An intersection combination returns the data common to bothqueries.
A union combination takes the all the data from both queries,eliminates duplicate rows, and builds a combined data set
The individual queries in a combined query must have thesame structure.
You can include more than 2 queries in a combined query
What youve learned so far
Formulas
Display results based on a formula you create
-
7/30/2019 advanced data analyzer techniques.pdf
37/39
6/24/20
3
Variable Editor
The Variable Editor enables you to build your own dimensionsbased on other dimensions, formulas and operators
Takes the first 5characters of
each zip code
To learn how to use Functions
Click on thefunction name
Descriptionprovides info
about therequired syntax Provides a
detailed definitionof the function
To Build a Formula
1. From the Report, click the
Variable Editor
2. In the Variable Editor, giveyour expression a Name.Then build your formula,using the Functions,Operators and Dataobjects required. Click thegreen checkmark tovalidate your formula.
3. Click Ok to return to thereport. Then drag theexpression from the Datatab onto your report.
-
7/30/2019 advanced data analyzer techniques.pdf
38/39
6/24/20
3
Formula Demo
Create a Service Unit Roster from the
Committee Universe that includes Troop-level and SU-level positions grouped undereach Service Unit.
Why do we need a formula?
The Problem: Only Troop-level positions show a Service UnitName in the Committee Hierarchy folder. SU-level positionsshow a blank. We need to show the service units of bothpositions in one column in the report.
Service Unit-levelposition
(Service Unit is theLabel Name)
Troop-levelposition
(Service Unit is thehierarchy)
What formula do we need?
The Solution: Create a formula based on this condition:
If the position is held on a Troop, show the Service Unit Name from theCommittee Hierarchy folder, else
Show the Label Name of the committee itself.
-
7/30/2019 advanced data analyzer techniques.pdf
39/39
6/24/20
Checkpoint #3
To can build your own expressions in your reports based onformulas. Formulas can consist of report objects, functionsand operators.
The If function can help you display the results you want in acolumn based on a condition.
What youve learned so far
Data AnalyzerResources
Data Dictionaries on CES Support
eLearning on CES Support
Web Intelligence User Guide
http://www.csdw.status.dhhs.state.nc.us/userguides/WebIntelligenceUserGuide.pdf
Web Intelligence XI and XI Release 2
http://www.doa.louisiana.gov/ois/Adhoc/Busines
s%20Objects/Webi%20XI%20R2_calculation_changes.pdf
Q&A
Thank you for attending!
http://www.csdw.status.dhhs.state.nc.us/userguides/WebIntelligenceUserGuide.pdfhttp://www.csdw.status.dhhs.state.nc.us/userguides/WebIntelligenceUserGuide.pdfhttp://www.doa.louisiana.gov/ois/Adhoc/Business%20Objects/Webi%20XI%20R2_calculation_changes.pdfhttp://www.doa.louisiana.gov/ois/Adhoc/Business%20Objects/Webi%20XI%20R2_calculation_changes.pdfhttp://www.doa.louisiana.gov/ois/Adhoc/Business%20Objects/Webi%20XI%20R2_calculation_changes.pdfhttp://www.doa.louisiana.gov/ois/Adhoc/Business%20Objects/Webi%20XI%20R2_calculation_changes.pdfhttp://www.doa.louisiana.gov/ois/Adhoc/Business%20Objects/Webi%20XI%20R2_calculation_changes.pdfhttp://www.doa.louisiana.gov/ois/Adhoc/Business%20Objects/Webi%20XI%20R2_calculation_changes.pdfhttp://www.csdw.status.dhhs.state.nc.us/userguides/WebIntelligenceUserGuide.pdfhttp://www.csdw.status.dhhs.state.nc.us/userguides/WebIntelligenceUserGuide.pdf