The third part of the solution is based on an OLAP Data Base, to create it we need to open Visual Studio Data Tools and create a new project using the Analysis Services Multidimensional and Data Mining Project Template.
Once created the project we need to add a new Data Source that use the Data Base that we created before (Part I) and that should be filled using the .Net application created in the part II of this series of post.
The next step is to create the Data Source View, in order to do this you need to do right click on the Data Source Views Folder of the solution and follow the wizard selecting the Data Source that was created before.
Your Data Source View should look like this.
Now we need to create the cube, in order to do this you need to do right click on the Cubes Folder and select new cube and follow the wizard.
We need to select the tables in which we want to create measures, for example count of tweets, users etc.
Your cube design will look like this
Your also need to ensure that your Dimension Usage look like this
We can see that the Hashtag table has a reference relation with the others dimension through the Tweets dimension.
Now you need to deploy and process your cube, in order to do this you need to do right click on the Cubes name and select Process.
After the cube is deployed you need to process it
Now you can see your OLAP Data Base and the cube in your Analysis Services Instance using the SQL Server Management Studio.
In the next post we will see how to use excel to explore the data in the cube.