BI Appliances

What is a BI Appliance?

If a data warehouse class database product or a reporting product or a data integration product or an all-in-one software package is pre installed and available in a preconfigured hardware box, then such a “hardware + software” box is called a ‘Business Intelligence'  Appliance’. The very purpose of an appliance model is to cover the underlying software components complexity and intricacies and make it simple like operating a TV system.

How an Appliance Model evolved?

As businesses gathered huge data, the demand for faster and better ways of analyzing data increased, the data warehouse as a software technology got evolved; there have been continuous efforts to build software systems that are cognizant of data warehouse environments.

We have seen IBM and Oracle releasing their data warehouse specific database editions

We seen the growth of data warehouse specific databases like RedBrick(now part of IBM), Teradata, Greenplum…
We have seen simple list reporting tools getting into proprietary data structures cubes and the emergence of acronyms MOLAP, HOLAP, ROLAP, DOLAP
We had a very new software market created for ETL and EII products
We have seen more new software applications related to BI being created BAM, CPM, Metadata Management, Data Discovery and lot more getting defined every day into the market….

As many organizations started setting up its BI infrastructure or enhanced its existing BI environment with different BI software packages they needed, they also imbibed different platforms and hardware, the maintenance of these became frightening. Getting started with a BI project by itself became a bigger project; we needed to spend sufficient time not just on choosing the right set of BI products but also on the supported hardware, dependent software packages and the platform. No BI vendor currently addresses the complete stack of BI system needs and this has been the driving factor for more acquisitions.

Products like Nettezza (Data base Appliance), CastIron (ETL Appliance) came up with their ‘software in a box’ concept, where we can buy or rent preconfigured ‘hardware + software’ boxes which in a way addresses the need of ‘ready to use’ BI market. Many of these boxes have Linux, open source databases, web server, message queues and proprietary software.

The Appliance based model is not new, IBM has been renting its ‘mainframe + software’ for decades. IBM has addressed the BI market with its ‘Balanced Warehouse’; a preconfigured ‘hardware + software’, its OS can vary from Windows – AIX – Linux with DB2 as database and data reporting can vary from DB2 Cubes – Crystal – Business Objects. HP in a similar way has come out with its Neoview platform which is a revitalized version of NonStop SQL database and NonStop OS.

The need of a CIO has been always ways to shorten the application deployment cycle and reduce the maintenance factor of the servers; the Appliance based products meet these KRA of a CIO and are getting accepted widely.

The Future

More Appliances, Focus on Performance:
We would see more BI appliances coming into market; as the Appliance model covers what’s underneath and in many cases the details being not available; the buying focus would be more on what the products deliver rather than what they have inside.

Common Appliance Standards:
Getting best of breed of software and hardware from a single vendor would not happen. We might see both software and hardware vendors defining a set of basic standards among themselves for the Appliance model. New organizations would also evolve similar to “tpc.org” which would define performance standards for appliances. We might see companies similar to DELL coming up which can assemble best of breed components and deliver a packaged BI Appliance.

More Acquisitions: The current  Business Intelligence Market landscape can also be interpreted as

1. Hardware + Software or Appliance based vendors – HP, IBM
2. Pure software or Non-Appliance based vendors – Oracle, Microsoft, SAP
3. 
   

Once the current BI software consolidation gets established the next wave of consolidation would be towards companies like Oracle looking for hardware companies to be added to their portfolio.

Technology Appliance Products Database Netezza Teradata DATAllegro Dataupia Data Integration CASTIron Reporting-Dashboard Cognos NOW (Celequest LAVA) Configurable Stack (with third party support) IBM Balanced Warehouse HP Neoview

Getting Current and Previous EFFDTed Rows

Almost in every project, this is one of the requirements to get the Current and Previous EFFDTed rows (from JOB table).
Most of the times, we will use the Self Join SQL to get the same.

SELECT

            A.EMPLID, A.EFFDT CURR_EFFDT, B.EFFDT PREV_EFFDT

FROM   PS_JOB A, PS_JOB B

WHERE

            B.EFFDT =(SELECT MAX(A_ED1.EFFDT) FROM PS_JOB A_ED1

                        WHERE B.EMPLID = A_ED1.EMPLID

                        AND B.EMPL_RCD = A_ED1.EMPL_RCD

                        AND A_ED1.EFFDT < A.EFFDT)

            AND B.EFFSEQ =(SELECT MAX(A_ES1.EFFSEQ) FROM PS_JOB A_ES1

                                WHERE B.EMPLID = A_ES1.EMPLID

                                AND B.EMPL_RCD = A_ES1.EMPL_RCD

                                AND B.EFFDT = A_ES1.EFFDT)

            AND A.EMPLID = B.EMPLID

            AND A.EMPLID=’KA0002′

Is this the only way to get the results?????

Here is the Simple and Advanced way to achieve the same results without using Self Join

SELECT

            A.EMPLID, A.EFFDT CURR_EFFDT, LAG (A.EFFDT, 1, NULL) OVER (ORDER BY EFFDT) PREV_EFFDT

FROM   PS_JOB A

WHERE            A.EMPLID=’KA0002′

Like this, we can also get the Current and Following EFFDTed rows by using LEAD Function

SELECT

A.EMPLID, A.EFFDT CURR_EFFDT, LEAD (A.EFFDT, 1, NULL) OVER (ORDER BY EFFDT) LEFFDT

FROM PS_JOB A

WHERE A.EMPLID=’KA0002′

Note: Above SQL holds good for Oracle DB. In Oracle, The Function LAG/LEAD is called Analytic Function, which is there right from Oracle 8i onwards.

Read More About  EFFDTed Rows

Fine Grained Access Control for PeopleSoft Database – II

previous post…

 Here are the steps to implement the Fine Grained Access Control feature to mimic the row-level security in your PeopleSoft online Query Manager.

Step 1:

We will create a function QRY_SEC_FUNCTION that will be used by the policy to add the filter.

create or replace function QRY_SEC_FUNCTION (schema_name IN varchar2,

table_name IN VARCHAR2)

return varchar2

as

V_OPRID VARCHAR2(32);

V_emplid varchar2(20);

V_CLIENT_INFO VARCHAR2(1000);

V_QRYSECRECNAME VARCHAR2(32);

V_SQL_TEXT VARCHAR2(4000);

V_TABLE_NAME VARCHAR2(32);

begin

V_CLIENT_INFO := SYS_CONTEXT(‘USERENV’,'CLIENT_INFO’);

V_OPRID := SUBSTR(V_CLIENT_INFO,1,INSTR(V_CLIENT_INFO,’,',1)-1);

V_TABLE_NAME := TABLE_NAME;

If V_OPRID is null then

V_SQL_TEXT := ‘EXISTS (SELECT ”X” FROM PS_PERS_SRCH_QRY A1 WHERE A1.EMPLID = ‘||V_TABLE_NAME||’.EMPLID AND A1.OPRID = USER)’;

else

V_SQL_TEXT := ‘1=1′;

end if;

RETURN V_SQL_TEXT;

exception

when others then

return ‘1=1′;

end;

/

Step 2:
Now, we will create the policy

begin

dbms_rls.add_policy

( object_name => ‘PS_PERSONAL_DATA’,

policy_name => ‘PERSONAL_DATA_POLICY’,

policy_function => ‘QRY_SEC_FUNCTION’,

statement_types => ’select’,

update_check => TRUE );

end;

That’s it!!
Now let’s test the result…

As we can see above, now our results from the database match the results from online query. The function has dynamically added the additional criteria similar to what was done by Query Manager.

Summary:
In today’s world, it has become critical to ensure that there are no security loopholes in the system that will expose data to people who should not be seeing them. Row-level security provided by PeopleSoft helps us secure online access and we most often forget that users setup in the database can by-pass this security and have access to all the data. FGAC helps us replicate the online row level security in the database thus helping us further secure the database.

Read More About  Fine Grained Access Control

“What Management Is” – The crucial link between Business and Intelligence

Let’s for a moment accept the hypothesis that the true intent of Business Intelligence is to help organizations manage their business better. “Better” in this context tends to be a rather elastic adjective as it straddles the entire spectrum of firms using BI for simple management reporting to the other extreme of using BI to ‘Compete on Analytics’ in the marketplace.

“Managing business better” presents the classic question of “What aspects of business can BI help manage better”. The Answer – “Pretty much everything”.

In this post, I would like to list down the different business areas that ought to be managed for the better and drill down into the applicability of BI for each of these areas in future posts. The primary reference for my listing is from one of the best management books I have ever read till date – “What Management Is” by Joan Magretta and Nan Stone. (http://www.amazon.com/What-Management-Works-Everyones-Business/dp/0743203186). This book really helps in drawing the boundaries around management concepts and for BI practitioners, like me, shows the direction for the evolution and business applicability of BI.

BI practitioners need to understand the following business areas:

1. Value Creation – BI can help in providing the critical “Outside-in” perspective
2. 
   
3. Business Model – Is this the right business to be in?
4. 
   
5. Strategy – Validation and tuning of Strategy thro’ BIsiness Intelligence
6. 
   
7. Organization Boundaries – BI can help solve the Build vs Buy conundrum
8. 
   
9. Numbers in Business – Really the sweetspot for BI applications
10. 
    
11. Mission and Measures – Connecting the company’s mission with the measures
12. 
    
13. Innovation and Uncertainty – Domain of Predictive Analytics & its ilk
14. 
    
15. Focus – Realm of Pareto’s Law vis-à-vis the more recent “Long-Tail” phenomenon
16. 
    
17. Managing People – Human Resource Analytics is one of the most happening analytics application areas at this point in time.

Bit of marketing here – after all, this is a corporate blog – My company Hexaware is a specialty provider of HR Analytics solutions. Please do visit –http://www.hexaware.com/new_hranalytic.htm for more information

To me, the list above presents the most comprehensive high-level thought process when confronted with implementation of BI in organizations. In my consulting engagements, the litmus test is to really see whether the BI strategy covers the different aspects of business as noted above 

– “More the coverage better is the BI vision”.

Information Nugget

I was quite fascinated by the range of analytical apps available “On-Demand” at http://www.salesforce.com/appexchange/category_list.jsp?NavCode__c=a0130000006P6IoAAK-1 . I personally feel that “On-Demand” does have the potential to disruptively change the way BI services have been delivered customers. More on that later!

The crucial link between Business and Intelligence

Have a Merry Christmas and a Happy New Year 2008!

How can we have DYNAMIC Prompt table for Record Fields?

Most of the times we would have faced a requirement of having Dynamic prompt table for Record Fields. Also PeopleSoft itself very much utilizes Dynamic Prompt table. How can we achieve the same in our project????

Here we go

This is accomplished by 2 ways

1. Using EDITTABLE fields for Prompt Table
2. Using Dynamic Views
3.  

1. Usage of EDITTABLE Fields

In this method, Prompt table property of Record Fields should be assigned with %EDITTABLE value. Actually what does it mean????

Prompt Table value for the Record Field is populated from the Record Field DERIVED.EDITTABLE value. The Record Field DERIVED.EDITTABLE should be assigned with value either in one of the Peoplecode events (Either in FieldChange or FieldEdit or RowInit events). This is simply done by

DERIVED.EDITTABLE = “PERSON_NAME”;

Note: EDITTABLE Field should be present in the SAME Page, where Record Field (In this case, The Field is nothing but EMPLID) is also referred. If the Record Field is not coming under Component Search Record Field, there is no need for placing the EDITTABLE field in the Page.

2. Usage of Dynamic Views

As we know, while creating Dynamic view, there is no need to specify the SQL. This SQL should be generated dynamically and the same should be assigned to the Record Field.

Say for example, if we see the above Record Field TASK_PROFILE_ID, it is assigned with dynamic prompt table view TL_TSKGRP_DVW and this view TL_TSKGRP_DVW is dynamically initialized by the following Peoplecode, which can be assigned both in FieldChange and RowInit events of the Record Field TASK_PROFILE_ID.

RECORDNAME.TASK_PROFILE_ID.SqlText = “SELECT T.TASKGROUP, T.TASK_PROFILE_ID, T.DESCR FROM PS_TL_TSKGRP_PRF_W T WHERE T.TASKGROUP = ‘” | &TSKGRP | “‘ AND T.EFFDT =(SELECT MAX(T1.EFFDT) FROM PS_TL_TSKGRP_PRF_W T1 WHERE T1.TASKGROUP = T.TASKGROUP AND T1.TASK_PROFILE_ID=T.TASK_PROFILE_ID AND T1.EFFDT<= %datein( ‘” | &maxdate | “‘) )”;

Both &TSKGRP, &maxdate are dynamic bind parameters.

Read More About  DYNAMIC Prompt Table