Oyetola Ogungbile

Project Overview<p>This project builds from raw source data, a data model (the back bone) for report analysis. A lot of cleaning and transformation was done in order to have proper tables. After that, related tables were merged in creating the dimension and facts tables, and relationships were established between them. This is a very key aspect, as all filters ride on the relationships.</p><p></p>Youtube Videos<p>The entire project has been captured in video format and can be seen on Youtube with the following links:</p><ul><li>Understanding the business and the data, creating clients and product tables</li><li>Creating sales, geo and junk dimension tables</li><li>Creating the fact inventory table</li><li>Creating the campaign table and a factless fact tables</li><li>Creating the order process table</li><li>Creating the sales target and security tables</li><li>Reconfirming standards, applying data category, date format and creating dimension date table</li><li>Build basic measures, implement and test two (2) row-level security</li></ul><p></p>Tools & Technologies<ul><li>PowerPoint</li><li>Excel</li><li>Power BI</li><li>Power Query</li><li>DAX</li></ul><p></p>Dataset Explained<p>The source data was from an excel workbook which had 23 worksheets. Each worksheet was converted into a query/table. Data about entities such as product, customers and sales were scattered across several worksheets and had to be identified before merging them togeter.</p><p></p>Standards Applied<p>Uniformity and consistency is important in any project for all collaborators. Below are the Standards adhered to in this project:</p><ul><li>Language - English</li><li>Naming conventions - Snake case</li><li>Table prefixes - "dim" (for dimension tables) and "fact" (for fact tables)</li><li>Identification of keys - _id (for source data) and _key (for Surrogate keys)</li></ul><p>In addition, data categorization and a uniform date format was applied ("yyyy-mm-dd") in the model</p>Dimension Tables<p>Dimension tables are descriptive in nature. Related tables with similar attributes were merged in creating each dimension table. The dim_clients table, dim_product table and dim_geo table, to mention a few, are the dimension tables created.</p><p></p>Fact Tables<p>Fact tables are transactional. There are more than three fact table in this project, and in creating them, an eye had to be place on the grand total in order to forestall duplicating or truncating the figure.</p><p></p>Building Relationships<p>The project was based on a star schema and several one to many (1-*) relationships were established between dimension and fact tables. However, in creating the row-level security, a one to one relationship had to be established between the security tables and the dim_client table. The dim_geo table served as a role-playing dimension table to the fact_sales table, with one the relationship being inactive.</p><p></p><p></p>Row Level Security<p>Stakeholders wanted two (2) row-level securities for:</p><ul><li>Client managers</li><li>Regional managers</li></ul><p>Once the relationships were established between the security tables and the dim_client table, the two roles were created and DAX was used in retuning the values relevant to each users. The roles were tested to confirm operability.</p><p></p>