A Real Estate company was looking forward to generating more traction in their sales and marketing initiatives. They had few existing and few new requirements to focus on.

Current System and Challenges

Data from Sharepoint/CRM is used in Power BI. New reports are required to be made, keeping in mind the slowness of reports. Data was fetched individually for each file from Sharepoint, with a bit of data modelling

Below Are the challenges faced:-

1. Issue with switching the environment (dev — uat — prod):

    Currently there is no mechanism to automatically move a ready report to UAT 

    and then to PROD environment. They are manually updating and publishing the 

    report to different environments.

2.Calling multiple tables from the Sharepoint or CRM:

    There were multiple tables in Sharepoint which were required to be picked up

    and transformed, those were picked up manually.

3.Performance issues of existing reports:

    The existing report was slow and they generally takes time whenever some 

    filters were applied.

Solution Provided and its impact

1. Issue with switching the environment (dev — uat — prod):

    Parameters were added for data sources, which enabled dynamic updates

    without much manual involvement.

2.Calling multiple tables from the Sharepoint or CRM:

    A function was added to the source query, which allowed us to select the

    required set of tables.

3. Performance issues of existing reports:

     We identified that the performance of the existing reports was not good 

     because:

     · Data Modelling: Data modelling was not done properly; tables were created as

       and when needed. Which created lots of performance issues. We updated the 

       model and followed STAR schema for the data model, which is a required 

       parameter for improving the performance of report.

     · Many-to-Many relationship: this kind of relationship between fact and  

       dimension tables creates complexity in DAX calculation and filters. To find an 

       alternative solution for this, we created composite key.

     · Date Dimension: Report was using date filters and there were YTD calculations 

       also involved in the report, and all these were done without a date dimension

       table. We created a date dimension table.

After going through all the challenges in the current system, we updated the current process with the addition of Data quality and cleansing steps, and then finally having a STAR schema for data modelling. After the data was modelled with STAR schema, we have added a few more reports for the sales and marketing team.

The client was able to see an improvement in the performance of existing reports. Also, they could see the new updates flowing smoothly and swiftly from DEV to PROD environment.