Home » Posts filed under Data Mining
Showing posts with label Data Mining. Show all posts
Showing posts with label Data Mining. Show all posts

SSAS 2012 2008 SQL Server Analysis Services Components Such as Cubes

Posted by 20Fingers2Brains on Tags: , , , , , ,


SSAS 2012 2008 SQL Server Analysis Services Components

  1. There are core components that are used to create an Analysis Services solution within Business Intelligence Development Studio. 
  2. Understanding these components will help you to start multidimensional and data mining solutions within your BI solution.
The Main SSAS Components are stated below:
  1. Data sources
  2. Data source views
  3. Cubes
  4. Dimensions
  5. Measures
  6. Slice and dice
  7. Data mining

 Let us now explain each one of this in detail
Data sources in SSAS 2012
  1. Data sources provide the connection information for the source system on which the Analysis Services solution is based.
  2. Typical configuration for data sources involve specifying the name of the server on which the data is stored. You also specify the credentials required to connect to the server and the name of the database to access. You should also consider that additional settings may be required between different data sources.
  3. The data sources that are supported by SSAS  2012 2008 include:
  • SQL Server 7.0 using the SQL OLE DB Provider or the .NET native OLE DB provider (x86, x64 and ia64).
  • SQL Server 2000 using the SQL OLE DB Provider or the .NET native OLE DB provider (x86, x64 and ia64).
  • SQL Server 2005 using the SQL Server Native Client OLE DB provider or the SqlClient.NET managed provider (x86, x64 and ia64).
  • SQL Server 2008 using the SQL Server Native Client OLE DB provider or the SqlClient.NET managed provider (x86, x64 and ia64).
  • Oracle 9.0 using the Microsoft OLE DB Provider for Oracle or the .NET native OLE DB provider (x86 only).
  • IBM DB2 8.1 using Microsoft OLE DB Provider for DB2 (x86, x64, ia64)—only available for Microsoft SQL Server 2005 Enterprise Edition or Microsoft SQL Server 2005 Developer Edition.
  • Access with Microsoft Jet 4.0 OLE DB provider (x86 only).
  • Teradata v2R6 with OLE DB 1.3 provider from NCR (x86 only).
  • Teradata managed provider (x86, x64 and ia64).  

Data source views in SSAS 2012

  1. Data source views are objects based on a data source that provide an abstraction of a subset of tables, columns and relationships from the data source that you require as part of your Analysis Services solution.
  2. This abstraction layer holds metadata about the objects and allows you to create an Analysis Services solution without a permanent connection to the data source.
  3. To run the cube wizard or the data mining wizard, a data source view must be created.
  4. Annotations can be added to provide friendly names for the tables and columns to improve the readability for the user.
  5. The type of information that is defined within a data source view includes:
  • A data source view name.
  • A definition of any subset of the schema retrieved from one or more data sources including:
    • Tables
    • Columns
    • Relationships

Cubes in SSAS 2012
  1. An OLAP database can hold one or more cubes. A cube is a fundamental unit of multidimensional analysis.
  2. A cube consists of dimensions and measures. 
  3. The dimensions form the axis of the cube and provide contextual information for the data that resides in the cube.
  4. This information is known as measures.

 Dimensions in SSAS 2012
  1. Dimensions provide the contextual information for the data that resides in a cube. 
  2. The dimensions in a cube typically map to a dimension table within the data warehouse.
  3. As dimensions are used to describe a business entity, dimensions consist of attributes that provide specific information about the business entity. 
  4. These attributes typically map to a specific column within the dimension table in the data warehouse.
  5. To improve readability, hierarchies can be introduced within the dimension. 
  6. For example, within a time dimension, a hierarchy named CalendarYear can be created containing three levels—Year, Quarter and Month. 
  7. These levels allow the user to easily drill down into specific time periods

 Measures in SSAS 2012
  1. Measures provide the numeric information that resides inside the cube. 
  2. They typically map to the measures columns in the fact table of a data warehouse.
  3. As well as including the measures information from the fact table, SSAS also creates preaggregated versions of the measures.
  4. For example, a fact table may consist of a measure named OrderQuantity. As well as including the detail information that is provided by the fact table, Analysis Service creates preaggregated OrderQuantity totals for yearly, quarterly or monthly totals. By creating this preaggregated data, queries for this information is retrieved more efficiently.
Slice and dice in SSAS 2012 
  1. Slicing and dicing is the technique that can be used to interrogate the data from within a cube.
  2. This technique is implemented in products such as Excel 2007/10/13. This allows you to use the pivot table feature to slice and dice the data to retrieve specific data.
  3. Business Intelligence Development Studio provides a browser window that allows you to slice and dice the data to test the results that is returned before the cube is deployed into a production environment.
  4. Furthermore, you can also employ this technique within Reporting Services as the basis for providing data for the reports that are deployed through SSRS.
Data mining in SSAS 2012 
  1. Data mining is a feature in SSAS that enables you to extrapolate trends and patterns in the data.
  2. You can create data mining structures to explore the data against algorithms that are provided
  3. There is also a Data Mining Add-in for Excel 2007 2010 2013 that allows you to explore the results that are returned by a data mining structure. You can also use the add-in to create your own data structures

BI Terminologies ETL Datawarehouse OLAP Datamining Scorecards Dashborads Reporting in Business Intelligence

Posted by 20Fingers2Brains on Tags: , , , , ,

Business Intelligence BI  Terminologies  for SSAS SSRS SSIS 
The Basic BI Terminologies are stated below
  1. ETL 
  2. Dataware house
  3. OLAP
  4. Data Mining
  5. Dashboards
  6. Scorecards
  7. 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
  1. 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. 
  2. 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. 
  3. 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. 
  4. 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. 
  5. 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. 
  6. 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.
  7.  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
  1. A data warehouse is a consolidated repository of data that has been populated by the movement of data from transactional systems by using SSIS.
  2.  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.
  3.  A data warehouse is a relational database that contains tables and relationships to provide an integrated view of the data within a business. 
  4. Compared to transactional systems, however, the tables are more denormalised within a data warehouse as to optimise it for querying purposes.
  5.  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. 
  6. A data warehouse can consist of one or more data marts.
  7. 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
  1. OLAP refers to the multidimensional analysis of data. 
  2. SSAS consumes information from a data warehouse to store data within an OLAP database that is stored in cubes.
  3.  Cubes can store detailed data, however, its power is to create preaggregated data that is persistent within the cube. 
  4. You can arrange the aggregated data so that it can be intersected by dimensions that provide contextual information for the aggregated data. 
  5. Dimension can include contextual information about customers, employees or orders. 
  6. 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
  1. 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.
  2.  This analysis can involve trend analysis, data classification or clustering and sequence analysis. 
  3. Data mining allows you to explore your data and find out patterns that may have not been immediately evident. 
  4. 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.
  5.  It also provides validation tools that you can use to validate the accuracy of the results that are returned by the data mining structures. 
  6. 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
  1. 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. 
  2. 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. 
  3. Storing the KPIs on SSAS helps you to manage the KPIs centrally 
F) Reporting
  1. Reports are the objects of the SQL Server BI stack that the users have most interaction with. 
  2. 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. 
  3. You can also automate the delivery of reports through subscription mechanisms such as e-mail. 
  4. Furthermore, SSRS provides the capability for power users to create their own reports for ad hoc requirements.

Copyright © 2014 20Fingers2Brains. All rights reserved. New Super Seo modified by CB Blogger. Powered by Blogger