BI Terminologies ETL Datawarehouse OLAP Datamining Scorecards Dashborads Reporting in Business Intelligence
Business
Intelligence BI Terminologies for SSAS SSRS SSIS
The Basic BI Terminologies are stated below
- ETL
- Dataware house
- OLAP
- Data Mining
- Dashboards
- Scorecards
- Reporting
- Organizations host Online Transactional Processing (OLTP) systems for holding data that runs the business, such as sales databases or order entry systems.
- Performing reporting on such systems would be detrimental to the running of the business as this reporting could cause performance problems that at the worst, may prevent a business from earning revenue from the OLTP systems that are set up for that purpose.
- Some businesses use the same SQL Server system for both transactional and reporting purposes.
- The more optimal approach is to separate the OLTP systems so that it can be optimised to perform modifications to the data, and create a reporting systems known as decision support systems, so that it can be optimised to read data.
- You can then use the data from the transactional systems as the basis for populating the decision support system in a controlled manner.
- When performing this task, you will come across the following terminology
A) ETL
- ETL stands for Extract, Transform and Load. Data is extracted from source systems such as OLTP databases and loaded into a destination database such as a decision support system or a data warehouse.
- During the movement of this data, you can optionally transform the data. Data transformations allow you to change the format or structure of the data.
- SSIS is an ETL tool that is provided by SQL Server 2008. ETL is an important aspect of a BI solution. The data that is stored in transactional systems, at the very least, are stored in a normalised structure, optimised for modifying data.
- A data warehouse holds the same information, but it is structured in a way that is optimised for reading data and not compatible with a transactional system.
- As a result, you can use SSIS to change the structure of the data and populate a data warehouse from a source transactional system. In typical ETL solutions using SSIS, staging tables may be used to break down the movement of data from source systems into the data warehouse.
- Staging tables acts as useful intermediary storage locations in that data can be moved out of the source systems and straight into the staging tables to reduce the time and resources that are placed upon the source system.
- From the staging table, you can then transform the data as it is loaded into the data warehouse without impacting the resources on the source systems.
B) Data warehouse
- A data warehouse is a consolidated repository of data that has been populated by the movement of data from transactional systems by using SSIS.
- SSIS also cleanses the data as it is moved into the data warehouse to ensure that the data is consistent. This movement occurs on a regular basis.
- A data warehouse is a relational database that contains tables and relationships to provide an integrated view of the data within a business.
- Compared to transactional systems, however, the tables are more denormalised within a data warehouse as to optimise it for querying purposes.
- As the data warehouse holds a separate copy of the data found on transactional systems, reporting and data analysis activities can then take place without impinging on the transactional systems performance.
- A data warehouse can consist of one or more data marts.
- A data mart is a set of tables that are interrelated and contain its own fact table and a number of dimension tables.
C) OLAP
- OLAP refers to the multidimensional analysis of data.
- SSAS consumes information from a data warehouse to store data within an OLAP database that is stored in cubes.
- Cubes can store detailed data, however, its power is to create preaggregated data that is persistent within the cube.
- You can arrange the aggregated data so that it can be intersected by dimensions that provide contextual information for the aggregated data.
- Dimension can include contextual information about customers, employees or orders.
- This allows analysis to be performed far more efficiently than could be performed against the same data in a transactional system. OLAP databases can store one or more cubes within it.
D) Data mining
- Data mining provides an exciting aspect to SSAS in that it can use mathematical algorithms to analyse the data in either a cube or a relational table.
- This analysis can involve trend analysis, data classification or clustering and sequence analysis.
- Data mining allows you to explore your data and find out patterns that may have not been immediately evident.
- SSAS provides the ability to create data mining structures that allow you to pass through the source data through data mining algorithms known as data mining models.
- It also provides validation tools that you can use to validate the accuracy of the results that are returned by the data mining structures.
- Microsoft Excel 2007 also has a data mining add-in that enables you to expose data mining structures through a familiar client tool.
E) Dashboards and scorecards
- Key performance indicators (KPIs) are a feature of SSAS and are stored in an OLAP database but can be exposed through client tools such as SSRS, Microsoft Excel and Microsoft Office SharePoint Services.
- The ability to provide key business metrics through these visual indicators is a very powerful feature that enables you to build scorecards and digital dashboards through the client tools.
- Storing the KPIs on SSAS helps you to manage the KPIs centrally
F) Reporting
- Reports are the objects of the SQL Server BI stack that the users have most interaction with.
- Using SSRS, you can provide flexible standard reports that can contain a high degree of interactive objects such as parameters and drill through capabilities that allow the user to be involved with the report.
- You can also automate the delivery of reports through subscription mechanisms such as e-mail.
- Furthermore, SSRS provides the capability for power users to create their own reports for ad hoc requirements.
0 comments:
Post a Comment