Terminology | Description |
---|---|
AMO | Analysis Management Objects |
BI | Business Intelligence |
BIDS | Business Intelligence Development Studio |
Cube | A logical object that stores dimensions and facts and provides a multidimensional view of the data (often used interchangeably with UDM) |
DM | Data Mining; also used for Data Mart |
DMX | Data Mining Extensions (language) |
DTS | Data Transformation Services (generally refers to legacy SQL Server 2000 packages) |
DW | Data Warehousing, or a Data Warehouse |
ETL | Extract, Transform, Load |
HOLAP | Hybrid OLAP |
KPI | Key Performance Indicator |
MDX | Multidimensional Expressions (language) |
MOLAP | Multidimensional OLAP |
OLAP | Online Analytical Processing (also see HOLAP, MOLAP, ROLAP) |
OLTP | Online Transaction Processing |
ROLAP | Relational OLAP |
SSAS | SQL Server Analysis Services |
SSIS | SQL Server Integration Services |
SSRS | SQL Server Reporting Services |
UDM | Unified Dimensional Model (often used interchangeably with cube) |
XMLA | XML for Analysis |
A developer's buzz on working with Azure, Visual Studio, SQL Server, and yes... DW, DM & BI
Thursday, July 29, 2010
The DW-DM-BI acronym and terminology guide
The BI field is full of acronyms - many are common to the industry itself, while others are specific to the Microsoft solution (incidentally, Microsoft used to be famous for their TLAs - Three Letter Acronyms - but lately we are seeing some increased usage of four letter acronyms).
Wednesday, July 28, 2010
Use a Data Warehouse for Reporting
Businesses of all types and sizes use database systems, called Online Transaction Processing (OLTP) systems, to store and manage their transactions. These OLTP systems, when well designed, store data in relational tables in a highly normalized format, that is, tables are linked to each other so as to:
This is because aggregation is being done on the fly for each query, and combined with the additional filters (WHERE clauses), quickly starts eating up our SQL Server resources. Not only are the reports slow, but other business users experience significant system slowdowns as well.
A Data Warehouse (DW) on the other hand, which is part of an Online Analytical Processing (OLAP) system, is built for one purpose alone - fast, efficient reporting.
In a DW system, data from the OLTP system will be read, de-normalized, and (typically) stored in a Star Schema, as a set of Dimension and Fact tables. This is often done using SQL Server Integration Services (SSIS). From these tables, tools within SQL Server Analysis Services (SSAS) can be used to build cubes, which contain not only our data but also its pre-calculated aggregates based upon definitions that we specify within SSAS. This results in many advantages:
- Avoid duplication/redundancy of data
- Minimize space requirements
- Enable fast retrieval and updates of these transactions
This is because aggregation is being done on the fly for each query, and combined with the additional filters (WHERE clauses), quickly starts eating up our SQL Server resources. Not only are the reports slow, but other business users experience significant system slowdowns as well.
A Data Warehouse (DW) on the other hand, which is part of an Online Analytical Processing (OLAP) system, is built for one purpose alone - fast, efficient reporting.
In a DW system, data from the OLTP system will be read, de-normalized, and (typically) stored in a Star Schema, as a set of Dimension and Fact tables. This is often done using SQL Server Integration Services (SSIS). From these tables, tools within SQL Server Analysis Services (SSAS) can be used to build cubes, which contain not only our data but also its pre-calculated aggregates based upon definitions that we specify within SSAS. This results in many advantages:
- Super-fast reporting
- No impact on the OLTP system
- Data Mining (DM) and Business Intelligence (BI) solutions can be readily built
Subscribe to:
Posts (Atom)