Thursday 31 May 2012

OBIEE 11g6: Getting Top-N Sales Reps for Year and Year-1


The report that is being generated will have the top 10 "Sales Rep Name" who have made the most business in the current year.
The other part to this report is that, we want to compare their performance to the previous years business that these top 10 "Sales Reps" have brought in.
We would also like to compare their ranks in the previous year.


  1. So first lets create a fact measure "Year Ago Revenue" which we can use to show in our report as below in the 5th column.



  2. Open the Administration Tool, then select the blue "Open Online" folder. Enter the Repository Password (BISAMPLE1) and the weblogic administrator's Password (e.g. Administrator1)



  3. Under the "Business Model and Mapping" Layer, right click the "F1 Revenue" and select "New Object" -> "Logical Column..."



  4. In the "Logical Column" Window give for Name: Year Ago Revenue as seen below:



  5. Goto the "Column Source" Tab and select "Derived from existing columns using an expression" radio button then click on the "Edit Expression" icon.
  6. Enter the following expression in the "Expression Builder" Window by selecting  "Functions" in the "Category:" list, "Time Series Functions" in the "Functions:" list then "Ago" "Time Series Functions:" section.
  7. Select the "<<Measure>>" in the "Ago( )" function, then select "Logical Tables" in the "Category:" list, "F1 Revenue" in the "Logical Tables:" list, then double click the "Revenue" in the "Columns:" list.
  8. Select the "<<Level>>" in the "Ago( )" function, then select "Time Dimensions" in the "Category" list, "H1 Time" in the "Time Dimensions:" list, then double click the "Year" in the "H1 Time:" list.
  9. Select the "<<Number of Periods>>" and enter 1.
  10. Make sure the below expression is created in the right text box:  Ago("Sample Sales"."F1 Revenue"."Revenue" ,  "Sample Sales"."H1 Time"."Year" , 1)



  11. Click OK to close the expression builder and OK again in the "Logical Column - Year Ago Revenue" window so the new logical column can be seen.
  12. Drag the "Year Ago Revenue" from the BMM layer to the Presentation layer as show below:



  13. In order to show the "Revenue Rank" at the Year level and NOT the combined years level.
  14. Double click the "Revenue Rank" measure under the "F1 Revenue" Fact table in the Business Model and Mapping layer and make sure the Rank("Sample Sales"."F1 Revenue"."Revenue" expression shows in the "Derived from existing columns using an expression" box.




  15. Click the "Levels" tab and and change the "Logical Level" for the "H1 Time" to "Year".
  16. Click OK to close the "Logical Column" window for the "Revenue Rank".



  17. Now save the changes with File -> Save and click OK to test the global consistency and check in the changes to the online repository.
  18. Now goto your Enterprise Manager Console and restart your BI services by clicking on the Restart button for your "Business Intelligence" -> coreapplication instance




  19. Now go back to your https://localhost:9704/analytics answers web page and create a report with the following columns:
    "Per Name Year", "Sales Rep Name", Revenue, Revenue, "Year Ago Revenue", "Revenue Rank".
    Select the "Per Name Year" options and choose the Sort -> "Sort Descending" to sort this column values in the descending order.
    Select the 4th "Revenue" Column options and select the "Edit formula" option to open the "Edit Column Forumla" window.
    Select the "f(...)" and select Rank function under the Functions -> Aggregate section and click OK to close the window.



  20. Now select the 6th "Year Ago Revenue" Column options and select the "Edit formula" to open the "Edit Column Formula" window.
  21. Select the "f(...)" button the Rank function under the Functions -> Aggregate section.
  22. Click OK to close the "Edit Column Formula" window.
  23. Select the 4th "Revenue" column options and select Sort -> "Sort Ascending". Note this is the second sorted column after the first "Per Year Name".
  24. The below screenshot shows the final modified columns :



  25. Click the Results tab to see the below results:



  26. Click the Save Icon to save the newly created analysis under a new folder "Sample Sales" under the "Shared Folders" as show below:
  27. Name the file: "Top 10 Sales Rep and Rank"



    Our Next task is to get the top 10 "Sales Rep Name" entries instead of all the member rows.

  28. Now go back to the "Criteria" Tab and select the 4th "RANK(Revenue)" Column options and select "Edit formula".
  29. In the "Edit Column Formula" Window select the "Custom Headings" checkbox and enter "OVERALL RANK(Revenue)" next to the "Column Heading".
  30. Click OK to close the "Edit Column Formula" window.



  31. Select the Results tab and expand the "Selection Steps" section by clicking on the "+" next to it.
  32. Under the "Sales Rep - Sales Rep Name" section, click the "2. Then, New Step..." link -> "Apply a Condition" to open the "New Condition Step" window.
  33. Select the "Condition Type" as "Top 10 based on X,
  34. For "Action" select "Keep only".
  35. For "Operator" select "is top".
  36. For "Rank" enter 10 and make sure the % checkbox is de-selected.
  37. For "Measure" select "Base Facts"."Revenue" field.
  38. Click OK to create the new step.



  39. You should now be able to see only the top 10 Sales Reps for each ear.
  40. Notice that "RANK(Revenue)" is the rank over all years and "Revenue Rank" is a rank on a year wise basis.



    Our Next task is to setup a Presentation variable so the user can select which years to report on.
  41. Goto the "Catalog" Tab and Edit under the "Top 10 Sales Rep and Rank" report.
  42. After you get the report select the Prompts tab:
  43. In the page click the Green addition symbol to add a new prompt, then select "column prompt" -> "Time"."Per Name Year"
  44. In the "New Prompt:" window let there be defaults and
  45. Select "* Prompt User" for the "Operator" option.
    Select "Choice List" for the "User Input" option.
    Select the checkbox for "Include *All Column Values* choice in the list"
    Select the checkbox for "Enable user to select multiple values".
    Select the checkbox for "Require user input" option.
    For the "Set a variable" select "Presentation Variable" and enter "YearData" as show below:



  46. Click OK and save the analysis by clicking on the save icon.



  47. Select the "Criteria" Tab and select the "Per Name Year" options and select "Filter".
  48. In the "New Filter" window select the "Add More Options" -> "Presentation Variable".
  49. For the "Variable Expr" enter "YearData" and the for the "Default" enter "2010".
  50. Click OK to close the "New Filter" window.



  51. Now the Filters section should like the below:




  52. Click the "Results" Tab then the  "Show how the results will look on a Dashboard icon    to view the results with the new created prompt.
    Select the 2009 and 2010 checkboxes and click on the white space are to see the OK button.



  53. Click the OK button to see your results with the user selected year values:


Summary:
As you can see we have a "Year Ago Revenue" measure in our RPD to view the "Sales Reps" previous year business revenue and rank to compare with current year business and rank.
Then we created "Selection Step" criteria to only get the top 10 "Sales Reps"
Then we created a pompt on "Per Name Year" which updates a presentation variable which can be used for your "Per Name Year" filter.

OBIEE 11g6: Getting Top-N Sales Reps Using Results Selection Steps

  1. Select the three columns "Sales Rep Name", Revenue, Revenue to show in your Results.



  2. Click on the third column "Revenue" options and select "Edit Formula"
  3. On the"Edit Column Formula" window insert the function "f(...)" under Functions -> Aggregate -> Rank and select OK, compare with below screenshot:



  4. Click on the third column "Revenue" options and select Sort -> "Sort Ascending" and make sure your "Selected Columns" is as below:



  5. Click the "Results" tab and your output should look like the below showing each "Sales Rep Name" with their equivalent accomplished "Revenue" and Rank.



  6. At the bottom of the "Results" window select the "+" to expand the "Selection Steps" Window



  7. In the "Selection Steps" Window select option 2. under the "Sales Rep - Sales Rep Name".
    Select the link "Then, New Step" and then the  "Apply a Condition" option



  8. In the "New Condition Step" windows select the "Top 10 based on X"  for the Condition Type.
    for the Action select "Keep only"
    for the Operator select "is top"
    for the Rank enter "10" and make sure the % check box is de-selected.
    for the Measure make sure it shows "Base Facts"."Revenue" column.



  9. After verifying that the above values are proper click the OK button.
    You table should then show the Top 10 Sales Representatives only for all the years existing in your database.




  10. In the above screenshot you have achieved the Top-10 ranks using another method after data is retrieved by using the "Selection Steps" process.

Summary:
This is another way to show who all are the Top-10 Sales Representatives making the most business for your company. We have achieved this by using the "Selection Steps" window after the data is being retrieved into the resultant table.

Tuesday 29 May 2012

OBIEE 11g6: Getting Top-N Sales Reps Using the TOP-N Function


  1. Select the three columns "Sales Rep Name",  Revenue, TOPN(Revenue, 10) to show in your Results.



  2. On the Third "Revenue" Column open the option and choose "Edit Formula"
  3. On the Edit Column Formula window options and select "Edit Formula"
  4. On the Column Formula select the "Insert Function" f(...) and choose the TopN under the Aggregate section.
  5. Select the "integer" and enter 10.



  6. Make sure the following statement is as shown below and click OK.



  7. On the second "Revenue" Column choose options and select Sort -> Sort Descending.



  8. Then Select the Results Tab to show the Top 10 Sales Rep for all the existing years in your database.



  9. To Verify that the TOPN(Revenue, 10) Column is using the proper TOP-N  functionality, create two columns with just "Sales Rep Name" and Revenue and put a sort option on "Revenue" column as below:



  10. Goto the Results Tab to view the results



  11. In the above screenshot you can verify that the first 10 columns are the same that you have got while applying the Top-N Function on the Third Revenue Column.
Summary:
This is one way to show who all are the Top-10 Sales Representatives making the most business for your company. We have achieved this by using the Top-N function applied on the "Revenue Column". Another way to achieve this by using the "Selection Steps" on "Results" Tab after our results have been shown.


Wednesday 9 May 2012

Setting up OBIEE11g Admin Tool for ODBC Access

When you see the "The connection has failed" error in OBIEE 11g Admin tool, you would need to follow the below steps to overcome this, atleast
this what was done on Windows 7:


  1. Copy the tnsnames.ora from the Oracle's Database location to the OBIEE 11g Install location for e.g.:  Oracle_HOME_LOCATION: C:\app\sripen\product\11.2.0\dbhome_1\NETWORK\ADMIN to OBIEE_11g_LOCATION: C:\OracleFMW\oracle_common\network\admin
  2. Open up Control Panel --> System and Security --> System --> Advance System Settings --> Advanced --> Enviroment Variables and add the following in the System Variables section:
    Variable Name: TNS_ADMIN
    Variable Value: C:\OracleFMW\oracle_common\network\admin
  3. Then logoff the current user and login again.
  4. Now try connecting using the Connection Type as OCI 10g/11g and give the corresponding values to see if it will connect to the database.


While using other tools for importing follow the above procedures as well this below:

  1. Update the user.cmd or user.sh file depending on your Windows or Unix OS as seen below:
For Windows:

set TNS_ADMIN=C:\OracleFMW\oracle_common\network\admin

For Unix:

TNS_ADMIN=/home/sripen/OracleFMWoracle_common/network/admin
export TNS_ADMIN


The user.cmd file can be found under for e.g.: C:\OracleFMW\instances\instance1\bifoundation\OracleBIApplication\coreapplication\setup

What is Indexing in a Database

You keep wondering why you need to set those indexes on databases columns?

Well here is a little insight into that.

Lets say you boss comes up and gives you a bunch of resumes for some 100 candidates.
What is the first thing you will do?
Well atleast I will get a cabinet and put them in folders with  some thing like Last Names from A-D in one folder, E-H in another folder etc.


Ok that work is done.
My boss comes back and says he want all the candidates with OBIEE experience.
Not that easy, but I will try coming up with an easy way so next time I don't shuffle through all the 100 resumes again.

I will have Card separators in each folder i.e Lets say the A-D folder I will arrange the A-D Last name candidates grouping them there technology experience.

So Next time my boss comes up  and asks me for the list of  candidats with Open-Systems experience, I just have to go through each main folder labeled with A-D, E-H etc and pull out the bunch of resumes under the section Open-Systems or OBIEE which ever he asks  for.
So instead of searching through 100 resumes I just need to search under 6 Folders and pull out the relevant technology resume bunch.

So that is  how indexing works in databases to improve the overall performance of  SQL Queries.
Next time when you are asked to index, now you know that you should be indexing on
LastName and Technology columns instead of  Age or other irrelevant columns which does not help the SQL Queries.

Exception Occuring During OBIEE 11.1.1.5.0 Install



While installing OBIEE 11g and it reaches the place where it's trying to create WLS Domain and fails with below in the log files:

oracle.as.provisioning.exception.ASProvWorkflowException: Problem invoking WLST - java.io.FileNotFoundException: C:\DOCUME


Then you need to the below and restart your installation over again:

  1. Create a new directory called  TEMP in a different location for ex: C:\TEMP
  2. Now, Right click on My computer --> Properties --> Advanced --> click on Environment Variables.
  3. There will be TEMP and TMP as variables under user variables section pointing to the C:\Documents and Settings\user_name\Local Settings\Temp path.
  4. Edit the TEMP and TMP variables and mention C:\TEMP in the variable value.
  5. Deinstall and re-install OBIEE 11g.

Unable to Login BI Publisher 10.1.3.4.0 issue on WinXP

Basically the file needs to be updated with the XDO instead of BI_SERVER as below:
C:\OracleBI\xmlp\XMLP\Admin\Configuration\xmlp-server-config.xml

<xmlpconfig xmlns="http://xmlns.oracle.com/oxp/xmlp">
<property name="SAW_SERVER" value="your-14980964f2">
<property name="SAW_SESSION_TIMEOUT" value="90">
<property name="DEBUG_LEVEL" value="exception">
<property name="SAW_PORT" value="9704">
<property name="SAW_PASSWORD" value="Administrator">
<property name="SAW_PROTOCOL" value="http">
<property name="SAW_VERSION" value="v4">
<property name="SAW_USERNAME" value="Administrator">
<property name="SAW_URL_SUFFIX" value="analytics/saw.dll">
<property name="SECURITY_MODEL" value="XDO">
<property name="BI_SERVER_SECURITY_DRIVER" value="oracle.bi.jdbc.AnaJdbcDriver">
<property name="BI_SERVER_SECURITY_URL" value="jdbc:oraclebi://your-14980964f2:9703/">
<property name="BI_SERVER_SECURITY_ADMIN_USERNAME" value="Administrator">
<property name="BI_SERVER_SECURITY_ADMIN_PASSWORD" value="Administrator">
</property></property></property></property></property></property></property></property></property></property></property></property></property></property></xmlpconfig>

Now you can login at:
http://your-14980964f2:9704/xmlpserver/login.jsp
with Administrator/Administrator as username/password.

Normalizing for OLTP and OLAP Databases

Its easy to figure out which tables are better for OLAP databases by
just looking at the size and simplicity of your SQL Query.

If the SQL Query has to many joins then for sure you sql engine is
going to be doing a lot of reads/fetches from different tables.

If the SQL Query has simple select statements with just WHERE
clauses that is the one you want to use to design your OLAP tables.



In OLAP repeated attribute values are acceptable but for OLTP
databases, it means to many redundant rows filling up your table.

In OLAP where and when ever there is to much redundant and un-unique
rows its time to start thinking of Normalization as below:



1st Normal Form

  1. Every row should be unique :- Achive this with setting primary
    key.
  2. No Repeating Groups


Supplier



Col Name

Data Type

Allow Nulls

PK

supplierid

int

NO



suppliername varchar(50) NO













Product



Col Name

Data Type

Allow Nulls



productid

int

NO



productname varchar(50) NO



supplierid

int





2nd Normal Form

  1. This has to be in complient with 1st Normarl Form.
  2. There should be no partial key dependencies
  3. All the Attributes must be dependant on the key




Products



Col Name

Data Type

Allow Nulls



productid

int

NO



productname varchar(50) NO



store

varchar(50)

NO



price

money

NO



  1. We will have multiple redundant entries for a similar
    productid,productname and price in different stores combination.
    Hence this is not in 1st Normal Form.
  2. We will need to remove the store and give it a seperate table
    as below:




Products



Col Name

Data Type

Allow Nulls



productid

int

NO



productname varchar(50) NO



price

money

NO





Stores



Col Name

Data Type

Allow Nulls



storeid

int

NO



storename varchar(50) NO



productname

varchar(50)

NO



  1. Hence we have broken it down so that store has its own storeid
    with storename and its productname
  2. In 2nd Normal Form its ok to have productname duplicated in
    two tables as above which is ok till 2nd normal form but doesn't
    conform to 3rd Normal Form

  3. This good in OLAP databases but it causes redundancy hence
    still need to be removed and normalized more for 3rd Normal
    Form.



3rd Normal Form

  1. This has to be in complient with 1st Normarl Form.
  2. There should be no partial key dependencies
  3. In 3rd Normal Form all the attributes must be *solely*
    dependant on the key


Products



Col Name

Data Type

Allow Nulls



productid

int

NO



productname varchar(50) NO



price

money

NO



Stores



Col Name

Data Type

Allow Nulls



storeid

int

NO



storename varchar(50) NO













Inventory



Col Name

Data Type

Allow Nulls



storeid

int

NO



productid char(10) NO











  • The Above tables have been normalized for OLTP databases which
    is good only for OLTP transactions.
  • When coming to OLAP databases the 2nd Normal Form tables are
    better suited with the redundant productname columns, since it
    makes for simpler query statements.

To Configure BI Scheduler on Windows

To get OBIEE "Oracle BI Scheduler" to work follow the below steps "D:\OracleBI\server\Bin\NQScheduler.exe" /service
On the "Job Manager" Application Console:-
  1. Choose File --> Configuration Options --> Scheduler Tab --> Database subtab, enter the following:
    • Connection Pool
      • Database Type: Oracle 10g R1
      • Call Interface: ODBC 3.5
      • Data Source Name: ORCL
      • Username: SUPPLIER2
      • Passowrd: SUPPLIER2
    • Database Tables:
      • Jobs : S_NQ_JOB
      • Instances : S_NQ_INSTANCE
      • Parameters : S_NQ_JOB_PARAM
      • Messages : S_NQ_ERR_MSG
  2. Choose File --> Configuration Options --> Scheduler Tab --> General subtab, enter the following:
    • Administrator Name : Administrator
    • Administrator Password : Administrator
  3. Choose File --> Configuration Options --> Mail Tab --> General subtab, enter the following:
  4. Run CryptoTools as below: D:\OracleBI\web\bin>cryptotools.exe credstore -add -infile D:\OracleBIData\web\config\\credentialstore.xml >Credential Alias: Admin
    >Username: Administrator
    >Password: *********
    >Do you want to encrypt the password? y/n (y): y
    >Passphrase for encryption: *********
    >Do you want to write the passphrase to the xml? y/n (n): y
    File "D:\OracleBIData\web\config\credentialstore.xml" exists. Do you want to overwrite it? y/n (y): y
    Note :- For PassPhrase "Administrator" was given
  5. Start "Oracle BI Scheduler" service
  6. Installing and Configuring the hMailServer is as follows:
    • update the C:\WINDOWS\system32\drivers\etc\hosts with the below:
      • 127.0.0.1 localhost localhost.com
    • Open the hMailServer Administrator application console :
      • Under Domains , click Add and enter
        • Domain : localhost.com
        • check the "Enabled" box
      • Under Domains --> localhost.com --> Accounts , click Add and enter
        • Address : user
        • Password : oracle
        • Maximum size (MB) : 50
        • Administration level : User
        • check the "Enabled" box
      • Under Settings --> Advanced enter
        • Default domain : localhost.com
      • Under Settings --> Advanced --> Autoban, uncheck the Enabled box
      • Under Settings --> Advanced --> TCP/IP ports click Add and enter
        • Protocol : IMAP
        • TCP/IP address : 127.0.0.1
        • TCP/IP port : 143
  7. Configuring OUTLOOK Express to receive the iBot reports:
    • Goto Tools --> Accounts
    • Under Mail Tab, select Add -> Mail..
      • Display name : user
      • E-mail address : user@localhost.com
      • My incoming mail server is a : POP3 server
      • Incoming mail server : localhost.com
      • Outgoing mail server : locahost.com
      • Account name : user@localhost.com
      • Password : oracle then click next and finish.