DWH News

Like many managers, Michael Lambert needed to accomplish more without spending more. The technology manager at nonprofit Compassion International figured that an increase in headcount was unlikely, so he considered how data analysts could offload routine but vital reports that consumed 80 percent of their time. He found SAS® Business Analytics.

Realizing that many reports resembled each other, Lambert sought to provide internal users with self-service reporting capabilities. SAS Business Analytics frees his analysis team to concentrate on work that yields more intelligence and benefit to the 58-year-old child advocacy organization.

InetSoft Technology, an innovator in dashboard, reporting and mashup solutions, announced that SUNRx has selected InetSoft’s Style Scope as its business intelligence solution for internal use.

“We were impressed with the visual appeal and ease-of-use of the interactive dashboard software,” asserted John Giolli, CFO at SUNRx. “InetSoft was easy to deal with and has a number of flexible license options on its products to choose from.”

IDBS, the leading worldwide provider of data management and analytics solutions to R&D and healthcare organizations, today announced that it has concluded an agreement to acquire Quantrix, an award-winning company that provides business modeling and analytics software and services. As part of the cash acquisition, Quantrix will continue to operate as an independent business unit from its headquarters in Portland, Maine, USA.

Radically backtracking from its earlier stance on the open-source community, software giant Microsoft has now admitted that its earlier hostility was a mistake. Almost a decade ago, company CEO Steve Ballmer called the open source Linux operating system a "cancer" where its IP was concerned; Microsoft has also accused open source providers of violating hundreds of its patents in recent years.

In a recent interview with Network World, Jean Paoli, the general manager of Microsoft's interoperability strategy team explained that the mistake has to do with equating Linux with all open source technology. "We understand our mistake, he says," and pointing out that the various work that Microsoft has since done with open source, he quipped, "We love open source."

Data warehouse and analytic appliances provider Netezza has reported revenues of $64m for the second quarter of fiscal 2011, an increase of 45% compared to $44m for the same period last year.

Revenue from products grew to $47.3m from $30m year-over-year, while services revenue grew to $16.5m from $14m for the same period last year.

As mentioned in the blog entry here, BI Server 11g now supports more functions which were added to support the new UI features. Each of these functions can be used as part of the Logical SQL or can even be used directly in the Answers UI. In this blog entry we shall be looking at how some of these functions can be used.

1. SELECT_PHYSICAL: Though this is not a function, this is a new feature supported by BI Server that can bypass the BMM layer in the SQL queries. So, any SQL that uses SELECT_PHYSICAL will be more closer to the ANSI SQL standard. There are 2 main advantages of this

a. Any external reporting tools that generate SQL automatically can now leverage the functionality of BI Server. For example, in the blog entry here i had shown the difficulty in actually using Logical SQL with Cognos. But introduction of SELECT_PHYSICAL should alleviate this issue thereby making BI Server open for better integration externally.

b. Using SELECT_PHYSICAL one can now make direct joins across data sources without actually having the need to model them separately in the Business Model and Mapping layer.

With the addition of more in-memory operations in the BI Server, this should act as a point of integration for external tools that have the need to leverage the metadata framework of BI EE.

To illustrate how this works, lets look at the repository shown below,

As you see, the above repository contains all the relevant tables in the SH schema. Now, to use the SELECT_PHYSICAL command, lets go to the Issue SQL section of the BI EE and fire the SQL given below

Remember, though this is a SELECT_PHYSICAL SQL statement, BI Server will still do the parsing as the objects will have to reside inside the repository.

This option can be enabled at the ODBC/JDBC level as well. That way the DSN can be configured to just accept SELECT_PHYSICAL or SELECT commands.

I will cover more details of how this can be leveraged from external tools like QlikView, Cognos etc in a future blog entry.

2. AGGREGATE AT:

This function adds more multi-level analysis capabilities within the BI Server. This function provides the same functionality as the Level Based Measures. What this does is, it provides an ability to extract the values of a parent level in a query without having to SUM up the values in the lower level. To illustrate, lets look at the simple report below

Now, what we need is an ability to display the value of yearly sales alongside the quarterly sales. To do that, we need to enter the formula as shown below

If we look at the report now, we should see that we can compare the Yearly sales along the Quarterly sales i.e. the AGGREGATE AT function will not respect the filter on that dimension. If you want the filters to be honored as well then the older SUM BY functions can still be used.

If you look at the Physical SQL generated, you would actually notice 2 separate WITH clauses (minimum). One for the Sales figure. And the other for the Yearly Sales figure. The Yearly Sales figure will not have the filter applied.

3. CALCULATEDMEMBER:

This function is used to derive calculated members using members at any level of a hierarchy. In 10g, we can do this from a pivot table. In 11g, this function has been introduced at the BI Server level itself so that we can leverage these directly from the repository.

To illustrate this, consider the hierarchy shown below

In this hierarchy, we want to find out the difference between Steven King’s Salary and his 2 direct reportees (Neena Kochhar and Lex De Haan). To do this, we need to use a function given below

This would push the function directly on the parent-child hierarchy and give us the correct values. This function cannot be used if there are other attributes of the same dimension in the report. This is generally used by external application tools. But within BI EE Answers, there is no need for using this as we can achieve the same using Custom Groups and Calculations.

4. EVALUATE_ANALYTIC:

This is another significant new feature that can be useful to push down the oracle database analytic functions. I have covered EVALUATE number of times before for function shipping database functions. EVALUATE_ANALYTIC extends that further and provides us an ability to function ship the analytic functions as well. The syntax of this function remains the same. I will cover this in detail in another blog post as there are quite a few use cases where EVALUATE_ANALYTIC can prove to be very useful.

5. HIERARCHICAL FUNCTIONS:

BI Server now supports hierarchical functions like ISCHILD, ISROOT, ISANCESTOR, ISDESCENDANT & ISLEAF that can be used to traverse a Parent-Child hierarchy. These functions generally return a boolean data type (true/false) and hence can be used in filters to restrict data. They can also be used in column formula case statements.

For example, in a hierarchy if we want to know whether a specific member is a leaf node or not, we can use the below function to display Yes/No as an attribute of the parent-child hierarchy

Next up is some interesting posts on the BI EE integration with Fusion Middleware & Essbase. I will be doing these posts towards the end of next week.

Earlier this week I had an analyst briefing with AVS (Advanced Visual Systems) to learn about their OpenViz solution offering.  Basically, AVS offers customers analysis through building visual applications based on the principles of data visualization.  Some applications mirror typical dashboard solutions, while others represent real-time process flows and the monitoring of business efficiencies. Overall, based on the vendor offerings in the market, this shows that the dashboard market moves in two directions. The first being the developer intensive data visualization solutions and the second being off-the-shelf consumer dashboards.

Earlier this week I had an analyst briefing with AVS (Advanced Visual Systems) to learn about their OpenViz solution offering.  Basically, AVS offers customers analysis through building visual applications based on the principles of data visualization.  Some applications mirror typical dashboard solutions, while others represent real-time process flows and the monitoring of business efficiencies. Overall, based on the vendor offerings in the market, this shows that the dashboard market moves in two directions. The first being the developer intensive data visualization solutions and the second being off-the-shelf consumer dashboards.

Pentaho Corporation, the open source business intelligence (BI) and data integration leader, today announced it has named Doug Johnson as the company's new executive vice president and chief financial officer. Mr. Johnson brings over 20 years of experience in finance and operations at technology companies including Fusion Systems, Discreet Logic, THINQ Learning Solutions and Black Duck Software. He will have responsibility for all global finance and administration operations and will play a vital role in guiding Pentaho's rapid growth and leadership in the Business Intelligence market.

Infosys Technologies has launched Finacle analyz, an offering that claims to helps banks analyse customer behaviour and take customer-centric decisions.

The company said that its new offering enables holistic customer analysis for banks with insightful business reports, executive dashboards and statistical models.

One-third of technology executives and business professionals are unware if their organisation utilised business analytics or had capabilities for business analytics, according to a new webcast poll conducted by Deloitte.

In the survey conducted with more than 1,900 technology executives and business professionals, 33% of the respondent did not know if their organisation utilised business analytics, or even if they had business analytics capabilities at all.