Snowflake and OLAP solutions

Question

I know questions related to this topic have been posed in the past but it's been a while and I was hoping to get a fresh conversation going about Snowflake and OLAP.

We started our usage of Snowflake a little over a year ago. Prior to that we were predominantly a Microsoft shop running SQL Server and SQL Server Analysis Services(SSAS). Our SSAS OLAP data sources still get used heavily all day/every day. In an effort to plan for the future where we hoped to utilize our data sources within Snowflake for reporting, I am trying understand our options in regards to multi-level aggregations and reporting against that data.

How are others doing multi-level aggregations and then doing BI and/or reporting against that data? We are using Power BI for BI and currently SSRS for reporting but we don't need to if there is something that works better. I am curious what products or processes are being used by others to do this OLAP type work along with visualizing that OLAP type data?

Any and all thoughts or approaches are more than welcome. Thanks in advance!

Answer 1

Snowflake provides an Elastic Data Warehouse Platform and, in my experience, it fits well within a classic model where an OLAP engine sits on top of a Datawarehouse that is designed using the Kimball Methodology.

I have been working in the same use case scenario as in the question while migrating an on-prem BI Solution based on a classic MS BI Stack, over to Snowflake, and the new solution provides the lowest granular data from within Snowflake, whereas aggregations take place in the PowerBI engine. In between Snowflake and PowerBI Service, Dataflows have been very useful to make transformations and to filter out unnecessary rows and columns