Create web-based reports over a Snowflake database

Create web-based reports with a database reporting tool

Full Transcript of Video

In this video, I’ll show you how to create web-based reports over a snowflake database in about 5 minutes. And before I build the reports, let me open up my snowflake interface and show you the data I want to use. I have a bunch of data in here. A lot of tables, and I could build reports over any of these, but I’m planning to build over this sales table and this forecast table.

And I’m going to build the reports with m-Power, as it makes the process so simple. And keep in mind, I can build over any database with m-Power, but I’m using snowflake today.

Before I build, I need to register these tables in m-Power. And as you see, I already have snowflake set up as a datasource in m-Power, which means that I can pick and choose which data tables I want to use, so I’ll just register the tables I already showed you…so I’ll get that sales table…and…the forecast table.

And, one thing you might run into is the fact that I don’t love these field names from snowflake. Of course, I could easily change the field names in each report or application I build, but I’d rather update them here so they’ll appear correct in any report, dashboard, or general application I build. And keep in mind, I’m changing these field names in my meta data layer in m-Power...it’s not actually changing things in snowflake. And, while I’m at it, I’ll also update the field formatting.

Okay, now that I have the data all setup, so let’s build a report over it.

First things first, I’ll need to name my report and choose my template, which in this case is the interactive report. A template basically gives the report its structure, layout, and features, but can be customized later on.

Okay, so now I need to tell m-Power which database table to use. And I’m using that sales data table that I just registered from snowflake, and want to select all fields in that table.

Moving on to the next step, I need to define a few dimensions for my report. These tell m-Power how to group and subtotal my data…it essentially dictates what data the report is going to let me analyze. Let’s add a few of these fields and then move on.

So, I’m actually all done with the required steps. Of course, there are optional steps like I can add calculated fields, filters, and custom logic…but I won’t add any of that yet…I’ll just go ahead and build. And this process that you just saw is the same process needed to create any type of report, or or really any other basic application. I’ll be able to customize things after it’s built.

Okay, it’s ready now, so let’s check it out. So here I have a basic interactive report. I can drag and drop these dimensions over here if I want to see my data differently. Let’s quickly run it by category. Now I have a breakdown of sales data by product category. And I can drill down into each one here if I want more information. And, that’s great, but…we can make it better.

First, let’s add that forecast data from snowflake that I showed you earlier. I can go back into my tables and join out to that table. I just need to tell m-Power which field to join on, and it handles the rest. Then I can grab a few fields from that forecast table.

Now, let’s jump in to the field settings and rearrange these fields real fast. I want the forecast columns to be right next to that year’s sales column. So I’ll just drag these around where I want them…and…let’s also remove the 2020 data as I only want to see the last 3 years.

And one more thing before I’m done. Let’s add some filters. These filters will let our report users filter out specific data within the report. So, for example, if they only want to see data for a certain product category, they can do that.

Okay, so I have all of that in there and now let’s rebuild this. And that’s the nice thing about m-Power is you can just go back and easily update your existing reports and applications.

It’s built, but let me jump into m-Painter here and do a couple of things. And m-Painter is where I can customize my report even further. And I could do so much…for instance, I could pull in other reports or applications, I can add different components in here, for example, maybe I want to add some nice charts in here. It’s really easy to go in, tell m-Power which data you want, and then add a chart. I won’t add one now, but I just want to give you a feel for a few things you can do.

Right now though, I want to make those filters that I added easier to use. By default, they’re inserted as text inputs, but it’s super easy to change. For instance, I’m changing this category filter into checkboxes so users can filter out data with just a click. I’ll also change the salesperson filter to a dropdown, because there are a quite a few of those and it would be too many checkboxes. And finally, I’ll turn this regiion filter into buttons. There are only 4 options and I can easily fit them on the one row just to save space.

Okay, so now I just need to save a run this and you can see what we have here.

Now, I have a report with my sales and forecast data. And again, I can rearrange the data here so I can see it from different angles. Or, now I can use these filters to quickly narrow down what data to display. For example, maybe I just want to see accessory sales. I’ll select that, and maybe I want to see this salesperson and then drill into the specific accessories they’re selling. Now, I can quickly go in and see which accessories Al has been selling and how he’s doing based on the forecast.

And I could slice and dice this data however I want but I’m sure you have a good idea as to what you can do…and…I’m just scratching the surface here. I could create dashboards and all types of BI and reporting apps over this data, but I won’t get into all of that here for sake of time.

I do hope you have a feeling for how easily you can create reports over your snowflake database so you can quickly analyze your data and view it from different angles. If you’d like to learn more or set up a demo, visit us on the web at mrc-productivity.com.