The most common data access method is structure query language (SQL). This language is designed to allow easy access to relational data. If you are familiar when set theory or Venn diagrams SQL will seem very intuitive. SQL allows you to quickly and simply combine relational data. Below is an example of a database containing two tables, customers and purchase order. The Customer table has customerid (the unique identifier for the customer), name (The customer’s name) and age (the customer’s age). The PurchaseOrder table has POID (the unique id for the purchase order), customerid (which refers back to the customer who made the purchase) and Purchase (what the purchase was). |
||||||||||||||||||||||||||||||||||||||||||||||||||||
Example: | ||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
SELECT
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
SELECT * FROM MyDataBase.Customer
|
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
ORDER BY
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
SELECT * FROM MyDataBase.Customer ORDER BY by age
|
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
WHERE
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
SELECT * FROM MyDataBase.Customer WHERE age = 18
|
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
INNER JOIN
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
SELECT * FROM MyDataBase.Customer INNER JOIN MyDataBase.PurchaseOrder ON Customer.CustomerID = PurchaseOrder.CustomerID
|
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
LEFT OUTER JOIN
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
SELECT * FROM MyDataBase.Customer LEFT OUTER JOIN MyDataBase.PurchaseOrder ON Customer.CustomerID = PurchaseOrder.CustomerID
|
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
GROUP BY
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
SELECT Name, count(*) as Orders FROM Customer INNER JOIN PurchaseOrder ON Customer.CustomerID = PurchaseOrder.CustomerID GROUP BY Name
|
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
UPDATE
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
UPDATE Customer SET Age = 26 WHERE CustomerID = 1
|
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
INSERT
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
INSERT INTO Customer Values (6, Terry , 50)
|
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
DELETE
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
DELETE FROM Customer WHERE CustomerID = 1
|
|
Tag: reporting
OLAP
1. Intro
Online Analytical processing (OLAP) allows rapid ad hoc analysis of large datasets via a drag and drop programming. OLAP has two main components, a back end which stores multi-dimensional data and a front end which allows users to drill through the data. A pivot chart is an example of a front end. The data can also be accessed via MDX, a multi-dimensional query language rooted in SQL. Microsoft, Cognos and Hyperion are the leaders in this market. An OLAP cube can be considered a data warehouse.OLAP cubes can have two to many dimensions. The maximum number of dimensions if determined software used but I seldom see a cube with more than 6 dimensions. The diagram below is of a 3 dimensional OLAP cube.

2. For Modeling
OLAP cubes allow users to quickly run complex queries using a graphical interface. They do not find relationships in the data the user must drill through the data to find those relationships. A good use for OLAP Cubes is verifying heuristic rules uncovered by other techniques.
3. Example
Table: Customer
Fields : Name, State, Age, Gender, Total Purchases, Total Expenditures, Total Fiction, Total Nonfiction
Dimensions
A dimension is any field of collection of fields you may want to group by. In the above example good candidates for dimensions are State, Age and Gender.
Fields you wish to report on are considered facts. In the above example good candidates for dimensions are Total Nonfiction, Total Fiction, Total Purchases and Total Expenditure. Some field may be considered both facts and dimensions. If a common question is, “what is the profile of are biggest customers?” then purchases and Expenditure should be added as Dimensions and State, Age and Gender added as Facts. In practice most if not all Dimensions will also be included as Facts. Notice Name is excluded as both a Dimension and Fact. Name is the lowest level of the data so should not be included unless required.