when analysis is better using web intelligence than excel
Post on 14-Apr-2018
214 Views
Preview:
TRANSCRIPT
7/29/2019 When Analysis is Better Using Web Intelligence Than Excel
http://slidepdf.com/reader/full/when-analysis-is-better-using-web-intelligence-than-excel 1/14
When analysis is better using Web Intelligence
than Excel
This article compares data analysis using Web Intelligence to the same workflow using
Microsoft Excel and for the given scenario demonstrates why using Web Intelligence is
quicker, easier and less error prone.
The objective here is not to show that in all cases Web Intelligence is better than Excel
rather to highlight that there are some situations where it is preferable to use Web
Intelligence. Equally there are some situations where Excel is better than Web Intelligence,
for example, calculating the geometric mean is easier in Excel as we can use a built in
formula whereas in Web Intelligence we have to calculate the geometric mean manually.
Business Scenario
First let us consider he following typical business scenarios,
The head of account management is looking at reassigning clients to account
managers in her team. To do so she first wants to get a list of accounts that Bob has
dealt with that Sarah has also dealt with.
A marketing analyst would like to get a list of products that are no longer in the top100 this month that were top 100 last month.
Also from marketing analysis we want to review customer segmentation. We want toknow how many of our customers are in the age group 20 to 25 and how many livein a large city and how many own a car. We also want to know how many fall intoeach of these three segments.
Each of these scenarios are simple enough to understand but doing the performing the
analysis can get quite complex as in each case we are having to perform set analysis. Set
analysis is all about identifying one group of customers, products, clients etc and then
seeing how this set compares to another set.
In the first scenario above the two sets are Sarah’s clients and Bob’s clients and we want to
see what clients in one set belong to another. In the second scenario one set is the top one
hundred products this month and the second set is the top one hundred products last
7/29/2019 When Analysis is Better Using Web Intelligence Than Excel
http://slidepdf.com/reader/full/when-analysis-is-better-using-web-intelligence-than-excel 2/14
month. And in the last scenario we have three sets: customers aged 20 to 25, customers
who live in a large city and customers who own a car.
Set operations are often represented as Venn diagrams where the two circles represent
each set and the different areas represent the different relationships. The first scenario is
displayed as a Venn diagram in figure 1 below.
Figure 1. Venn diagram displaying the relation between Sarah's and Bob's clients
and the third scenario above is represented by this Venn diagram,
Figure 2. Venn diagram of scenario three display relation between three sets
Almost all leading databases support set operations and we can use Web Intelligence to
make use of these to perform our set analysis. Microsoft Excel however is not particularly
good at set operation, although it can be done with a bit of hacking around.
Let us look at how Web Intelligence compares to Excel for this type of analysis.
Set Analysis using ExcelLet us look at how we might perform the first scenario in a spreadsheet. Consider a
spreadsheet where we have two columns: a list of Sarah’s Clients and a list of Bob’s Clients
and we want to produce a list of clients that belong to both Bob and Sarah.
7/29/2019 When Analysis is Better Using Web Intelligence Than Excel
http://slidepdf.com/reader/full/when-analysis-is-better-using-web-intelligence-than-excel 3/14
Figure 3. Screenshot of Excel spreadsheet display three lists of Sarah's clients, Bob's clients and clients
of both Bob and Sarah
We can of course just visually inspect the two lists and can spot which clients are common
to both for example “Initech” and “Roboto Industries” are in both lists. We could then just
write down the clients common to both. This is fine for a small list such as above but not so
practical if the lists contains hundreds or thousands of entries.
For longer lists we can make use of the vlookup function.
Set Analysis in Excel using the vlookup function
Beside the two columns of data we add a third column and enter the formula,
=VLOOKUP(C3,B$3:B$21,1,FALSE)
and copy this down over all rows. This will give a result similar to the screenshot below
7/29/2019 When Analysis is Better Using Web Intelligence Than Excel
http://slidepdf.com/reader/full/when-analysis-is-better-using-web-intelligence-than-excel 4/14
Figure 4. Excel spreadsheet using vlookup function to identify entries common to both lists
This is effectively taking a value from Bob’s list (range C3 to C21) and then looking up this
value in Sarah’s list (range B3 to B21). When it finds a match it returns the value otherwise
it returns an “#N/A” error.
So we have now identified a list of clients common to both lists however all these “#N/A”
statements are a bit ugly but we can hide these by updating our formula to,
=IF(NOT(ISERROR(VLOOKUP(C3,B$3:B$21,1,FALSE))),C3,"")
We now have a list of clients common to both Bob and Sarah but we still have blank rows
for when there is a client in common. To remove these we can use the filter function to hid
the blank rows. However this also affects the original lists so not ideal but we can copy
values of the final list to another spreadsheet.
As we can see it is not impossible to identify which clients are common to both Bob and
Sarah using Excel but it isn’t particularly straightforward. Furthermore if we have large data
sets or we are trying to analyse three or more sets of data then this method becomes quite
time consuming and is prone to copy/paste mistakes and other user errors.
Let us now look how we can perform the same analysis using Web Intelligence
Set Analysis using Web Intelligence
We have a universe that we can use to get a list of clients that an account manager has had
contact with so first we’ll create a query that lists Sarah’s clients.
7/29/2019 When Analysis is Better Using Web Intelligence Than Excel
http://slidepdf.com/reader/full/when-analysis-is-better-using-web-intelligence-than-excel 5/14
Figure 5. Web Intelligence query that will return a list of clients for Sarah
And this produces a simple table listing Sarah’s clients. We now edit the query and click the
set analysis icon to create a second query, here we update the query filter to now list Bob’s
clients. We also change the set operator from “union” to “intersection” and run the query.
For more information on this feature see “Using combined queries” in the Web Intelligence
user guide “Building Reports with the SAP BusinessObjects Web Intelligence Java Report
Panel”.
Figure 6. Web Intelligence query using set analysis feature to perform an intersection of two queries
This then produces a list of clients that both Sarah and Bob have had contact with,
Figure 7. Web Intelligence report displaying result of intersection query
As can been seen this is much quicker and easier than hacking the data in Excel. In addition
we can easily change the set operator to the minus set operator to determine which clients
Sarah has had contact with that Bob has not.
7/29/2019 When Analysis is Better Using Web Intelligence Than Excel
http://slidepdf.com/reader/full/when-analysis-is-better-using-web-intelligence-than-excel 6/14
Is this the only way of doing set analysis in Web Intelligence?
Well it is certainly the easiest, the other option is to bring both sets of data into the report
and then analyse the data there however Web Intelligence doesn’t provide any set analysis
features or formula so it is a little tricky but it is still possible.
Report side set analysis
Here we create two separate queries and then integrate the result in the report. This could
be useful if we want to display a list of Sarah’s clients, a list of Bob’s clients and a list of
clients common to both. Let us look at how we might do this.
We create a report that contains two queries. Each query is the same as shown in figure 5
above where in the Result Objects we have client name and in the Query Filters we have
“Employee Name equals Sarah” in one and “Employee Name equals Bob” in the other query.
When we run this report Web Intelligence will by default enable the merge dimension
feature to integrate the results of the two queries and the screenshot below shows the
result. For more information on merged dimensions see the chapter “Merging dimensions
from multiple data providers” in the Web Intelligence user guide.
Figure 8. Result of using two queries and using the merge dimension feature to integrate the result in
the report
The report displayed above in figure 8 is the result from this query and here we are
displaying three columns. The first displays the result set of the query returning Sarah’s
7/29/2019 When Analysis is Better Using Web Intelligence Than Excel
http://slidepdf.com/reader/full/when-analysis-is-better-using-web-intelligence-than-excel 7/14
Clients, the other Bob’s clients and in the middle we have the result of the merged
dimension.
When merging these two queries Web Intelligence is performing a union of data from both
queries (in SQL language a full outer join). What we want however is an intersection of
these two data sets (or normal join in SQL).
Unfortunately there isn’t any feature where we can control how Web Intelligence merges the
result sets, for example instructing Web Intelligence to use an intersection or indeed a
minus set operation rather than a union. However there is a workaround.
We add a new column to the middle table and enter the following formula,
=Count([SarahsClients].[Client Name]; All)
This returns 1 when the client belongs to the set of Sarah’s clients otherwise it returns zero.
We then update this to,
=Count([SarahsClients].[Client Name]; All) + Count([BobsClients].[Client
Name]; All)
And this returns a 2 when the client exists in both Sarah’s and Bob’s client list.
Figure 9. Report updated with a formula to determine which clients are common to both sets
Finally we create a new variable of this formula which we can then use to filter the table to
only display the clients common to both Sarah and Bob.
7/29/2019 When Analysis is Better Using Web Intelligence Than Excel
http://slidepdf.com/reader/full/when-analysis-is-better-using-web-intelligence-than-excel 8/14
Figure 10. Final report with middle table filtered to display clients in common to both sets
Conclusion
Above has shown that both Excel and Web Intelligence can perform Set Analysis. In Excel it
requires a reasonable amount of manual intervention and the use of the vlookup function to
establish a list of members common to both sets. This manual effort is time consuming and
is prone to user errors.
In Web Intelligence the easiest method is to use the set analysis feature of the query to
return our desired set. We also saw that it is possible to perform the same analysis in the
report through use of a relatively simple formula although it would be better if we could
specify that the merge dimension feature should use an intersection or minus when merging
two data providers.
So it is reasonable to conclude that set analysis is best performed using Web Intelligence.
This article isn’t trying to show that in all cases you must use Web Intelligence instead of
Excel but is aiming to highlight that as an analyst you should know both products and be
able to decide which tool is best for a given scenario.
Tuesday, June 1, 2010 by Al Gulland
7/29/2019 When Analysis is Better Using Web Intelligence Than Excel
http://slidepdf.com/reader/full/when-analysis-is-better-using-web-intelligence-than-excel 9/14
1. One Response to “When analysis is better using
Web Intelligence than Excel”
2.
nice post. thanks.
By cna training on Jun 2, 2010
You must be logged in to post a comment.
Search
Share This Article
Article Categories
Applications (1)
Business Intelligence (20)
Delivery Methodology (5) How To (14) Software (1)
Statistical Analysis (9) Technology (31) Database Technology (6)
IBM DB2 (1)
Oracle (1) SQL Server (3) SAP Business Objects (27)
Developer Suite (2) Information Delivery (4) Information Management(3)
SAP Business Objects Enterprise (7)
SAP Data Services (4) Universe Design (6)
Web Intelligence (12)
Uncategorized (1)
Meta
Register Log in
Entries RSS
7/29/2019 When Analysis is Better Using Web Intelligence Than Excel
http://slidepdf.com/reader/full/when-analysis-is-better-using-web-intelligence-than-excel 10/14
Comments RSS WordPress.org
WordPress | Entries (RSS) | Comments (RSS)
When analysis is better using Web Intelligence
than Excel
This article compares data analysis using Web Intelligence to the same workflow using
Microsoft Excel and for the given scenario demonstrates why using Web Intelligence is
quicker, easier and less error prone.
The objective here is not to show that in all cases Web Intelligence is better than Excel
rather to highlight that there are some situations where it is preferable to use Web
Intelligence. Equally there are some situations where Excel is better than Web Intelligence,
for example, calculating the geometric mean is easier in Excel as we can use a built in
formula whereas in Web Intelligence we have to calculate the geometric mean manually.
Business Scenario
First let us consider he following typical business scenarios,
The head of account management is looking at reassigning clients to account
managers in her team. To do so she first wants to get a list of accounts that Bob has
dealt with that Sarah has also dealt with. A marketing analyst would like to get a list of products that are no longer in the top
100 this month that were top 100 last month.
Also from marketing analysis we want to review customer segmentation. We want to
know how many of our customers are in the age group 20 to 25 and how many livein a large city and how many own a car. We also want to know how many fall intoeach of these three segments.
Each of these scenarios are simple enough to understand but doing the performing the
analysis can get quite complex as in each case we are having to perform set analysis. Set
analysis is all about identifying one group of customers, products, clients etc and then
seeing how this set compares to another set.
In the first scenario above the two sets are Sarah’s clients and Bob’s clients and we want to
see what clients in one set belong to another. In the second scenario one set is the top one
hundred products this month and the second set is the top one hundred products last
7/29/2019 When Analysis is Better Using Web Intelligence Than Excel
http://slidepdf.com/reader/full/when-analysis-is-better-using-web-intelligence-than-excel 11/14
month. And in the last scenario we have three sets: customers aged 20 to 25, customers
who live in a large city and customers who own a car.
Set operations are often represented as Venn diagrams where the two circles represent
each set and the different areas represent the different relationships. The first scenario is
displayed as a Venn diagram in figure 1 below.
Figure 1. Venn diagram displaying the relation between Sarah's and Bob's clients
and the third scenario above is represented by this Venn diagram,
Figure 2. Venn diagram of scenario three display relation between three sets
Almost all leading databases support set operations and we can use Web Intelligence to
make use of these to perform our set analysis. Microsoft Excel however is not particularly
good at set operation, although it can be done with a bit of hacking around.
Let us look at how Web Intelligence compares to Excel for this type of analysis.
Set Analysis using ExcelLet us look at how we might perform the first scenario in a spreadsheet. Consider a
spreadsheet where we have two columns: a list of Sarah’s Clients and a list of Bob’s Clients
and we want to produce a list of clients that belong to both Bob and Sarah.
7/29/2019 When Analysis is Better Using Web Intelligence Than Excel
http://slidepdf.com/reader/full/when-analysis-is-better-using-web-intelligence-than-excel 12/14
Figure 3. Screenshot of Excel spreadsheet display three lists of Sarah's clients, Bob's clients and clients
of both Bob and Sarah
We can of course just visually inspect the two lists and can spot which clients are common
to both for example “Initech” and “Roboto Industries” are in both lists. We could then just
write down the clients common to both. This is fine for a small list such as above but not so
practical if the lists contains hundreds or thousands of entries.
For longer lists we can make use of the vlookup function.
Set Analysis in Excel using the vlookup function
Beside the two columns of data we add a third column and enter the formula,
=VLOOKUP(C3,B$3:B$21,1,FALSE)
and copy this down over all rows. This will give a result similar to the screenshot below
7/29/2019 When Analysis is Better Using Web Intelligence Than Excel
http://slidepdf.com/reader/full/when-analysis-is-better-using-web-intelligence-than-excel 13/14
Figure 4. Excel spreadsheet using vlookup function to identify entries common to both lists
This is effectively taking a value from Bob’s list (range C3 to C21) and then looking up this
value in Sarah’s list (range B3 to B21). When it finds a match it returns the value otherwise
it returns an “#N/A” error.
So we have now identified a list of clients common to both lists however all these “#N/A”
statements are a bit ugly but we can hide these by updating our formula to,
=IF(NOT(ISERROR(VLOOKUP(C3,B$3:B$21,1,FALSE))),C3,"")
We now have a list of clients common to both Bob and Sarah but we still have blank rows
for when there is a client in common. To remove these we can use the filter function to hid
the blank rows. However this also affects the original lists so not ideal but we can copy
values of the final list to another spreadsheet.
As we can see it is not impossible to identify which clients are common to both Bob and
Sarah using Excel but it isn’t particularly straightforward. Furthermore if we have large data
sets or we are trying to analyse three or more sets of data then this method becomes quite
time consuming and is prone to copy/paste mistakes and other user errors.
Let us now look how we can perform the same analysis using Web Intelligence
Set Analysis using Web Intelligence
We have a universe that we can use to get a list of clients that an account manager has had
contact with so first we’ll create a query that lists Sarah’s clients.
7/29/2019 When Analysis is Better Using Web Intelligence Than Excel
http://slidepdf.com/reader/full/when-analysis-is-better-using-web-intelligence-than-excel 14/14
- See more at: http://www.gulland.com/wp/?p=386#sthash.BvdlwVP0.dpuf
top related