Categories
PowerBI Snowflake Uncategorized

Snowflake and Power BI

In the article I will try to give you a small introduction in how to create tables and views in SNOWFLAKE.

Snowflake is basically a Data Warehouse where you can have many databases.

For this article let’s assume that you have already a created user, and also you user has been added to one or many specific roles that have grant permissions of creating tables. When you enter the first time in you may have the following view:

We are going to click on the option “Classic Console” that is on the left pane of the page, as you can see above.

You’ll be redirected to sign into Snowflake again, that is normal, after you’ve signed the classic console view will have 7 tabs.

For this article only Databases and Worksheets will be described. The “Databases” section shows all the databases you have permissions to see, “Worksheets” section is where the queries can be executed, in this link of Snowflake Documentation there is a very detailed explanation of how it works.

Is SQL Supported in Snowflake?

Snowflake supports a common standardized version of SQL: ANSI. This means that most of the common operations needed in warehousing operations, like create, update, insert, select are supported.

CREATING TABLES

Here’s an example of creating a table:

Before you run the query, make sure you’ve chosen the right Data warehouse, database and schema where the table is going to be:

Then you can write your SQL statement:

Now that our table exists, we can load data from a csv file :

1- Go to the Databases section

2- Choose The database of the table:

3- Choose the table and click on “Load Table”

4- Follow the steps of the pop-up window:

  • Its important to set the property of the “File Format” step: “Header lines to skip” to 1 in order to avoid loading the columns headers of the csv.

5- Check that your data has been uploaded.

CREATING VIEWS

Let’s create a simple view in snowflake.

1- As same as tables, make sure to select the correct, data warehouse, database and schema

2- Insert the SQL statement in the worksheet console:

3- Refresh and check the table has been created:

4- Preview data to check the view is the desired one.

SNOFLAKE AND POWER BI

Now that we have our table and view let’s connect our source to Power BI.

1- Go to “Get Data” and then search for Snowflake source:

2- Need to know the server url and the name of the warehouse, also for advanced options you can insert a SQL statement as a part of the connection to the source, in our case we won’t use that.

3- We must enter the credentials and once the identity is verified we will have access to choose the table or view we need.

4- You can choose “Load” or “Transform Data” according to your convenience.

5- Finally you can use the data from Snowflake in Power BI

“So… this has been a very quick and practical guide to start familiarizing with Snowflake, hope this article can be useful.”

Categories
Azure Synapse Analytics Dedicated SQL Pool (Data Warehouse)

How to connect from Excel to Azure Synapse Datawarehouse (Dedicated SQL Pool)

Select in the ribbon the option “Data”

Pic1

Select “Get Data”

Select the option “From Database” and then “From SQL Server Database”

Pic3

A small window will pop up, here you need to introduce the Server URL and the name of the Database even it is said to be optional.

Pic4

Go to “Database” and add the credentials given.

Pic5

Select one by one the tables you need.

Pic6

Once you select the table you can “Load” into Excel or choose “Transform Data” to apply changes in Power Query.

Pc7

Categories
PowerBI

Enhance Relative Date Filter and allow for comparison of two periods

We are going to address the following limitations:

  1. Sometimes you may want to highlight the last two weeks of the month to compare it with the first two weeks of the month, or maybe compare the performance of main KPI’s of the chosen last period.
  2. You can observe that the relative date filter of Power BI uses today’s date (17-Jan-2022) as the last date, and sometimes you need that the last week to start on a previous day, not today. For example, if you only have data until 15-Jan-2022 and today it is 19-Jan-2022, when you want to show Last week, you want it to refer to the days between 10-Jan-2022 and 16-Jan-2022…

3. Sometimes you may want to customize the type of periods you see in the slicer. By default, it shows Days, Weeks, Week Calendars, … You may want to reduce that list and show less options.

4. Weeks start on Sunday or on Monday, depending on your regional settings. You may want to force your dashboard to have weeks always start on a Monday, independently of the regional settings of the viewer.

To resolve these problems, we follow the idea of Josh Trewin, a Sport and Data Scientist in his video and went a little bit further.

As the tutorial says, we need to:

We assume that you have a Dates table in your data model, as can be seen below.

Well, you then need to duplicate your Dates table. In the example below, we created ¨Dates_Disconnected¨ table.

Change the Date slicer to dropdown format.

Change the column of ‘Dates’[Date] in the Date slicer for the column ‘Dates_Disconnected’[Date].

Create values for slicer of periods (with the options that suits you), and measure to get the selected value.

PeriodSelected = ALLSELECTED(Periods[Period])

Create measure that returns a number of days depending on the selected period.

PeriodDays = 
SWITCH([PeriodSelected],
    "Week", 6,
    "Month", 30
)

Create metrics that get the max and min of the period we are going to filter:

PeriodEndLast = LASTDATE(Dates_Disconnected[Date])
PeriodStartLast = [PeriodEndLast] - [PeriodDays]
PeriodEndPrev = [PeriodStartLast] - 1
PeriodStartPrev = [PeriodEndPrev] - [PeriodDays]

Add the period slicer to your canvas and set the selection to single select.

Create the magic filter measure. We are filtering the dates in our related table by returning a 1, without losing the dates and also rows in the fact table, before the selected period, and this is why I love this idea, because it allows you to make a comparison with other periods on the same page of your report. This measure works as filter on a visual level, so actually, it doesn’t filter your date table.

DateFilter = 
VAR _Date = MAX('Dates'[Date])
VAR _Filter = 
   IF( _Date <= [PeriodEndLast], 
      IF(_Date >= [PeriodStartLast], 
        1,  
        0 
      ) 
      ,0
    )
return 

_Filter

Now Add this measure to the charts you want to show the period, and this would be the result so far:

Chart filtered in the last month

Chart filtered in the last week going two days back.

What we have left is to make the measure that will show the amount of spending in the last period selected and the amount of spending in the period before that.

SpendPeriod =
VAR _Value = 
    CALCULATE(
        SUM('Internet Sales Fact'[spend]),
        DATESBETWEEN(
            'Dates'[Date],
            [PeriodStartLast],[PeriodEndLast]
        )
    )
RETURN

    _Value
SpendPeriodPrev = 
VAR _Value = 
    CALCULATE(
        SUM('Internet Sales Fact'[spend]),
        DATESBETWEEN(  
            'Dates'[Date],
            [PeriodStartPrev],[PeriodEndPrev]
        )
    )
RETURN 
    _Value

Now just add cards to see the values, periods and voilà, you have a comparison view:

You can see on the left that we can change the period and go back in time. Hope this helps!!!