Relationship between Enterprise Resources Planning (ERP) and Databases

How to install Oracle SQL Developer in a laptop and How to run a script in Oracle SQL Developer?

I am searching to find this answer in so many websites and constantly failing to connect to sql developer. Finally I found it.

Steps

1) Install jdk using this link
http://www.oracle.com/technetwork/java/javase/downloads/jdk-7-netbeans-download-432126.html
2)Install Oracle through this link. Provide a user name and password when prompting.
http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html
3)Install sql developer through this link and stored the folder in C drive (You can store anywhere. But I stored in C drive.)
http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
4)Provide the path in the environment variables if it does not automatically stored.
C:\Program Files\Java\jdk1.7.0_80\bin\java.exe
C:\oraclexe\app\oracle\product\11.2.0\server\bin
5) open sql developer.exe from the sql developer folder and click connect. Provide any connection name and  the username and password which u already given while installing oracle. Other parameters as default. Test the connection. Success. :)

Running Script

Open Tool- > Preferences ->Database -> worksheet-> Point the path to look for scripts

Open worksheet

Type @scriptname.sql

Run Script

Key Performance Indicator or simply KPI

You are driving. You are checking your speed in speedometer. Similarly there is a digital dashboard which to determine the business intelligence behind an organization.
Key Performance Indicators (KPIs) are highly summarized measures designed to quickly relay the status of that measure. They usually reflect the most vital aspects of the organization. KPIs are often presented as a graphical icon, such as a traffic light or a gauge, designed to convey the indicator’s status at a glance.
In SQL Server Analysis Services, a KPI can reflect five different status levels:
c Very Good
c Good
c Fair
c Bad
c Very Bad
Numeric values: 1 for Very Good, 0.5 for Good, 0 for Fair, −0.5 for Bad, and −1 for Very Bad

Creation of KPI using Cube Design Tab

Name: A unique name for the KPI.
Associated Measure Group :The measure group being used to calculate the
values for the KPI. It can be single measure group in the cube or all measure groups.
Value Expression: An MDX expression used to calculate the current value of the
KPI.
Goal Expression: An MDX expression used to express the current goal for the
KPI.
Status Indicator: The graphical representation to be used with this KPI.
Status Expression: An MDX expression used to translate the current value of
the KPI into a graphic representation.

Changing sql-server database from tabular to multidimensional

From yesterday onwards, I am stuck up with an error while trying to deploy cube.
"You cannot deploy the model because the localhost deployment server is not running in multidimensional mode. "

I started googling to find a solution. And I found it. By default, when we are installing sql server Analysis services use tabular mode. Without reinstalling, if u want to change to multidimensional mode, follow this steps.

Change the deployment mode

• Go to the path "C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Config" and copy the file "msmdsrv.ini" to desktop.
• Open the file "msmdsrv.ini" and change the value of DeploymentMode to 0.
  0 - Multidimensional
  1 - SharePoint
  2 - Tabular
• copy the "msmdsrv.ini" back to the location "C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Config" . Replace the existing  "msmdsrv.ini" file.
• Restart the SQL Server Analysis Services (MSSQLSERVER) from Administartive Tools-> Services.
• Deploy again. Success.

To know more, click the link below to change from multidimensional to tabular mode.

http://www.sanssql.com/2013/11/change-ssas-deployment-mode-from.html

Difference between Measure and Measure Group-Cube Design-SSIS

Measures
Measures provide the actual information that the users of our cubes are interested in. Measures are the bits of numerical data that we need to aggregate. Measures come from the fields in the fact tables in the data mart.

Eg: Accepted Products, Rejected Products, Inventory Fact Count, Number on Back Order

Measure Group
Each measure group in a cube corresponds to a table in the data source view. This table is the measure group’s source for its measure data. The measure group is said to be bound to this table. Each record in the table becomes a different
member of the measure group.
Eg: Manufacturing Fact, Inventory Fact

Source: Delivering Business Intelligence with Microsoft SQL Server 2012 (3rd Edition) Author: Brian Larson

Type 2 Slowly Changing Dimensions

SCD type 2 will store the entire history in the dimension table. In SCD type 2 effective date, the dimension table will have Start_Date (Begin_Date) and End_Date as the fields. If the End_Date is Null, then it indicates the current row. it will  accurately keep all historical information, but will cause the size of the table to grow fast which needs more storage space and poor performance.So SCD2 is used only when it is necessary to track all historical data.

Example:
--Source Table

Create Table Customers
(
  Customer_Id integer Primary Key,
  Location    Varchar(30)
);

--Target Dimension Table

Create Table Customers_Dim
(
  Cust_Key integer Primary Key,
  Customer_Id   integer,
  Location      Varchar(30),
  Starting_Date    Date,
  End_Date      Date
);

Steps

Identify new records and insert into dimension table Customers_Dim with  Starting_Date as Current date (getdate()) and End_Date as NULL.
Identify changed record and insert into dimension table with Starting_Date as Current date (getdate()) and End_Date as NULL.
Identify the changed record and update the existing record in dimension table with End_Date as Current date (getdate()).

In type 2, you can store the data in three different ways. They are
Versioning
Flagging
Effective Date

Versioning: A sequence number is used to represent the change. The latest sequence number always represents the current row and the previous sequence numbers represents the past data. The records with the highest version number is treated as an active record.
 Flagging: A flag column is created in the dimension table. The current record will have the flag value as 1 and the previous records will have the flag as 0/ If the flag indicates ‘Y’ the record is latest and if the flag indicates ‘N’ then the record is outdated.
 Effective Date: The period of the change is tracked using the start_date and end_date columns in the dimension table. NULL in the End_Date indicates the current version of the data and the remaining records indicate the past data. Four attributes of a Type 2 dimension:


1) SCD original ID -  An alternative primary key for the dimension
2) SCD start date - date this dimension member become active
3) SCD end date - date this dimension becomes inactive
4) SCD status - current state, either active or inactive usually represented by Flags

Example:

Type 1 : In the Type 1 SCD example, each employee can only appear once in the Salesperson
dimension.

Type 2: In the Type 2 SCD, a salesperson can appear in the dimension more than once

A Type 3 SCD is similar to a Type 2 SCD with one exception. A Type 3 SCD does not

track the entire history of the dimension members. Instead, a Type 3 SCD tracks only the current state and the original state of a dimension member.
A Type 3 SCD is implemented using two additional attributes:
1)  SCD Start Date The date the current state of this dimension member became active 2)  SCD Initial Value:  The original state of this attribute

Source: Delivering Business Intelligence with Microsoft SQL Server 2012 (3rd Edition) Author: Brian Larson
http://www.ajer.org/papers/v2(4)/K0248691.pdf