Friday, 29 November 2013

Data Source and Data Source View

Data Source
       A data source in Analysis Services specifies a direct connection to an external data source. In addition to physical location, a data source object specifies the connection string, data provider, credentials, and other properties that control connection behaviour.

      Information provided by the data source object is used during the following operations:

  • Get schema information and other metadata used to generate data source views into a model.
  • Query or load data into a model during processing.
  • Run queries against multidimensional or data mining models that use ROLAP storage mode.
  • Read or write to remote partitions.
  • Connect to linked objects, as well as synchronize from target to source.
  • Perform write back operations that update fact table data stored in a relational database.
      When building a multidimensional model from the bottom up, you start by creating the data source object, and then use it to generate the next object, a data source view. A data source view is the data abstraction layer in the model. It is typically created after the data source object, using the schema of the source database as the basis. However, you can choose other ways to build a model, including starting with cubes and dimensions, and generating the schema that best supports your design.

       Regardless of how you build it, each model requires at least one data source object that specifies a connection to source data. You can create multiple data source objects in a single model to use data from different sources or vary connection properties for specific objects.

       Data source objects can be managed independently of other objects in your model. After you create a data source, you can change its properties later, and then pre-process the model to ensure the data is retrieved correctly.

Click here for step by step instruction to create data source.

Data Source View

         A data source view (DSV) is an abstraction of a relational data source that becomes the basis of the cubes and dimensions you create in a multidimensional project. The purpose of a DSV is to give you control over the data structures used in your project, and to work independently of the underlying data sources (for example, the ability to rename or concatenate columns without directly modifying the original data source).

        You can build multiple data source views in an Analysis Services project or database on one or more data sources, and construct each one to satisfy the requirements for a different solution.

Click here for step by step instruction to create data source view.

Thursday, 28 November 2013

SSAS Tutorials : Multidimensional Modeling

SSAS Multidimensional Modelling Tutorials

  1. Data Source and Data Source View
  2. Dimension and Cube
  3. Measures, Attributes and Hierarchies
  4. Advanced Attribute and Dimension Properties
  5. Relationships Between Dimensions and Measure Groups
  6. Calculations
  7. Key Performance Indicators (KPIs)
  8. Actions
  9. Perspectives and Translations
  10. Administrative Roles

SQL Server Analysis Services (SSAS) business intelligence (BI) semantic modelling options

SQL Server Analysis Services (SSAS) provides three different options to create business intelligence semantic model. Namely those 3 options are tabular, multidimensional and PowerPivot.

  • Tabular model uses relational database modelling objects such as tables, views and relationships for modeling data, and the xVelocity in-memory analytics engine for storing and calculating data.
  • Multidimensional model uses OLAP modelling constructs such as cubes and dimensions and uses MOLAP, ROLAP, or HOLAP storage.
  • PowerPivot model is a self-service BI solution that lets business analysts build an analytical data model inside an Excel workbook using the PowerPivot for Excel add-in. PowerPivot also uses xVelocity in-memory analytics engine within Excel and on SharePoint.

Table below summarizes feature available for each modelling option.

Calculated MeasuresYesYesYes
Custom AssembliesYesNoNo
Custom RollupsYesNoNo
Distinct CountYesYes (via DAX)Yes (via DAX)
DrillthroughYesYesYes (detail opens in separate worksheet)
Linked objectsYesNoYes (linked tables)
Many-to-many relationshipsYesNoNo
Parent-child HierarchiesYesYes (via DAX)Yes (via DAX)
Semi-additive MeasuresYesYesYes
User-defined HierarchiesYesYesYes
See below links to learn about each modelling option.