OBIEE: Calculating First Day of Year, Quarter, Month in Answers / Analytics

The difference between syntax is highlighted:

First Day of Calendar Year: TIMESTAMPADD(SQL_TSI_DAY, -1*(DAYOFYEAR(CURRENT_DATE )-1) , CURRENT_DATE )

First Day of Calendar Quarter: TIMESTAMPADD(SQL_TSI_DAY, -1*(DAY_OF_QUARTER(CURRENT_DATE )-1) , CURRENT_DATE )

First Day of Calendar Month: TIMESTAMPADD(SQL_TSI_DAY, -1*(DAYOFMONTH(CURRENT_DATE )-1) , CURRENT_DATE )

Hope this helped!

SQL: Formatting SQL Statement

Key components of Data Manipulation Language (DML) SQL statement's format:

SELECT	INSERT	UPDATE	DELETE
SELECT	INSERT INTO VALUES	UPDATE SET WHERE	DELETE FROM WHERE
FROM	INSERT INTO SELECT FROM WHERE
WHERE AND OR
GROUP BY
HAVING AND OR
ORDER BY

Hope this helped!

Business Intelligence Project Information Gathering Template

Please note that this templates are created in MS OneNote.

This template illustrates a basic technical information gathering for a new BI project.




Contact	File Transfer Process	File Name
John Doe	FTP / www.sample.com	Sample_File.csv




Project/Metric Name	Source File Name	Data-Load Frequency	Data-Load Type	File Transfer Method
Product Count	SampleFile.csv	Daily	Full / Increment	Upload



• Database Table:
• T_DIMENSION_TABLE_D
• T_FACT_TABLE_F
• Date Column:
• ACTIVITY_DATE
• Measures the metrics over certain period of time. i.e. MTD, QTD, YTD
• Hierarchy / Drills:
• Country > State > County > City > Zip Code
• Filters:
• Zip Code, First_Name, Last_Name etc...
• Subject Area / Cube Name:
• Sample Sales
• Metrics:
• A / B = C


• Meeting Minutes:

YYYY-MM-DD Abc Action item needs to be completed and colored into black

YYYY-MM-DD Abc

Business Intelligence Basic IT Infrastructure Template

Please note that this templates are created in MS OneNote.

This template is an overview of What you need to start a BI project.

1. DOCUMENTATION:
1. IT infrastructure
2. Workflow process
3. Data warehouse design - ER Diagram (ERD)
4. On-going projects / new project initialization


2. COMMUNICATION:

	Login:	Password:	Note:
E-mail:	username@doamin.com	***
Chat Client:	IM Chatter / username	***
VPN:	VPN Client / username	***



3. SERVER ACCESS/NAME (Linux via Putty):

Server Name	Description	Access Point	User/Password
n10:		Server_name	Username/Password



4. BI ENVIRONMENT:

	URL	Login	Password	Note
DEV:		Username	Password
SIT:		Username	Password
STAGE:		Username	Password
PROD:		Username	Password



5. DATABASE CONNECTION (TNS):

TNS Name / Environment:	Schema User/Password	Hostname	Port	Service Name
TNS1	Username/Password	Host_name	1234	ORCL



6. Other:
1. Abc

Entity Relationship (ER) Diagram

How to read Entity Relationship (ER) diagram? and ER basics:

Source

OBIEE 11g: User Interface (UI) Performance Tuning / Enhancement

2.6.4 Apache 2.2.x HTTP Server
This topic describes how to enable caching and compression in Apache HTTP Server of your Oracle® Business Intelligence Enterprise Edition. Important Note: High load of HTTP replies with 304 status code causes the OBIEE 11g UI to work slow in IE browser 7 / 8. To resolve this issue, it is highly recommended to implement HTTP caching and compression that will help to minimize the round trips over the Web to
revalidate cached items, can make a huge difference in browser page load times.
a. How to Enable Compression and Caching:

1. On the Apache machine, open the file HTTP Server configuration file (httpd.conf) for editing.
2. In httpd.conf file, verify that the following directives are included and not commented out:
LoadModule deflate_module modules/mod_deflate.so
LoadModule expires_module modules/mod_expires.so
LoadModule headers_module modules/mod_headers.so

3. Add the following lines in httpd.conf file below the directive LoadModule section to compression / caching and restart the Apache HTTP Server: 

#HTTP Compression
<IfModule mod_deflate.c>
SetOutputFilter DEFLATE
SetEnvIfNoCase Request_URI \.(?:gif|jpe?g|png)$ no-gzip dont-vary
SetEnvIfNoCase Request_URI \.(?:exe|t?gz|zip|bz2|sit|rar)$ no-gzip dont-vary
SetEnvIfNoCase Request_URI \.(?:pdf|doc?x|ppt?x|xls?x)$ no-gzip dont-vary
SetEnvIfNoCase Request_URI \.avi$ no-gzip dont-vary
SetEnvIfNoCase Request_URI \.mov$ no-gzip dont-vary
SetEnvIfNoCase Request_URI \.mp3$ no-gzip dont-vary
SetEnvIfNoCase Request_URI \.mp4$ no-gzip dont-vary
</IfModule>

#Caching of static files
ExpiresActive On
<IfModule mod_expires.c>
ExpiresByType image/gif "access plus 3 months"
ExpiresByType image/jpeg "access plus 3 months"
ExpiresByType application/x-javascript "access plus 3 months"
ExpiresByType text/css "access plus 3 months"
ExpiresByType text/javascript "access plus 3 months"
ExpiresByType image/png "access plus 3 months"
ExpiresByType application/x-shockwave-flash "access plus 3 months"
</IfModule>

#This stops the HTTP 304 replies in IE 7/8 browser
<IfModule mod_headers.c>
<FilesMatch "\.(gif|jpeg|png|x-javascript|javascript|css|swf)quot;>
Header set Cache-Control "max-age=7889231"
</FilesMatch>
</IfModule>

Source

Hope this helped!

OBIEE: Changing Default Chart Colors

To change default chart color series navigate to (difference is highlighted):

OBIEE 11.1.1.7.x

<ORACLE_HOME>/Oracle_BI1/bifoundation/web/msgdb/s_FusionFX/viewui/chart/dvt-graph-skin.xml
Older versions:
<MW_HOME>\Oracle_BI1\bifoundation\web\msgdb\s_blafp\viewui\chart\dvt-graph-skin.xml

Add the following <SeriesItems> tag before </Graph>
Note: You can add as many <Series id="n" color=.... /> as you wish.

<SeriesItems>

<Series id="0″ color="#ff0000″ borderColor="#ff0000″/>
<Series id="1″ color="#00ff00″ borderColor="#00ff00″/>
<Series id="2″ color="#0000ff" borderColor="#0000ff"/>
</SeriesItems>

</Graph>

Save your changes and exit. ALWAYS make sure to take a back-up of the original file.

Note 2: Changes are effective immediately; however make sure to clear your browser cache and there is no need to restart any OBIEE services.

Hope this helped!

Unexamined life...

"The unexamined life is not worth living!" - Socrates

OBIEE 11g: Data-Level / Object-Level Security - Query Limit

To set query limit and number of minutes a query can run per physical layer database connection, follow the below steps:

1. Login to Repository using OBIEE Admin Tool
2. Go to Manage > Identity
3. Go to Application Role tab, choose the role and double click on it to open.

4. Click on Permissions tab



5. Set the Query Limits. You can limit queries by the number of rows received, by maximum run time, and by restricting to particular time periods. You can also allow or disallow direct database requests or the Populate privilege.



Hope this helped!

OBIEE 11g: Query Tuning / Friendly Alternative for CURRENT_DATE

Using in Analysis:

"Date_Column" IN (TIMESTAMPADD(SQL_TSI_DAY,-1,CURRENT_DATE))

This will return yesterday's date - it is an alternative to CURRENT_DATE - 1.

Using Dashboard Prompt:
SELECT TIMESTAMPADD (SQL_TSI_DAY,-1,CURRENT_DATE) 

FROM "Subject_Area_Name"