Your quarterly dose of business intelligence tips & tricks - Issue 2 - Q3, 2006
   

Siebel Analytics tips & tricks:

Configuring LDAP for Siebel Analytics

Siebel Analytics 7.8 – Enhanced functionality with “Bins”

Learn more about Data Load Statistics for Siebel Analytics

More tips & tricks:

Business Objects

Oracle

ClearPeaks

 


Contact ClearPeaks:

ClearPeaks (headquarters)
Valencia 266 3, 1
08007 Barcelona, Spain
Tel +34 93 272 1546

ClearPeaks
Plaza Peixateries Velles 3, 2
43003 Tarragona, Spain
Tel +34 97 725 0778

 

Editor
Anoek Aukema

Contributors
Alex Caminals
Victor Castellá
Victor Cutilla
Emma Gorgori
Moïse Kameni
Jordi Ricart
Albert Seco

 

Configuring LDAP for Siebel Analytics
Author: Moïse Kameni

The Scenario

This document presents the steps to follow in order to have an existing enterprise Lightweight Directory Access Protocol (LDAP) integrated with the Siebel Analytics reporting tool (Enforcing all the rules, security restrictions, etc. already defined within the organization).

The Procedure

Step1:
Open the Siebel Analytics RPD File and, preferably stop the Siebel Analytics Service to avoid long delays for saving.



Step2:
From Manage Menu click on Security. This screen will appear.




Step3:
Create a new LDAP Server. The Test connection button can make sure that the connection is established.




Step4:
The Advanced Screen tab requires no changes.




Step5:
By clicking on the Test Button the following Message box appears. This shows LDAP is working fine.
In Instance specific cases, the “Base DN” parameter (General Tab) needs to be changed accordingly.



Step 6:
Return to Manage Menu and click on Variables.




Step 7:
In the Session Variable, select the USER variable. Double-click it.



Step 8:
The following message box will appear. Since LDAP Authentication will be used, changes need to be made for the correct Initialization block to point to LDAP.
Create a new Initialization Block by clicking on the “NEW” Button.




Step 9:
Enter a Name such as “LDAP” in this example.
In the Data Source Connection, choose “LDAP”.
As displayed at the bottom, the LDAP Server is still “Not Assigned”.




Step 10:
Click on the “Browse” button, choose the Server that was created in the earlier screen. If this no servers are displayed here, a new LDAP Server can also be created from here.



Step 11:
Select the Server from the above screen and the following message box should appear.
Click OK to save your changes.
Restart the Analytics Service.
Check the Analytics Screens from the Siebel Application.



Step 12:
Launch the Siebel Analytics Web application and confirm the setup by logging in with your LDAP credentials.

Go to top

Siebel Analytics 7.8 – Enhanced functionality with “Bins”
Author: Emma Gorgori

With the release of Siebel Analytics 7.8 a powerful new feature has been incorporated – “Bins”. This feature allows the user to group results of an object in a certain way. The Bin functionality works like a “CASE WHEN” condition, but with the difference that this can be built on the front-end by the end user whereas normal CASE WHEN statements are built in the repository through the Administration tool.

How does it work?
The Bin functionality is part of the Edit Formula available in the Siebel Analytics Request Column Buttons.
In the Criteria tab, the user selects the column that shall be used to create bins. By using the Edit Formula button the user then selects the tab called Bins and the Add Bin option at the bottom.







A pop up window will appear with a similar screen to the one used for adding filters. In this screen, the user will add the condition that the values have to follow to be grouped. See example below:





The condition for the bin is defined in the same way as a filter condition.





Once the condition is set up, the user will have to give a name to the Bin. See below:




The user can create as many bins as required. Once every bin has a name, there is an optional feature to give a name to the rest of the values that are not grouped.



Working with Bins
Once one or more Bins are created, they can be combined with any other column. If the other columns are facts, the bin will force an aggregation of the metric.



Charts and any other functionality available in Siebel Answers can be used as usual with a column that has a bin.
In the example below, a table groups the different customer names based on their name. This Customer column is made up of bins and next to it is a metric from the same subject area that can be combined with the bin column with no problem. Also, a pie chart next to the table, shows the table results graphically.


Warning for Siebel Analytics 7.8.2 users
The Bins functionality has given certain errors with the Siebel Analytics 7.8.2 version. In a situation where bins are displayed in a pivot format table, and some columns are in the “excluded” section of the pivot table, it might be that the Bin value does not aggregate properly for this excluded column.
A solution for this situation is avoided using the Pivot table view and having one or more excluded columns.

Conclusion
Bins provide the power user with access to Siebel Answers with the ability to group one or more variables in different ways depending on his needs. This avoids the dependency on the Administrator to build the grouping within the repository. This powerful function should be used whenever the required bin is too user-specific to warrant the grouping in a new object in the repository.




Go to top

Learn more about Data Load Statistics for Siebel Analytics
Author: Victor Castellá

Introduction
Database statistics are considered a very important task to improve query performance. This article gives an overview and tips to provide a better understanding of Data Load Statistics (DLS). Statistics are usually updated on a daily, weekly or longer scale, depending on database activity (inserts/deletes) and the configuration specified by Database Administrator (DBA). The Data Warehouse Application Console (DAC) updates statistics during the extract, tranform and load (ETL) processes.

Analyze Tables
The task that updates the statistics in the DAC is named Analyze Tables. DAC allows enabling or disabling this functionality through the System Properties panel. See the parameter in the following screenshot.




The DAC updates the statistics of the Data Warehouse while the ETL processes are executed. Every time a dimension or a fact table is loaded, DAC launches a task that refers to the table loaded and updates the statistics.

For tables where the data load is significant, the Analyze Table task may take a lot of time to execute. The efficiency of this task also depends on the specifications of the hardware where the Data Warehouse (DW) database is located. If the DW is Oracle, it is possible to modify parameters within the DAC setup to make the statistics creation process more efficient.

SQL command
Once executed, the Analyze Tables task launches a SQL command against the database. The DAC obtains this SQL command from a XML file, which means that it can be modified.

The XML file is located in the following folder:

X:\SiebelAnalytics\DAC\CustomSQLs\customsql.xml

The entries related to the statistics are the following:

<SqlQuery name = "ORACLE_ANALYZE_TABLE" STORED_PROCEDURE = "TRUE">
DBMS_STATS.GATHER_TABLE_STATS(ownname => '@TABLEOWNER', tabname => '%1', estimate_percent => 30, method_opt => 'FOR ALL COLUMNS SIZE AUTO',cascade => true )
</SqlQuery>

<SqlQuery name = "ORACLE_ANALYZE_TABLE_70" STORED_PROCEDURE = "FALSE">
ANALYZE TABLE %1 COMPUTE STATISTICS
</SqlQuery>

<SqlQuery name = "MSSQL_ANALYZE_TABLE">
UPDATE STATISTICS %1
</SqlQuery>

<SqlQuery name = "DB2_ANALYZE_TABLE" STORED_PROCEDURE = "TRUE">
SIEBSTAT ('@TABLEOWNER', '%1', 'SQL_STATS_ALL')
</SqlQuery>

For Oracle databases, DAC uses the GATHER_TABLE_STATS, which is part of the package DBMS_STATS. The method used by default requires a lot of resources to execute the statistics, as it generates statistics using histogram models. This method generates the most complete statistics, although in the case huge volumes of data in the Data Warehouse, this process can exceed the maximum times established to execute the ETLs.

TIP: The parameter estimate_percent defines the amount of data that is going to be analyzed. By reducing this value, the Analyze Table task will take less time. This is a trick that can be very useful, especially when facing very long ETL processes.

SQL example
<SqlQuery name = "ORACLE_ANALYZE_TABLE" STORED_PROCEDURE = "TRUE">
DBMS_STATS.GATHER_TABLE_STATS(ownname => '@TABLEOWNER', tabname => '%1', estimate_percent => 10, method_opt => 'FOR ALL COLUMNS SIZE AUTO',cascade => true )
</SqlQuery>

An alternative to reduce the ETL processes is to disable the Analyze Tables option (as mentioned before). Once the DW is loaded, the statistics can be manually executed against the database.

Conclusion
As conclusion we know that the DAC is updating the statistics while the ETL are executing. It can enlarge too much the ETL processes in big databases. In the case of Oracle where the statistics are more complex, we know now that there is the option to reduce the percentatge of data to be analyzed. Finally there is also the option to disble the Analyze Tables functionality from the DAC application and then launch this manually when the ETL has finished.


Go to top