Task: to give a basic look at the building of my first data cube by means of SSDT by Microsoft Visual Studio
To build a data cube we will need to follow these few steps:
1. Creating a data source
To connect to this data source the service account is being used. Using the service account (the account that runs the SQL Server Analysis Server service) is common even in production. Make sure that the service account has privileges to read this data source.
Figure 1: Creating a data source – Configuration
2. Creating a data source view
Select FactInternetSales and click on the Add Related Tables button.
Figure 2a: Creating a data source view – Configuration
Figure 2b: Creating a data source view – Diagram of facts and dimensions
3. Building my first cube
Select the data source from the first step, then click on the Suggest button and at the end select measures and dimensions.
Figure 3a: Building my first cube
Figure 3b: Tweaking dimensions for user-friendly appearance
Any warnings appear after tweaking. We will ignore warnings for this basic example.
4. Deploying and querying the cube
At the last step, click on the Deploy Solution item on the Build menu and publish the cube.
Figure 4: Querying the cube
Double click on the cube and navigate to the Browser tab. For example, drag and drop measures such as ExtendedAmount and dimensions and hierarchies such as Fiscal Quarter – Month hierarchy under the Due Date dimension into the query pane. We will retrieve requested data.
Source code: SSAS Package (SQL Server 2008 R2 and BIDS; Adventure Works DW2008R2)
Note: do not forget to customize connection strings etc
Additional references:
- SQLAuthority News – Download Whitepaper – Choosing a Tabular or Multidimensional Modeling Experience in SQL Server 2012 Analysis Services
by Pinal Dave - SQL SERVER – 2005 – Analysis Services Query Performance Top 10 Best Practices
by Pinal Dave - SQL SERVER – Data Warehousing Interview Questions and Answers Complete List Download
by Pinal Dave - SQL SERVER – Guest Post – Architecting Data Warehouse – Niraj Bhatt
by Niraj Bhatt - SQL SERVER – Languages for BI – MDX, DMX, XMLA
by Pinal Dave - SQL SERVER – SSAS – Multidimensional Space Terms and Explanation
by Pinal Dave
Placko's database KB 