When working with your database in BIME, there’s a lot going on behind the scenes. Every time you select a measure, attribute, or work with a filter this is translated as a SQL query to the database and the results are returned back to BIME as a beautiful visualization. It’s pretty neat and in this post I’ll outline the differences in using a SQL editor vs. working with your data in BIME.
So, let’s say we have an online retail store that sells phones, tablets, and accessories throughout the world. (We’re a pretty big deal.) And we want to take a look at the revenue our store generates in the United States last year. We have thousands of transactions and store all of this data in our data warehouse, for which we leverage Amazon Redshift. Our transaction table looks like the one below:
In our favorite SQL editor (SQL Workbench/J in this case), we’d probably write a query similar to this:
Note: You may need to download the relevant JDBC driver before getting started, you can read more about the Redshift driver here.
While this query returned what I was looking for, it’s hard to see at a glance how revenue between different states compare and I’d have to write additional queries to see which product categories are driving revenue.
In BIME, we’ve made it easier to explore and ask questions of your data in a visual way. Let’s take the same example and talk a little bit about how we’d achieve the same results in BIME. To achieve the same results, I can select revenue from my measures, add state and country to my columns and filter our country attribute to the USA. With a total of six clicks, I was able to achieve the same result and see that we’re performing very well in the midwest as well as the state of California.
And if I want to dig a little deeper into this data I can add category to rows and see which products are generating the most revenue in these locations. In one glance, I can see that our tablet sales are driving most of our revenues across the USA.
Back in our SQL editor, I could achieve the same results by adding category to my select clause but that makes things a little messy. And what if I wanted to see how well our sale of tablets grew in 2015? Well I’d have to write a new query similar to this:
In BIME I could achieve the same results by decomposing my data by year, month and filtering our date to “Last year”. This only takes about 4 clicks.
Now, our growth looks pretty great, something our company should be very proud of. But I’m interested in what products specifically lead to such a great month in August so we can replicate this success in the future. In SQL, I’d write a query similar to this to breakdown our sales by product sub_category:
In BIME, I could achieve the same results by clicking decompose on the month of August and selecting sub_category. We can see that our sale of iPads in August did very well!
If I was interested in understanding the trend of iPad sales and wanted to see our growth, it could be beneficial to apply a running total to our query. In SQL, I could write a query that looks like:
In BIME, we have a great function built-in, under results manipulation, that lets you add a running total within a few clicks. I’d first decompose ‘ipad’ by day and then apply a Result Path Calculation. It looks something like this:
Our end result looks like this:
What’s great about this pre-built results manipulation feature is that I only have to click a few times to achieve the same result. BIME intelligently visualizes the data for us in the most in the most easy to view way. It doesn’t matter what database you’re using, BIME knows how to communicate with it and will help you ask questions of your data quickly and efficiently. And if you’re ever interested in seeing the queries that BIME is sending to your database, you can navigate to ‘SQL Options’ under the ‘Results Manipulation’ tab to take a peek.