What is Data Source and DataSource View?
An Analysis Services database contains a collection of Data Sources, which stores all the data sources used to build dimensions and cubes within that database. Analysis Services will be able to retrieve source data from data sources via the native OLE DB interface or the managed .NET provider interface.
Data source that contains one fact table with some number of dimensions linked to it by joins; that data source is populated by data from an OLTP database and is called an Operational Data Store (ODS).
The ODS is a single entity storing data from various sources so that it can serve as a single source of data for your data warehouse.
Data Sources Supported by Analysis Services:
Analysis Services uses a cartridge mechanism that allows it to use the appropriate SQL language and extensions to talk to different relational database systems.
Analysis Services 2008 officially supports specific relational data sources. The major relational datasources for Analysis Services databases include Microsoft SQL Server, IBM ’ s DB2, Teradata, Oracle,Sybase, and Informix.
The Data Source Wizard also allows you to create data sources based on an existing data source connection already created so that a single connection can be shared by Analysis Services for multiple databases. The wizard also allows you to establish connections to objects within the current Analysis Services project, such as establishing an OLE DB connection to the cube being created in the project. Such a connection is typically useful while creating mining models.
The Impersonation Information dialog in the Data Source Wizard has four options to choose from. The following lists more details on the four options and when each option is likely to be used:
- Use a specific Windows user name and password: we typically would choose this optionwhen the SSAS instance service startup account does not have permissions to access thebackend. When we select this option you need to specify a Windows username and password that SSAS will use to connect to the relational backend. Due to security reasons theusername and password are encrypted and stored. Only the encrypted password is sent tothe SSAS instance when the project is deployed.
- Use the service account: This is the option typically selected by most users. We need to makesure the service startup account of the SSAS instance has access to the relational backend.
- Use the credentials of the current user: This option is typically selected for data mining. Thisoption can be used for out – of – line bindings, DMX OPENQUERY statements, local cubes, andmining models. Do not select this option when we are connecting to a relational backend forprocessing, ROLAP queries, remote partitions, linked objects, and synchronization from targetto source.
- Inherit: This option instructs Analysis Services to use the impersonation information specified for the database connection. This option used to be called “ Default ” in SQL Server 2005 edition.
. NET versus OLE DB Data Providers:
There are two types of data providers that most data sources support. OLE DB defines a set of COM interfaces that let you access data from data sources. There is also a .NET managed code interface similarto OLE DB. Providers implementing that interface are called .NET providers.
. NET Providers:
Microsoft has created the .NET Framework and programming languages that use the framework to run in the Common Language Runtime (CLR) environment. The .NET Framework itself is a huge class library that exposes tons of functionality and does so in the context of managed code.
Ready to put your .NET skills to the test? Try out some of our .NET interview questions in your own hiring process.
OLE DB Data Providers:
OLE DB is an industry standard that defines a set of COM (Component Object Model) interfaces that allowclients to access data from various data stores. The OLE DB standard was created for client applications to have a uniform interface from which to access data. Such data can come from a wide variety of data sources such as Microsoft Access, Microsoft Project, and various database management systems.
The Trade – Offs:
Versions of Analysis Services prior to Analysis Services 2005 supported connecting to data sources through OLE DB providers only. SSAS 2008 and SSAS 2005 have much tighter integration with the .NET Framework and support connections via both OLE DB and .NET data providers.
Data Source Views:
Data Source Views (DSVs) enable you to create a logical view of only the tables involved in your data warehouse design.
DSV Wizard:
The DSV Wizard helped us to create a DSV by going through a few dialogs. We need a data source to create a DSV. If we had not created a data source object in our database, the DSV Wizard allows us to create new data sources from the DSV Wizard’s Select a Data Source page by clicking the New Data Source button. In addition, the DSV Wizard also allows us to restrict specific schemas as well as filter certain tables, which helps us to work with only the tables we need to create our DSV.
DSV Designer:
The DSV Designer contains three panes. The center pane contains a graphical view of all the tables in the DSV and their primary keys. The relationships between tables are represented by lines with an arrow at the end. The top – left pane is called the Diagram Organizer , which is helpful in creating and saving concise views within large DSVs. When a DSV contains more than 20 tables it is difficult to visualize the complete DSV in the graphical view pane.
The lower – left pane of the DSV Designer is called Tables and is used to show the tree view of allthe tables of the DSV along with their relationships to other tables
Adding/Removing Tables in a DSV:
The DSV Designer provides you with the capability to easily modify the DSV. To modify the existing tables, right – click the diagram view pane and select Add/ Remove Tables.
Using this dialog we can add additional tables to the DSV by moving tables from the Available objects list to the Included objects list or remove existing tables by moving them from the Included objects list to the Available objects list. We can also remove a table from the DSV in the DSV Designer in the graphical view pane or the table viewpane using the following steps:
- Select the table to be deleted.
- Right – click the table and click Delete Table from DSV.
- Click OK in the confirmation dialog that appears.
Specifying Primary Keys and Relationships in the DSV:
To specify the primary key(s) for a table, you need to do the following in the DSV Designer:
- Select the column in the table that you want to specify as a primary key.
- Right – click and select Set Logical Primary Key.
- Select column A in Table1 that is involved in the join to Table2.
- With column A selected, drag and drop it to column B in Table2. This forms a relationship between Table1 and Table2.
We can also create a new relationship by right – clicking a table and selecting New Relationship.
Customizing Your Tables in the DSV:
SSAS 2008 provides the functionality of performing all these particular operations within the DSV using a Named Query. We can invoke the Named Query editor by right – clicking a table and selecting Replace Table With New Named Query. If we want to add a specific table twice in Our DSV or add some columns of a new table, we can launch the query designer by right – clicking the DSV Designer and selecting With New Named Query.
Execute the query to make sure the query is correct. The results from the underlying relational database will then be visible in a new pane beneath the SQL pane. Click OK once you have formed and validated your query. The table is now replaced with results from the Named Query you have specified in the DSV.
A new column is added to the Employee table. The data type of this calculated column will be determined based on the data types of the actual columns involved in the calculation or data used within the expression. If the expression results in a number, the data type for this column will be an integer.
The DSV maintains the calculated column of a table as a computed column in the metadata; it does not write it out to the underlying tables. When we want to view the data of this table the expression must be added to the SQL query so that we can see the data of this computed column.
Data Source Views in Depth:
Data warehouse designs consist of several fact tables and all the associated dimension tables. Small datawarehouses are usually comprised of 10 to 20 tables, whereas larger data warehouses can have morethan a hundred tables.
To create a new diagram, you need to do the following:
- Right – click the Diagram Organizer pane and select New Diagram,
2. Name the new diagram “ Internet Sales. ”
3. We now have an empty diagram view. Right – click the diagram view and select Show Table. We are presented with a dialog where we can choose the table(s) we want to include in this diagram.
4. Select all the tables that are part of the InternetSales fact table and click OK. This gives you a diagram view of Internet Sales that contains the Internet Sales fact table and the related dimension tables.
Data Source View Properties
Maintain properties. Within the DSV you can view the properties of the objects in the DSV such as tables, views, columns, and relationships. Properties of these objects are shown in the Properties window within BIDS.
Multiple Data Sources within a DSV:
Once we have added the tables from multiple data sources to our DSV, we can start creating our cubes and dimensions as if these came from a single data source. The limitation that the primary data source needs to be a SQL Server is due to the fact that Analysis Services instance uses a SQL Server –specific feature called OPENROWSET to retrieve data from other data sources.
