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.
