DWH News

As Mark had pointed out earlier here, one of the significant changes in the BI EE 11g release is the migration of security from the BI EE repository to Weblogic. Users and Groups will no more be maintained within the rpd. Even in an external authentication like LDAP, Groups will not be maintained within the repository. This is good in many ways as it can significantly reduce the size of the repository. The downside though is, in offline mode unless there is a change in any security object (like custom filters to a group etc), the users & groups will not become visible. The use of Web Catalog Groups is no more recommended. It still exists primarily for supporting upgrades. In this blog post, lets see how the new weblogic based security works.

There is a terminology change that we need to get used to while moving to BI EE 11g. Groups now refer to Weblogic groups. BI EE specific groups are called as Application Roles. The figure shown below explains the relationship between Users, Groups & Application Roles.

Weblogic security is much more comprehensive and it can support multiple external directories like LDAP, OID etc. It also supports table authentication. So, if you are planning on setting up external authentication, its preferred to set it up at the Weblogic layer. We can also setup external directories within BI EE (like in 10g).

To start with lets create a simple user bieeuser1 in the Weblogic Console Security Realm (http://localhost:7001/console).

While creating a user we can assign it to a provider. A provider is the authentication directory against which users will be authenticated. In our case, we have created the user in the default weblogic directory. In the same way lets create 2 more users bieerpdadmin & bieewebcatadmin.

The idea is to provide the following privileges to each of the users that we have created

bieeuser1 – Casual user with no access to the repository but with read only access to dashboards (no answers)
bieerpdadmin – Repository Administrator with full control of the Repository objects but with no control on the web catalog.
bieewebcatadmin – Web Catalog Administrator with full control on the web catalog but with no access to the repository

Remember, its not always necessary to assign users to weblogic groups. Users can be directly assigned to application roles. Once the users are created, lets login to the Weblogic enterprise manager and navigate to the coreapplication under the Business Intelligence folder.

Now, right click on the coreapplication and choose the Security->Application Roles option

This should list all the default application roles available within BI EE.

Let’s neglect the existing roles(ideally we will be reusing/reassigning the default roles as much as possible) and create 3 new roles.

1. BIEERoleUser
2. BIEERoleRPDAdmin
3. BIEERoleWebCatAdmin

For now lets not add any users/roles to these roles.

Lets now navigate to the Application Policies and start granting specific rights/grants to the roles that we created. Remember in any ADF application we need to define application policies so that they can be granted to individual users. We start with assigning the following privileges to the BIEERoleUser

1. resourceType=oracle.bi.server.permission,resourceName=oracle.bi.server.queryUserPopulation

This basically provides an ability to query the BI Server repository.

Lets assign this privilege to the BIEERoleUser application role.

In the same way lets assign the following privileges to the BIEERoleRPDAdmin and BIEERoleWebCatAdmin

For BIEERoleRPDAdmin

1. resourceType=oracle.bi.server.permission,resourceName=oracle.bi.server.ManageRepositories

For BIEERoleWebCatAdmin

1. resourceType=oracle.bi.server.permission,resourceName=oracle.bi.server.queryUserPopulation

Now that the roles have the right privileges, lets go back to the Application Role screen and start assigning the 3 users to each of these roles.

After assigning these, if you try to login to the repository in online mode, only the user bieerpdadmin can login. We have satisfied one requirement, that is we have created a user who is the repository admin. For presentation services, by default during install the privileges in presentation services get assigned to only 3 default groups BIAdministrator, BIAuthor and BIConsumer. So, to make bieewebcatadmin a true webcatalog administrator we have to go to the Application Roles and add the BIAuthor & BIAdministrator role to BIEERoleWebCatAdmin

This will automatically make the user bieewebcatadmin user to be the administrator of the webcatalog. Initially it is mandatory to assign a new role to the BIAdministrator role as that is pre-configured to be the only role having access to Privileges in Presentation Services. Granting BIAdministrator to a role also opens up the access to the repository. This can be disabled as well. It is not necessary to always create new roles and assign policies to them. Existing roles can be re-used and re-assigned. The entire security model is now more aligned with Oracle Platform Security framework so that all the fusion applications can integrate with each other seamlessly.

iDashbaord shares a sample manufacturing dashboard with Dashboard Insight. Leading manufacturers have converted their data assets into effective business intelligence. Other slides in this interactive dashboard show Production levels, logistics information, and financial information.

Attunity Ltd., a leading provider of real-time data integration and event capture software, announces that its Attunity Stream 5.3 real-time and low-impact change data capture (CDC) solution has achieved certified integration with SAP® BusinessObjects™ solutions. Attunity also announced its participation in the SAP EcoHub solution marketplace, which makes it easier for customers to discover, evaluate and purchase trusted solutions from partners that complement SAP applications.

In the digital media era, marketing has shifted from fuzzy brand building to highly targeted and measurable efforts including Web and e-mail campaigns, paid search and, increasingly, social media initiatives.

To deliver on the promise of measurability, software-as-a-service-based business intelligence vendor myDials on Tuesday launched a Performance Module for Marketing designed to help marketing executives spend their budgets more wisely.

The myDials BI service provides a foundation of dashboard interfaces that can be linked with myriad data sources and set to user-customizable views and drill-through analyses. Customers can also set up goals, thresholds and exception alerts whereby users can spot exception conditions such as revenue shortfalls.

The growing success of cloud computing is driving every organization in every industry to understand how the cloud affects their business. Cloud, as a topic of discussion, has moved beyond developers and IT organizations and into the boardroom of the world’s most prestigious companies such as NASA, Eli Lilly, and 3M – all of which are leveraging the cloud in some way. While debate rages about the benefits that cloud computing delivers, few topics at present are debated as hotly as cloud security.

A new study released today by The Information Difference, an independent analyst firm, finds that data governance is growing faster than previously forecast and is increasingly seen as a prerequisite for effective master data management (MDM) and data quality programs. The study also identified a well-defined need for purpose-built data governance solutions to replace an assortment of ad hoc tools currently in use.

FortiusOne, the leading location-based analysis software provider, today announced Frank Moyer as Chief Executive Officer.  Frank joins FortiusOne following his successful leadership at EzGov, a global technology company acquired by CACI in 2009.  Bringing over twenty years experience, Frank comes to FortiusOne to extend the company’s enterprise reach and further solidify GeoIQ as the market leading location analysis platform.

Frank most recently served as Chief Executive Officer of EzGov from 2005 to 2009, where he grew the company into a highly profitable global services business that was sold to CACI in 2009.  Prior to EzGov, Frank served as an executive with Accenture’s Technology Practice Group, where he worked with numerous Fortune 500 companies.

Stonefield Software has introduced its newest version of business intelligence (BI) and reporting software Query Version 4.0 for Sage Accpac. The new version eliminates IT bottlenecks by creating a self-service reporting offering for the casual user.

The company said that the version 4.0 for Accpac provides a new 'application view', which makes creating ad hoc queries and custom reports much simpler, and enables users to see the data in the same way as it is presented in the application.

IBM and DePaul University’s College of Computing and Digital Media have announced a new Center for Data Mining and Predictive Analytics that is intended to give students expertise in predictive analytics. As part of the program, DePaul is also offering students a Masters of Science in Predictive Analytics.

One of the important points to consider when moving from BI EE 10g to 11g is in understanding the BI Server behavior changes. Though there are quite a lot of changes in the underlying code for performance improvement etc, introduction of new features in 11g can potentially alter your generated queries in 11g. In this blog entry i will basically cover 2 main important changes that have been introduced in 11g. There are more actually, but that is something we will try to cover in our training days event.

1. Level Based Measures at Detail Level:

One significant change in 11g is the handling of level based measures at the detail level. For example, lets consider a measure AMOUNT_SOLD that is assigned to the detail day level of the date dimension.

In 10g, whenever we bring this column for reporting, we would basically see something as shown below i.e. the level will always be enforced.

In the case of 11g, there are 2 types of behavior

a. If the report contains normal attribute columns and when the query is at a grain higher than the measure grain (say the report is at the Year level), then normal aggregation will be applied and the level will not be enforced like in 10g. An example screenshot is given below

b. If the report contains hierarchical columns, then if the query is at higher grain than the measure, the measure values will become null. The values will appear at that grain or below. An example screenshot is given below

2. Priority Groups & Order of LTS:

In 11g, there is a new concept called Priority Groups. In 10g, the LTS that determines the final query is dependent on the ordering of the LTS in the dimension logical table(in most cases – but there are cases where we cannot be always be sure to guarantee this behavior). But in 11g, this is determined by the priority order set in each and every LTS.

For example, lets consider a case where there are 2 aliases for the same dimension CHANNELS as shown below

In the business model and mapping layer, lets assume that both the LTS are mapped to all the columns. Both the LTS are at the same detail level. Now, lets assign a priority group of 0 for the Channel Alias – B LTS and a priority group of 1 for the Channel Alias – A LTS. Also the SALES LTS of the Fact table will also be assigned to the priority group 0. In 11g, the LTS with the least number is considered as the one that has to be chosen for querying (highest priority)

Now, lets create a report containing columns from Channels dimension and the Sales fact.

If you look at the query of the report, you will notice that Channels Alias – B will be chosen as it is the one having the highest priority.

Lets now go back to the repository and reassign the highest the priority to Channels Alias – A as shown below

For the same report, if you look at the query now, you will notice that Channels Alias – A will be chosen instead of the B alias.

Visual Mining, a leader in business intelligence dashboard and data visualization solutions, today announced that American Exteriors, LLC selected Visual Mining’s NetCharts Performance Dashboards to analyze and track performance for executive management, as well as the organization’s sales, operations and finance divisions.

Despite the buzz surrounding the importance of managing data and the use of data warehouses, mid-market companies have historically been left out of the conversation. But no organization, regardless of size, can afford to ignore this reality of the business world.

Companies that lag behind frequently suffer from a lack of visibility into their customers, supply chain, products and services. They can’t clearly see how well they perform as an organization and against their competitors. Without proper structures in place, accessing the information required to understand these challenges proves difficult at best. Addressing business pain points requires a data management infrastructure.

The Importance of Social Media and Business Intelligence Integration. Social media is now increasingly used to not only project marketing influence, but also to collect important demographic and consumer data based on interactions that occur across the various platforms. As this social internet continues to evolve, social media interactions are more easily collected into systems of metrics than can parse, store and interpret this interaction data into actionable market intelligence. Coupled with Business Intelligence, this makes for increasingly powerful, actionable and immediate access to consumer sentiment.

Data visualization pioneer Corda has been awarded a patent for "Best Image Fallback" -- a pivotal technology that optimizes the display of images and graphics in web browsers and on mobile devices.

The new patent protects an innovation that plays a vital role in data visualization solutions, which translate raw data into rich graphics and performance dashboards. Using Best Image Fallback, Corda software products can automatically detect and display the best image type, such as Flash, PNG or SVG, that a given web browser or mobile device can support. The result is the best possible viewing experience for users.

Mark has already explained the new Parent-Child hierarchy new feature in 11g here. Parent Child hierarchies are different from normal level based hierarchies not only in the nature of their structure but also in the way aggregations are done. For example, in a level based hierarchy, the most common type of aggregation that is done involves rolling up the numbers from the lower most level. But in the case of parent-child hierarchy, an aggregation by default enforces the roll-up across all descendant members. To understand this lets try to take this through an example.

Lets consider a sales department with the employee hierarchy shown below

Its a simple parent child employee hierarchy found in the oracle HR schema. Now the requirement for us is to model the Salary attribute of an employee in 3 ways

1. As an attribute – Show the salary of each employee as an attribute in the parent child hierarchy.
2. As a measure (All intermediary Roll-ups) – Roll-up the salary figures of all the employees who roll into the employee. For example, Scott King will have a Salary equal to sum of salaries of all the employees who report to him, including the ones who are his direct & in-direct reporting employees.
3. As a measure (Level-0 Roll-up) – Roll-up the salary figures from the lower most level till the top node For example, in the above figure, Scott King will have a Salary Roll-up figure of all the employees who are the lowermost level (not including the intermediary nodes – very similar to normal level based hierarchy roll-ups)

The above requirement is quite straightforward, but to achieve similar such aggregations in a normal level-based hierarchy, we will have introduce fragmentation & other techniques. But with parent-child hierarchy modeling now available in BI EE, all the above 3 requirements can quite easily be achieved.

Following is the employees parent child table that stores all the attributes of an employee

We start off with modeling this as a normal dimension table. As shown in Mark’s blog entry, we then create the Parent-Child table that stores all the relationships within the employee hierarchy (if you look at the script used in creating this, it will be containing a Connect By statement that will connect a root hierarchical node to every member that is part of its hierarchy).

Once this is created, we start with including the Salary as an attribute within the Employee dimension itself

This will serve our first requirement i.e show salary as an attribute of every employee. Now, to model the 2nd requirement i.e. to model salary as a measure and then make it to rollup from all the intermediary hierarchical members, we start with defining an alias on the main Employee table. Then join this table to the custom parent child employee table. The join structure of all the 3 tables is given below

We would then be using the salary column from the new employee alias table in our logical fact table as shown below

Lets now look at the report now and see what BI EE generates.

As you see, BI EE has generated the correct results with the roll-up of intermediary members as well. This is something that is quite difficult to model in BI EE 10g. If we look at the query generated (just the key one that generates the measure as BI EE now generates lots of intermediary sub-queries to support contextual drilling) you would notice that the default Parent-Child behavior is to achieve a roll-up across a hierarchy

To achieve the 3rd requirement i.e. to model a level based hierarchy kind of rollups from level-0, we create another alias in the physical layer and then model it the same way as the 2nd requirement. In the fact logical table, we then create a new measure that will map to the new physical alias as shown below

To ensure that we roll-up only from level-0 members, we then apply a filter in the fact logical table source as shown below

Lets now take a look at what BI EE generates

As you see, BI EE now generates the roll-ups only from level-0 members as against every descendant. We can have even more granular roll-ups based on custom defined attributes.