MicroStrategy Reporting Tool

Microstrategy

§

§

What is MicroStrategy Reporting Tool

§MicroStrategy is a tool which is used to develop enterprise wide documents/dashboards/reports. So, in simple words it is a report generation tool.

§MSTR can run on 2, 3 or 4 tier architecture. The 3 Tier architecture comprises an Intelligence server between MicroStrategy Desktop and the data Warehouse and the Metadata. The 4 tier architecture is same as 3 tier except it has an additional component of MicroStratey Web.

§
MSTR Web you can create, manipulate and deploy MSTR Reports, Dashboards in Web where the users can access them through any internet browsers like IExplorer, Mozilla etc.,

§MSTR Desktop you can connect to the MSTR intelligent Server and work on MS projects/Administrative tasks like user management and so on. And Architect the attributes, facts and so on from your PC or Laptop.

MicroStrategy Architecture

Intelligent Server: Microstrategy intelligent server provides jobs management and analytical processing for all microstrategy applications. This acts as a central component connecting the metadata, warehouse, desktop, web server and narrow cast server.

Web server: Microstrategy web server responds to the requests from browsers. Web server interacts with the i-server to extract the necessary information. Can be installed on most of the major web servers and supports most popular browsers. Contact microstrategy for certified products.

 

Narrow cast server: Delivers personalized business insight to emails, cell phones, pagers, file servers and print servers extending the reach of business intelligence applications.

 

Metadata: Data about data.

2 Tier Architecture

3 Tier Architecture

N Tier Architecture

MicroStrategy Architecture - Usage

USAGE:  Report - Document - Dashboard - Visual Insight - Dataset - Report Types - Document Types - Scheduling - Subscription

MicroStrategy Objects

Schema Objects

Attributes

Fact

Hierarchies

Application Objects

Metrics (measures i.e. sum, avg..)

Prompts

Filters

Advanced Reporting

Custom Groups

Consolidation

Drilling

Quick Switch

Template

Selectors (parameters on dashboard)

Widget (flash display of dataset)

Intelligent Cube

Link Document/Report

MicroStrategy Objects

Schema Objects

Attributes : Provding business context to a report.

Fact : Facts are numerical data which can be aggrigated.

Hierarchies: represent a set of attributes with paths between them.

Application Objects

Metrics (measures i.e. sum, avg.,): Calculating data on a report.

Prompts : Asking for user input i.e. parameters

Filters :  Filtering data on areport.

Advanced Reporting  Enables to create an ordered collection of components like top 10 customers or tom5 lowest selling items

Custom Groups : Enables to group attribute elements in new ways without changing metadata. you can use them on a report just like attributes.

Consolidation: 

Drilling : Enables you to view data at different levels (up/down) than the ones originally displayed in result set.

Quick Switch : Switch a dataset report between grid view and graph view in interactive mode or flash mode.

Template :  Templates supply the layout or format for a report or document.

Selectores (parameters on dashboard)

Widger (Falsh display of dataset) :  In memory copy of data.

Intelligent Cube

Link Document / Report

MicroStrategy Objects

Examples:








Filters in reporting



A filter is used to select the data for calculating the metrics in the report. 

Some of the main OBIEE tools are 
Oracle BI Server, 
Answers, 
BI Publisher, 
Interactive Dashboards, 
Oracle BI Administration, 
Delivers (for scheduling), etc.

Meta data repository i.e. RPD (Rapid File database, .rpd) file is the main one on OBIEE; it can be developed by using 3 layers (Physical/Business/Presentation layers) in ADMIN tool.

Oracle BI Administrator tool has 3 layers;

OA Framework Personalization

OA Framework Personalization

OA Framework includes the OAF Personalization allows to personalize an Oracle E-Business Suite application page without modifying any underlying code. 

In OAF the page definitions are stored in JDR tables normally called MDS repository (metadata will be available in JDR tables, such as all fields, regions, buttons etc have to be clubbed into a meaningful to make a web page. MDS provides service to store & return page definitions). So it’s easy to modify the pages.

Few JDR tables are JDR_ATTRIBUTES, JDR_ATTRIBUTES_TRANS, JDR_COMPONENTS & JDR_PATHS. Only one JDR API is JDR_UTILS is a PL/SQL package which can be used to query and inspect OA Framework metadata in the MDS repository.

Migrating OA Framework Personalization’s also easy from Functional Administrator Responsibility by export and import by using FND: Personalization document root path profile with FTP from source and target paths (dev to production) in Linux.

Note: Dynamically created pages without OA Extension metadata behind them are not personalizable with the OA Personalization Framework.

All Personalization’s you make through the OA Personalization Framework are added on top of the base product meta data at runtime. These personalizations never overwrite the existing base product UI and are therefore preserved during upgrades and patches, and can also be translated. This means you can create your personalization on a test system first, extract your personalization’s to a flat file, then load your finalized personalization to your production system with little interruption. 

The following system profile options affect the behavior of the OA Personalization Framework.

1. Personalize Self-Service Defn: Yes to allow ‘Personalize’ button to appear at the top of the page
2. FND: Personalization Region Link Enabled: Yes to display all the regional links above each region Minimal to display key regional links
3. Disable Self-Service Personal: Yes will disable all personalization at any level
4. FND: Diagnostics: Turns on 'About this Page'
5. FND: Personalization Document Root Path (new in 11.5.10):Required to migrate personalization. Set this profile option to a tmp directory with open (777) permissions.        

Personalization levels are administrator or user level or be seeded by Oracle application developers.

Administrator-level personalization: All OA framework-based pages can be personalized by default. However, administrators can't personalize programmatically created pages or regions, or pages or regions for which the application developer has explicitly disabled administrator personalization.

Administrators may personalize pages, for example, by

Changing text for labels, prompts, and tip messages
Hiding or showing components
Reordering components
Restricting query results
Adding items, such as fields, buttons, links, pick lists, or images
Specifying whether fields are required
Changing the destination of hyperlinks

The extent to which an administrator can personalize page flow is limited, and depends on how the flow was implemented. In some cases, you can modify function URLs and personalize the destination URLs that display as navigational elements. Page flow changes are generally beyond the scope of systems administrators, however, because such changes may affect transaction flows, and need thorough testing.

Administrators can personalize OA framework pages at the following levels:
function
location
site
organization
responsibility
As an administrator, you can define personalizations at the function level to determine which users can access specific functions, and when. For example, you can hide the salary field from a user updating an employee record, but display it when the user creates a new employee.

At the location level, you can use locales as the context for personalization – for example, to show different address field labels based on users' country settings.

Site-level – or global – personalizations apply to all users with access to a given application component, such as "setting the number of rows shown in a table."

Organization-level personalizations apply only to users in a specified organization or business unit. You can use organization-level personalizations, for example, to stipulate that notifications be sorted by age for one organization and by urgency for another.

Personalizations at the responsibility level display only to users under a specific responsibility. You may, for example, configure a trend graph to display only to users under the sales manager responsibility.

The level at which an administrator configures personalization settings determines their precedence over other settings. For example, a responsibility-level personalization takes precedence over site-level personalizations for users with the specified responsibility. Users at the site who are not using the relevant responsibility would see only the site-level changes.

User-level personalization:Default user personalizations, or user views, can exist at three levels. They can be seeded by Oracle developers, created by a system administrator, or created by the end user.

End users can access the personalization user interface by clicking the Save View or Personalize button. They can then personalize inquiry pages using query regions to create specific data views. For example, they can

Create and save searches, Configure table column titles, Hide or show columns, Reorder columns, Add data filters, Change sort orders.

OA Framework Examples

1.    Remove a button
2.    Change prompts
3.    Hide/reorder columns in a table
4.    Set initial values
5.    Display a field

Example 1: Remove a work list link in below screen, click the personalize page link to remove work list access on left bottom.

Carl f to find access and then click the pencil button.

 Set rendered as false at responsibility level.

 Click return to application as shown below..

Link has been removed in below screen.

Example 2: Change a web page at privacy link in below page.

Example 3:  Add export button to export supplier contacts into excel file.

Example 4: Add button to call concurrent program from below page.

Example 5: Modify a query..like excluding an employee type in below screen.

Example 6: Change the number of records in grid 5 to 10.

Example 7: Hide a field from below page..

 

Below screen do not have payee bank account column heading as it's hidden now.

Example8: add a tip on top right in below screen.

See the TIP on right top on below screen.

Oracle APEX - Oracle Application Express

Oracle APEX - Oracle Application Express

Oracle APEX: It is formerly called HTML-DB, a web-based Rapid Application Database tool which resides in Oracle database. We can develop web based browser interface application with very minimal programming skill set (PL/SQL, HTML, CSS and JavaScript) in a very fast development cycle with only a browser and access to an Oracle database running Application Express.

If you are running on oracle database and you want to build rich web application with reports, forms, charts, drill downs and dashboards with limited Java experienced developers in a short time frame, Oracle APEX is the most likely candidate for consideration.

APEX works with only Oracle databases.

APEX Architecture

APEX (v4.2) is installed on Oracle database (above 9.2), starting from Oracle 11g it comes pre-installed with the database. It is comprised of Meta data in tables, PL/SQL code and extensive JavaScript APIs.

The URL request from the browser is translated into appropriate APEX PL/SQL call by either Oracle HTTP Server (Apache) with mod_plsql plugin or Embedded PL/SQL Gateway. This varies by the type of APEX installation on oracle database. After the data is processed, results are relayed back to browser as HTML. This cycle happens each time user request or submits a page. The application session state is maintained in database tables.

APEX installation can be done in two ways. DBAs are more concerned about it than developers. But this determines how the URL is translated. APEX embeds business logic and presentation together in oracle database. APEX is fast growing platform for RAD. APEX doesn't replace OAF/ADF (as it’s not following MVC architecture for secured application), it's highly productive, wizard driven development and deployment and easy to use navigation cannot go unnoticed.

Advantages of APEX:


1. Builds smart web applications that are both fast and secure.
2. Runs on and resides in Oracle database. APEX framework and META data are stored in Oracle   tables.
3. It is FREE. No licensing required.
4. Fast learning curve for developers to build application.
5. Deployment of application is as simple as 'Export and Import' if hard coded references of values that change between environments are avoided.
6. Lot of scope for customization of application look and feel.
7. It can be configured to use Oracle SSO and EBS FND user repository.
 
Oracle HTTP Server: In this three-tier configuration, mod_plsql in Oracle HTTP Server acts in between client web browser and server database.

For each URL that is processed, mod_plsql either uses a database session from its connection pool, or creates a new session on the fly and pools it. For mod_plsql to invoke the appropriate database PL/SQL procedure in a URL-processing session, you must first configure a virtual path and associate that path with a Database Access Descriptor (DAD).

A DAD is a named set of configuration values that specify the information necessary to create a session for a specific database and a specific database user/password. This includes the database service name and the Globalization Support setting (for example, language) for the session.

Embedded PL/SQL Gateway: This is a classic client-server architecture where embedded PL/SQL gateway provides the Oracle database with a Web server and also the necessary infrastructure to create dynamic applications. The embedded PL/SQL gateway runs in the XML DB HTTP server in the Oracle database and includes the core features of mod_plsql, but does not require the Oracle HTTP Server powered by Apache. Inclusion of the embedded PL/SQL gateway simplifies the architecture and eliminates the middle tier entirely. Oracle HTTP Server is known and proven technology; it has been used for Self Service applications.


apex.oracle.com allows anyone to request for  free APEX account to build applications or run packaged applications. Follow the below steps for creating account in apex.oracle.com

Go to apex.oracle.com and click 'sign up for account' link below Login button.

1.Fill administrator information. This user will have all rights to create new users and manage their accounts.

2.Enter workspace name (For Ex: XX-APEX). Workspace is dedicated area where you develop applications. A workspace is attached to one or more database schemas. In a typical development environment, you might create a single workspace for all your developers to share.
3.Enter name of database schema and its space allocation.
4.Fill justification and complete by confirming the request.
5.You will receive an email once the request is approved. Oracle administrator do great job to approve your request as soon as they can. 
6.After clicking on approval link, you get another email with userid/password.
7.Then you are set on login on apex.oracle.com  and run sample application under Application Builder.

Creating a Form in APEX: 

Select the EMP and DEPT tables along with primary key and sequences.

Login to oracle apex

Select the database schema

Click the edit page button on above screen to get below screen for adding region.

Select master detail form in above screen and specify the master table I.e. DEPT in below screen.

Specify the detail table I.e.EMP in below screen.

Primary key details 

Click the edit in above master region then it will show below detail block.

It's easy to add/delete/update with APEX forms.

Creating a Report in APEX: 

Select the create page button in below screen.

Select the report in below screen.

Select the classic report in below screen.

Select the query builder button in above screen and select dept table and click run

  

Click return in above screen to get below screen.

Enter link label in below screen to get excel output.

I.e. DL to excel

 Click run page to run the report.