Let us suppose we have an enterprise and we gather certain information about that enterprise from various sources and have a clear understanding about the organization for the smooth functionality of any business. This practice is called Enterprise Intelligence.
Enterprise Intelligence includes advanced analysis and multi-dimensional visual analysis capabilities in order to identify any bugs across your enterprise.
Business Intelligence
The technology – Business Intelligence (BI) is purely for business decision making. This is a practice in which big data is collected, the data is analyzed and integrated, certain values are created from the data and finally presented for valuable business information. Example: Data warehouses, dashboards.
Data Warehouse
Data warehouse combines informations collected from all the possible sources into a single repository. In data warehouse, the data is organized, unnecessary information is filtered out and the data is processed for a specific purpose.
Data Warehouse differs from Data Lake in the sense that Data Lake contains large pool of raw data its purpose is not yet defined.
Analysis Services
Analysis Services (Microsoft SQL Server Analysis Services) is an Online Transaction Processing and Data mining tool in Microsoft SQL Server. To Understand how we can use Analysis Services to extract summary information from our data, first of all we need to familiarize ourselves with OLAP. Online Analytical Processing.
Online Analytical Processing:
Online Analytical Processing (OLAP) is a technology behind many Business Intelligence applications in which fast analysis of shared multidimensional Information is done.
This includes complex analytical calculations, limitless report viewing and planning for any kind of scenarios in the data.
OLAP has vast speed of querying and huge storage space.
Basics of OLAP:
- Dimension
- Attributes
- Fact Table
- Cube
- Hierarchy
- Level
- Measure
- Schema
Dimension:
Dimension contains attributes related to an entity to categorize data on row/table axis.
Attribute:
Each attribute contains columns in the dimension table. These are used in foreign key relationship to the fact table.
Fact:
Fact contains measurable numeric data, for one or more entities.
Cube:
Cube is a multidimensional data structure composed of dimensions and measure groups.
Hierarchy:
Hierarchy is a collection of nested attributes associated in a parent-child fashion. It is formed from one or more attributes from same dimension.
Measure:
Measure specifies a column that contains quantifiable data, usually numeric, that can be aggregated.
Schema:
In star schema, every dimension is linked to the fact while in the snowflake schema, some dimensions are linked to the fact through intermediate dimension.
MDX
MDX ( Multidimensional Expressions ) is a language for querying data base.
Two Dimensional – We need T- SQL for querying the database ( for any insert, delete, update, etc).
Three Dimensional – We need MDX to query the data from cube database.
If we pass an MDX query from any kind of report, SSRS or any reports, First of all, this will generate MSX queries which will hit to database (Analysis Services). Then the result will be displayed in the browser.
This will not show complexity in writing MDX.
MDX plays a major role..Because SSAS cubes are developed and deployed but it is of no use if no query can be done from that cube. Hence, MDX is used.
There is nothing like update, delete drop.
95% of the queries are SELECT. 5% is CREATE.
SInce this is used for datawarehouse that means we need to analyze whether the business is going good or not, SO the major purpose of datawarehouse solution is to analyze the business.
Example:
SELECT <columns>
from tablename
where condition
MDX: ( Axis are used in MDX i.e. 0 axis, 1 axis and so on)
SELECT <ITEMS> ON axis(0),
<items> on axis (1),
<items> on axis (2),
and so on
from
CUBE_NAME
where
<CONDITION>
select axis(0) on COLUMNS,
select axis(1) on ROWS
from CUBE_NAME
WHERE CONDITION
Axis is the representation of either columns or rows.
Business Intelligence Landscape:
- Source
- ETL (Extraction, Transformation, Load)
- Data Warehouse
- Presentation