Monthly Archives: December 2016

RDP to AWS instance results in hang on “Configuring Remote Session”

Faced this issue a few minutes back and it has to do with the local system settings and firewalls in particular. Here are the steps I tried in the order I tried them

  • First checked if I was typing in the password correctly.
  • Checked if the security group for the instance has been modified to not allow incoming connections
  • Rebooted the machine to see if there was a resource constraints that was preventing RDP sessions.
  • Checked Firewall on local system to verify there was nothing blocking connections
  • RESOLVED:- Changed the wireless network that was recently created on my laptop from PUBLIC NETWORK to WORK

Once done Norton and Windows doesn’t put additional restrictions on what I can access over the network and problem solved.

 

 

 

Loading multiple files into Power Query/ PowerBI

In this post we cover how to work with multiple files of type CSV when loading into Power Query. Very often we have a set of data provided in a specific format on a daily / weekly basis on which we want to perform the analysis using PowerBI. Combing the different files into a single dataset is one of the most common tasks that is performed as part of ETL and here we explore the different ways of achieving it.

OPTION 1

When working with large files or many small files the issue we face most often is the fact that the combined size of the dataset might exceed the capability of PowerBI in Excel or the PowerBI cloud service. In such cases it is often better to load the data into a single table in a database and perform the remaining tasks by connecting to the table in the database instead. The below link has a video on how to import data into a database.

https://www.youtube.com/watch?v=FMZR94hW5xc

OPTION 2

When working with multiple small files we can also import the data into Power Query directly as shown below

Launch Power Query within Excel

Click the Option “From Folder” and navigate to the folder that contains the files you would like to import. The below screenshot will appear with a list of files found in the folder

Click Load , one would think this would be enough but actually if you look closely at the text below “From Folder” you will see it only imports the meta data. The next step is to open the Power Query Editor.

Here we see the same meta data as well as other information as shown in the screenshot below, the column we are interested in is Content which is usually the first column in the list. If you notice the column name there is a double down arrow icon next to the word Content. We need to click this in order to explore the contents of the file.

At which point the file content are displayed and our work is done. In the screenshot below you will notice I am loading data from different types of files with different number of columns. Ideally in the real world you would you a folder containing all files in the same format.

DAX and Date Time or Time Intelligence Analytics

One the best parts of using analytics engines are their ability to understand dates and other types of hierarchies. As human being we use time as a core aspect in our measurement of change. Things like sales week on week or week over week. Year till date, month till date are all important calculations we need to perform. However the challenge has always been that standard approach to RDBMS and EF Codds rules for databases requires no understanding of the nature of the data and treats every row atomically. In other words as far as traditional RDBMS are concerned there is no reason for one row to have any dependency on any other row within the same table. The most common example of this being that there is no inherent order to the data.

With DAX and the tabular model we have the ability to take row by row data and work on it using hierarchies we see in the data without having to make it a cube or some other format. The biggest advantage of this being that we can now perform analytics on small sets of data without having to worry about the complexity of building a cube.

In order to demonstrate this I am using the PowerPivot component within Excel but the steps are similar when even doing this within SSAS tabular model. The three most important things that need to be in place for Time Intelligence are:-

  • A table with the continuous range of dates. Continuous means no missing dates between the minimum and maximum dates within the table.
  • A relationship between the date within the above calendar table and the fact table which is being interrogated.
  • Mark the calendar table as a date table so that DAX knows to treat dates as such.

Below are screenshots to show the steps involved.

Step 1

Create a date table, you can create one directly in Excel or simply import one from within a table in the database, it would look something like this. Notice that I also click Add to Data Model so that PowerPivot know to bring the data from the sheet into PowerPivot model so that I can perform the rest of the steps.

Step 2 Add a relationship between the tables.

At this step please note that PowerPivot only allows one relationship between tables. So if you have multiple dates within the Fact table (the Foreign Key table) then you need to import multiple calendar tables and establish relationships for each one individually.

Navigate to the PowerPivot tab on the top and then press the manage button as shown below

Next click the relationship view button on the bottom right of the screen and drag and drop the column from FK table ( Fact Table) to the PK table ( calendar table).

As you can see in the below query I am using three tables and the table PQDates is joined to the table Power Query using Date column and Value Dt columns.

If you get any errors while dragging and dropping the relationship most likely it is because of

  • Incorrect datatype (one of the date columns might be stored as text, fix this the same way you change datatypes in Excel).
  • You’re dragging and dropping from the wrong table, drag from FK to PK table not vice versa.
  • There might be duplicates in the PK table as a result of which it’s not able to tell which the PK table is.

Step 3 Mark the calendar table as Date Table

Go back to table view by clicking the table view icon on the bottom right of the screen

Open the sheet containing the date’s table, in my case its PQDates. Under the Design tab there is a button for Mark as Date table. Click it and select the column that contains the unique dates from the calendar table. In my case this is the Date Column.

Press OK.

With this we have configures the Date intelligence functionality for DAX the only thing left to do is write a DAX function to leverage it.

In the below formula I am calculating the amounts withdrawn in the previous month so that I can compare it against the current month.

Notice the use of PreviousMonth DAX function. This function automatically returns a list of dates for the previous month based on the month I am looking at. Since the numbers are calculated within the context of a particular month you will see that the Measure at the bottom half of the screen show Blank. This is not an error but simply letting us know that we need to view this measure within the context of months.

Here is what the screenshot will look like without Months.

Here is a screenshot with the months added

And that’s all there is to it. One word of caution here is to make sure that you only have date components in your date columns, if one column has a date like 2016-01-01 and the other has 2016-01-01 13:54:00 then they won’t be able to match up with each other. Make sure the grain for the columns are the same.