Wednesday, September 30, 2015

SQL BOOK


Below are the good good sql/plsql book.

Prepares By Oracle : SQL BOOK
Prepared By Oracle : SQL PRACTICES BOOK
Prepared By Manjunath BT : SQL BOOK
Prepared BY Tutorialspoint.com : SQL/PLSQL BOOK

If your new to SQL and PLSQL - Then go for this book : SQL/PLSQL


Display Grand Total

Hi All,

Some time user wanted below type of Grand Total.[Check Red Mark].
















To get above type of grand total. follow below steps.
STEP 1 - Create same kind of Report == >Edit Report == >Go to Result 
STEP 2 - Go to Selection Step Pane
STEP 3 - From list --> select column.[B]
STEP 4 - You will see two things
                1st. Start all member and 
                2nd : Then New step
Click on 2nd option and add calculated Item
STEP 5 - Select Function as SUM --> Add all PRODUCT ---> and put Heading as Grand TOTAL

--
Thanks 
Rakesh Patil
rakeshpatil002@gmail.com

Friday, April 24, 2015

Factless Fact Table

Fact table is nothing but the fact table does not contain measures or facts. These tables are called “Factless Fact tables”.

For example -  Below fact table which has only Cust_ID_Prod_ID and Cust_Add_ID key is a factless fact. There are no measures in this table. But still you can find out products sold over a period of time.


Thanks and Regards
Rp - rakeshpatil002@gmail.com

Bridge Table

If you want to connect two tables where there is no relation ship you can use a thrid bridge table for connecting them which will have common columns in both tables, this is used in BMM Layer.


Thanks and Regards
Rp - rakeshpatil002@gmail.com

Duplicate rows in a table

SELECT
  Col1, Col2, COUNT(*)
FROM Table1
 GROUP BY Col1, Col2
HAVING COUNT(*) > 1

Tuesday, March 31, 2015

Patch Apply

Hi All,

Below are the basic steps to apply patch.

1 - Download the respective patch from Oracle site
2 - Unzip the Patch and paste the files in the below path


D:\Oracle\Middleware\Oracle_BI1

 
3 - Go to the command prompt and enter the below statements

Set ORACLE_HOME=D:\Oracle\Middleware\Oracle_BI1  ---->this should be your obiee installed path
Set PATH=%ORACLE_HOME%\bin;%PATH%
Set JAVA_HOME=%ORACLE_HOME%\jdk
Set PATH=%JAVA_HOME%\bin;%PATH%
Set PATH=%ORACLE_HOME%\OPatch;%PATH%


4 - Then navigate to below path in the command prompt

D:\Oracle\Middleware\Oracle_BI1\1885888(this is the folder name once i unzipped the patch)

5 - After navigating to the path enter the below command and press enter

opatch napply -skip_duplicate     

6 - It will take 1 to two minutes to check your patch files
    Then one command line display .It will ask Y/N  ---press 'Y'
7 - It will take atleast 5 min to execute all patches to your OBIEE
     Once you completed please login to analytic page
8 - Go to administration button    and please check your bundle status

Thanks and Regards
Rp - rakeshpatil002@gmail.com

Saturday, February 21, 2015

Change Apply Button of dashboard prompt for particular dashboard page

Hi All, 

Some time you will get requirement, to change the apply button name for particular dashboard page. As per my knowledge we cannot change the apply button name for particular dashboard page.

Solution 
1--> Hide the prompt apply button
2--> Create the separate button with help of HTML and Java Program. Give the name as per your requirement. Here in my example i have give "Apply Rakesh Patil"

Steps
1--Edit Dashboard, Go to Dashboard Properties,
In Prompts Apply Buttons - Select hide all apply buttons
In Prompts Reset Button – Select Hide all apply buttons
Click on ok.




















2--Drag Text object – Go to Properties, and select check mark  in Contain HTML Markup and add below code.


<DIV CLASS = "XUIPromptEntry minibuttonOn" align="center">
<input class="button" type = "button" value = "Apply - Rakesh Patil" onclick='javascript:
try
{
var x = 0;
var aElm=PromptManager.getPromptManager().getAllPromptCollectionJSON();
for(var i = 0; i < aElm.length;i ++ )
            {
            for(var j = 0; j < aElm[i].promptSteps[0].prompts.length;j ++ )
                        {
                        var promptid = aElm[i].promptSteps[0].prompts[j].promptStreamID;
                        var g = null;
                        var h = PromptManager.getPromptManager().getPromptCollectionInfoWithViewID(aElm[i].viewStatePath);
                        if(h)
                                    {
                                    try
                                                {
                                                var b = h.getAllPromptExprsArray();
                                                if( ! h.verifyPromptValuesAndDisplayError(b))
                                                            {
                                                            return;
                                                            }
                                                g = PromptManager.buildPromptExprGivenExpr("", b);
                                                }
                                    catch(f)
                                                {
                                                alert(f);
                                                return;
                                                }
                                    }
                        }
            x++;
            }
x--;
PromptManager.submitPrompt(aElm[x].viewStatePath, true,"PromptFinish", g)
}
catch(e) {alert("Apply ALL Button Exception" + e.description);}' ></input>
<input class="button" type = "button"  value = "Reset" ONCLICK = 'return PersonalizationEditor.removeDefaultSelection(false)' > </input>
<div/>

Let me know if you have any questions.

Output :






Thanks and Regards 
Rp - rakeshpatil002@gmail.com


Sunday, February 15, 2015

Dummy or Black Column in OBIEE11g

Hi All,

I have seen, so many times due to some business requirement we need to create to dummy or black Column.

Steps –
Drag any Column from subject area
Go to Edit formula – add below syntax

Column with numeric values:  CAST(null as DOUBLE)
Or
Text column: Null
Or
Date column CAST(null as DATE)

Check below screen shots for Column with numeric values

Thanks and Regards 
Rp - rakeshpatil002@gmail.com

Sunday, February 8, 2015

Logical dimension table does not join to any fact source

Hi All 

This is regular error while developing RPD, for those who are new in OBIEE.

Error Type : Logical dimension table does not join to any fact source

Trick to fix the above error : Change the logical level to column in fact table LTS under content tab aggregation content, group by section.Check Below Screen shots.Check the global consistency check, you should not see such warnings.



















Thanks and Regards 
Rp - rakeshpatil002@gmail.com

Monday, January 26, 2015

Initialization blocks (IB) : Refresh interval

Hi All,

The SQL query in the Initialization block gets fired based on the refresh interval set while defining the Initialization block. So if the refresh interval is set to 2 hours, the BI server will execute the query and refresh the variable after every 2 hours. The BI server will always follow the schedule. Whatever value is stored in the variable will be used by the BI server to replace any references to the variable.











The query is also fired every time the BI server is started. So essentially, the query is fired for the first time when the BI server is started. The BI server then refreshes the variable based on the refresh interval specified in the IB.


Thanks and Regards 
Rp - rakeshpatil002@gmail.com