Sales Report Preparation using Snowflake and SSRS

Who is the client?

The Client is a global provider of development, production, technical expertise and services to a wide range of metal, steel and manufacturing industries.


What was the client trying to achieve?

The Client was looking for a cloud data warehouse solution to improve performance in accessing large datasets by means of a SQL Server Reporting Services (SSRS) Report on relevant industry sales. The performance of SSRS was very poor while generating the report from SQL Server Analysis Service. Data load, aggregation & calculation using Snowflake, a data warehouse built for the cloud, significantly improved the reporting performance by approximately 10 times.

Reporting performance 10 times faster

What services did Data-Core provide?

Snowflake is an analytic data warehouse marketed as Software-as-a-Service (SaaS). Snowflake's data warehouse is not built on an existing database or a “big data” software platform such as Hadoop. The Snowflake data warehouse uses a new SQL database engine with a unique architecture designed for the cloud. Data-Core’s main focus was to load data into Snowflake’s staging tables from a CSV file, populate Snowflake’s data warehouse tables from the staging tables, and use Snowflake computing to generate the calculated/aggregated fields. The information would then be shown through the SSRS Report. This improves the overall performance of the reporting process.


What challenges were encountered?

Data-Core was able to overcome many challenges during this project, including the ability to:

  • Understand and analyze the characteristics of the data
  • Create Snowflake’s staging tables to load data from a CSV file
  • Load data from a CSV file to Snowflake’s staging tables
  • Create the data model (fact/dimension) inside Snowflake’s data warehouse
  • Populate the fact/dimensions from Snowflake’s staging tables using Pivot Query
  • Create views using the Snow SQL windowing function
  • Connect the views with SSRS and generates the reports with improved performance

Data-Core also provided the reports using Power BI, and performance was even better than that achieved with SSRS. This was a value-added service provided to the client.

Database engine with a unique architecture designed for the cloud

In Summary…

Data-Core’s successful implementation of Snowflake’s cloud data warehouse generated improved performance of the SSRS Sales Reports, saving the client time and money.

Views using Snow SQL windowing function