The goal of my project was to develop a data warehouse of my own and use dummy datasets of my choosing to demonstrate how such data could be managed and utilised using the following Microsoft Services:
- SQL Server Integration Services (SSIS)
- SQL Server’s Analysis Services (SSAS)
- SQL Server Reporting Services (SSRS)
Further analysis of the dataset was also shown using PowerBi, where I was able to create an interactive visualisation report of my chosen data’s.
Using Microsoft Services and PowerBi allowed me to explore many ways in which large scale databases can be transformed and simplified for the sake of making the data appear more organised and easily readable to any individual analysing the data records.
Creating Data Warehouse
For my data warehouse I chose to use Microsoft SQL and created a database of my own under my university username. To populate my database I searched for datasets from kaggle.com where I decided to choose a theme in which I would enjoy working with – Mario Kart. I then used the following Microsoft Services to develop a data integration, analysis and reporting solution to support Data Warehousing.
The SSIS has features which allowed me to showcase a range of data migration tasks I was able to perform using the data from my datasets. The following SSIS methods are what I performed to create a project supporting Data Warehousing solution.
Enhanced Entity Relational Diagram (EERD)
To provide structure to my relational database and create relationships between my data tables I created an EERD. The diagram also helped me identify patterns within my datasets which I used for Data Analytic and Data Visualisation.
Extracting data from CSV File to Data Warehouse
The dataset downloaded from kaggle came in the form of CSV files which required me to use SSIS to import the data from CSV to my data warehouse. Steps I took to import the data can be seen through this link.
Once the data warehouse was populated with my dataset I then transformed the data using the SSIS methods stated:
Say for example the speed data for one of the Mario character was changed, this would also have an effect on the class type the character is placed under. To allow the user to compare the past and current change to the Mario character speed and type data, the following method was used. A timestamp was an additional column added to allow the user to view the date and time of when the data was changed if needed.
Sometimes data types may not match and therefore need to be converted. This was the case for my dataset. Some of the numerical data’s had “varchar” as its data type which created issues for myself when it came to using calculation methods further in my project. However there are times when data conversions can create errors which can be easily overseen. The following method allowed me to carry out data conversions and view the data’s that were able to successfully convert and those that were not able to. In my example, I tried to convert all my data’s in my tires table to one-byte unsigned integer (convert the numerical data to 0 or greater) however there were errors which showed in my errorlog CSV file.
Further transformations were carried out on the data in my data warehouse using SSAS. The service features allowed me to create a tabular Model Project that would provide high performance analysis of my data warehouse. I was able to perform and demonstrate ways of creating:
- Calculated Columns – Created a new column within the Character Data table called “Speed Class” and added a calculation method that if a Mario characters speed was less then 3 then they are considered as slow whereas those greater than 4 are considered fast/faster.
- Measures – Finding out the maximum/minimum/average speed value using the data from the character table.
- Perspective -As the name implies this method is what allows me to create various perspectives that will enable me to show certain information depending on the user role. For example information that a player will be able to view will be different to the information that is seen by a developer of the gaming system.
I then deployed the SSAS tabular model database on SQL as you can see by the image.
This service feature allowed me to develop a report project for my data warehouse. SSRS offers various features e.g. the ability to showcase the data analysis through the use of charts and tables.
I performed the following skills within my data table:
- Sorting – Alphabetically order the character table
- Drilling – The table has a toggling feature that will allow the user to either see more/less information about a certain mario characters information.
- KPI – if the speed values of each mario character exceeds 2.25 then they will either be amber/green in colour. However, if the value is lower then 2.25 then the indicator will be red in colour.
Further visuals of my datasets were shown using PowerBi where the software also allows the user to interact with the given reports. An example I created the following report to allow the viewer to select a character, view their acceleration, speed and weight data and use those information to find a kart that will help balance out the characters weaknesses (or vice versa). For example say the player is given a character with a slow speed then combining the kart with the highest speed and acceleration may increase the chances of winning.
Teesside University – Information Technology BSc (Hons)
Year 3 Modules:
Client Focused Business Solutions, Computing Project , Databases and Business Intelligence, Information Governance, Service Management.
| Year 2 Modules:|
Relational and NoSQL Databases, Networks and Systems Administration, Rapid Development Systems, Information Systems Security, IS Developer.
|Year 1 Modules:|
Web and Mobile Development, Systems Design and Databases, Rapid Development Tools, Internet Architecture and Security, Client-Side Scripting, Computer and Mobile Technologies.
A highly passionate and responsible final year IT student who is looking for a part time job to do alongside my studies. Through experience from past projects and jobs in childcare and hospitality, I am capable of working well in a team. Not only do I establish effective communication with those I interact with, but I also ensure to show respect and encouragement to each individual. I am an eager learner and approach any work I am given with a positive attitude.
Software & Hardware Proficiencies
PowerApps, Microsoft SQL Server, Vertabelo, Packet Tracer, PowerBI, Visual Studio, MS Excel.
Employment, Work Experience & Volunteering
Sept 2016 – Present: Catering Assistant – Barkers Restaurant.
- Attentive and ensure that customers are satisfied with my level of service.
- Also, establish trust between my customers.
During my progress in the university, I have been introduced to a wide range of software, some of which I have tried exploring even more during my free time. For example, PowerApps and SSMS. In past university projects I enjoyed working with these two and have tried to explore the various ways in which the data taken from Microsoft SQL Server can be visualised on PowerApps. In addition, PowerBI is another software that I was recently introduced to which I have tried to further explore on my own.
I also enjoy and show interest in networking which is why I thought I would go with a different approach from PowerApps and SQL for my final year project.