As soon as a variable is entered, the expression will be visible for the machine and evaluated in the query. What are these doing? Everything wrapped inside those brackets will be invisible to the source code. ![]() Step 2: OptionalityĪlright, now that we know how to add a variable, let’s go a step further and add some square brackets: ]. But now, we can enter any number and will eventually get the results if there’s a match (which will be between values 20 here). Otherwise the SQL code will fail, b/c there would be nothing behind that equal sign. I enabled the “required”-switch to force a default value. ![]() Interested in how I shifted that data from MS SQL Server to Postgres? Take a look here. Oh and by the way, the database I am using is the AdventureWorksDW2019 sample database with PostgreSQL. With variables you enable your co-workers to do their own research in depth and do filtering in your native query without them needing to know anything about SQL. So you change the code over and over again, which can add up really fast.² But maybe your co-worker wants to inspect the data some more and comes to you for every single change in the result set. So what if the same question is asked for another time period? Well, that’s super easy, barely an inconvenience. Now the query may look like this: SELECT SUM(CASE WHEN salesterritorykey = 1 THEN salesamount END) AS sales_territory_1, SUM(CASE WHEN salesterritorykey = 1 THEN salesamount END) / SUM(salesamount) AS ratio FROM dbo.fact_internet_sales WHERE EXTRACT(YEAR FROM orderdate::DATE) = 2014 Unfortunately, Metabase cannot handle this by its built-in logic, but thanks to its native SQL-capability, you can use it as an IDE. Let’s say, a colleague comes up to you and asks if she can get the numbers of 2014 year’s sales for territory 1 and its share of the total sales. ![]() But once you wrote that query, it’s static. □ This is no different with Metabase but sometimes even the mightiest tools meet their limits and a manually written SQL query is the better way. You know, just a few clicks and that chart is done. Now, typically you should have a ready-to-use-by-clicking area where all your reporting tables lay and can be queried by your BI tool of choice. First, allow me to congratulate you to this choice, b/c who needs Tableau and Power BI anyway? In Metabase you can write native SQL queries and visualize them in the same run (it’s Open Source, hence free to use)¹. Image: Made by the author with a lot of effort Introduction
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |