In this part of the tutorial we will decorate the UDM with some attributes and will generate the relational database schema.
We will start with the attributes of the MSA dimension. Looking back at the data source, we see that the MSA dimension (columns A and B) contains three levels of hierarchy - Region (Northeast, Midwest, etc.), Sub-region (New England, Middle Atlantic, etc.), State and the lowest level - MSA. So we have total of four attributes including the key attribute.
We manually add the attributes to the MSA dimension:
Then we add the Month and Year attributes to the Time Period dimension:
The Employment Category dimension is flat - there is no hierarchical structure to it, so we’ll leave this dimension alone.
It is time to generate the schema now.
Follow these steps to get this done:
- Launch SQL Server Management Studio, connect to SQL Server Database Services and create a new relational database - we’ll call this database “Employment BI”:
- Go back to Business Intelligence Development Studio and create a new data source in the SSAS project - right-click on the “Data Sources” icon in the solution explorer and select “New Data Source”. Provide the necessary information to create the data source connection to the “Employment BI” relational database.
Here’s a snapshot of my settings:
For impersonation mode I usually select to have SSAS impersonate my windows account by selecting “Use the service account” in the Data Source Wizard, but feel free to select whatever makes sense in the context of your security settings:
Go through the Data Source wizard and when prompted, name the data source “Employment BI”. - Launch the Schema Generation Wizard by selecting “Database\Generate Relational Schema…”
- On the first screen select “Create a new data source view”, name the new data source “SSAS” and select the “Employment BI” data source:
- On the next screen uncheck the settings to preserve referential integrity and to preserve data on regeneration:
- Leave the naming conventions page as is and click “Finish”
The Schema Generator produces the entire relational schema and an SSAS data source view based on the metadata we provided earlier. Here’s a snapshot of the data source view:
The generator has produced two fact tables: Employment_Facts and Unemployment_Facts and three dimension tables: Employment_Category, Time_Period and MSA.
Note that the schema follows Kimball’s guidelines for keeping the dimensional tables denormalized. In other words the Schema Generator favors denormalized star schema over normalized snowflake schema. In a star schema all the attributes of a dimension are collapsed together in a single table to avoid complex and slow multi-table joins when data is queried.
In the next post we will finish this mini-project by creating an SSIS project to populate the dimensional model with the data captured in the Excel spreadsheet.
I find that your written work is rather great as it comes with a good deal of accurate data. In Any Event, was overcurious whether you would willing to interchange contacts with my website, as I am looking forward to generate more contacts to further extend and get better web exposure for my site. I do not mind you putting my contacts at the sitewide page, just approving this web links on this particular page is more than sufficient. Moreover, please contact me at my site if you are interested in exchanging links with me, I would really like that. Thanks a lot and I hope to get a reply from you very soon!
I tried installing the program but my computer wouldn’t accept it at first. Before installation I was already having problems with my computer. It has already been rejecting execution of my already installed applications plus, a low disk space prompt keeps popping up at the task bar. When I researched fro a solution it turned out that computer registry has been having problems. I downloaded c cleaner software and installed to my computer. After a few minutes of registry scan it then proceeded to fixing every identified error. I restarted my computer and the low disk space prompt was already gone. I also noticed a big change in computer response. I tried reinstalling BI Solution and was finally successful this time.
I find that your written work is rather great as it comes with a good deal of accurate data. In Any Event, was overcurious whether you would willing to interchange contacts with my website, as I am looking forward to generate more contacts to further extend and get better web exposure for my site. I do not mind you putting my contacts at the sitewide page, just approving this web links on this particular page is more than sufficient. Moreover, please contact me at my site if you are interested in exchanging links with me, I would really like that. Thanks a lot and I hope to get a reply from you very soon!
+1
Thank you Melen for recommending ccleaner, it fixed the problems I was encountering!
This can be I technology that I might love the way to be capable how to usage for myself. this’s merely a cut higher than the remainder as well as I will’t wait till my provider has this. Ones New insight i has what I needed. Thanks
I have been exploring for a bit for any high-quality articles or weblog posts in this kind of house . Exploring in Yahoo I at last stumbled upon this website. Reading this information So i am satisfied to show that I’ve an incredibly excellent uncanny feeling I came upon exactly what I needed. I so much indisputably will make certain to don’t overlook this website and provides it a glance a continuing.