SSAS 2012 2008 SQL Server Analysis Services Components
- There are core components that are used to create an Analysis Services solution within Business Intelligence Development Studio.
- Understanding these components will help you to start multidimensional and data mining solutions within your BI solution.
- Data sources
- Data source views
- Slice and dice
- Data mining
Let us now explain each one of this in detail
Data sources in SSAS 2012
- Data sources provide the connection information for the source system on which the Analysis Services solution is based.
- 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.
- 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
- 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.
- 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.
- To run the cube wizard or the data mining wizard, a data source view must be created.
- Annotations can be added to provide friendly names for the tables and columns to improve the readability for the user.
- 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:
Cubes in SSAS 2012
- An OLAP database can hold one or more cubes. A cube is a fundamental unit of multidimensional analysis.
- A cube consists of dimensions and measures.
- The dimensions form the axis of the cube and provide contextual information for the data that resides in the cube.
- This information is known as measures.
Dimensions in SSAS 2012
- Dimensions provide the contextual information for the data that resides in a cube.
- The dimensions in a cube typically map to a dimension table within the data warehouse.
- As dimensions are used to describe a business entity, dimensions consist of attributes that provide specific information about the business entity.
- These attributes typically map to a specific column within the dimension table in the data warehouse.
- To improve readability, hierarchies can be introduced within the dimension.
- For example, within a time dimension, a hierarchy named CalendarYear can be created containing three levels—Year, Quarter and Month.
- These levels allow the user to easily drill down into specific time periods
Measures in SSAS 2012
- Measures provide the numeric information that resides inside the cube.
- They typically map to the measures columns in the fact table of a data warehouse.
- As well as including the measures information from the fact table, SSAS also creates preaggregated versions of the measures.
- 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.
- Slicing and dicing is the technique that can be used to interrogate the data from within a cube.
- 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.
- 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.
- 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 is a feature in SSAS that enables you to extrapolate trends and patterns in the data.
- You can create data mining structures to explore the data against algorithms that are provided
- 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