Power BI Image Indicators in Grids

Creating indicators in grids (matrix or tables) is a quite useful to show color based categories/KPIs. Let’s see how we can manage it using power bi.

Environment:

PowerBI Desktop – October 2017 version

Power BI Report Server October 2017 version

Scenario:

I have two data sets in this scenario, First one is called service which has medical services by categories and second one is called Indicator which has required indicator colors for each and every category. My goal is to show image indicators for each service based on its category.

Table structure as follows in the PBI Desktop.

 

 

Solution:

Firstly, we need to create a relationship between Service and Indicator data sets. I’m going to set up a relationship between these two data sets using ID column in Indicator and Category column in Service.  We can do it in relationships tab in the left pane.

Now, I just need to see the merged dataset before we do any modifications. It will look like this if we create a simple table view.

Since it looks good, what we need to do now is to create a new column in the Indicator table to derive the image URL for each and every color. I have images saved in my SharePoint site with the exact same name as in the color column and then it’s pretty straightforward to create a new column by appending the server name and the column name (and whatever the extension) like this.

Next important thing is to mark the newly created column’s data category as ‘Image URL’ in the modeling tab. Essentially, Power BI will treat it as an image instead of text. Then drag the created column into the table. Default size of the image will be very large for this indicator, so we need to adjust that in the grid properties in order to make it nicer.

Time to test our simple table; this is how it looks when I publish it to the Power BI report server.

Sometimes, it is necessary to change the order of records to make things more user friendly. In this case, category 1 (Emergency care) data in the top adds more sense to it. Since we do not have category column in this table view, we have to sort the category_name column by category (which is just the number). To do that, we can change the Sort by Column property of the category_name column to category.

 

MCSE in Data Management and Analytics

I was successfully able to earn the title MCSE in Data Management and Analytics last week.

https://www.youracclaim.com/badges/bca06dc8-9049-4871-8479-7779829772b1/public_url

After completing MCSA in SQL Server 2012/2014 certification, it requires only one exam (70-466 in my case, which is one of the required exams) to get the MCSE title. Here is the pathway to complete the MCSE in Data Management and Analytics.

https://www.microsoft.com/en-us/learning/mcse-data-management-analytics.aspx

 

Streaming Replication with PostgreSQL

PostgreSQL ? yes, this is going to be the first PostgreSQL post that I’m going to post. Well, I’m going to write my experience with PostgreSQL replication, possible error scenarios, workarounds and best practices. For this post I’m going to stick to PostgreSQL 9.6 on CentOS7 environment. But I’m sure procedure is more or less the same in other linux environments.

Introduction

PostgreSQL offers several solutions to maintain replicas. There are some open source and commercial third party tools available as well. It is needless to say choosing the best-fit solution in the early stages is vital and can save a lot of time.

Streaming replication, which I’m going to elaborate here, depends on transnational log files. Slave/secondary servers use a stream of Write Ahead Log files in order to keep up to date with master/primary server. This is a single master replication mechanism. Even though streaming replication is asynchronous by default, we are going to focus on synchronous mode here.

Environment

At this point, we have following setup. For sake of simplicity, I have assigned ip addresses of master node and slave node  to x.x.x.master and x.x.x.slave respectively.

• Master Node : x.x.x.master
  • CentOS7
  • PostgreSQL 9.6 installed & configured
• Slave Node : x.x.x.slave
  • CentOS7
  • PostgreSQL 9.6 installed & configured

Make sure servers can communicate to each other.

Let’s begin the process of implementing a basic synchronous streaming replication first.

Replication Settings on Master Node

• Create a user named replicator

This is the user that we are going to use to initiate streaming replication.

postgres=# CREATE USER replicator REPLICATION PASSWORD ‘pppp’ LOGIN;

• Add an entry to the pg_hba.conf

pg_hba.conf file normally resides at /var/lib/pgsql/9.6/data/ directory. Adding following entry, we allow access from slave node to master node.

host    replication     replicator      x.x.x.slave/32         md5

• Configure postgresql.conf to start WAL sending process
  • postgresql.conf file normally resides at /var/lib/pgsql/9.6/data directory.
  • wal_level defines how much information written to the WAL. For streaming replication we need replica.
  • we set synchronous_commt to on, in order to confirm all the changes made by a transaction have been transferred to slave nodes before getting the acknowledgement. (default value is also on)
  • max_wal_senders must be set to higher than 0 to enable replication. It is advisable to set this to a slightly higher value than  the number of slave nodes.
  • wal_keep_segments defines the number of wal segments resides at a given time in pg_xlog directory. will discuss about this value later.
  • finally, setting synchronous_standby_names to non empty value, we enable synchronous mode of this replication. it could be multiple values, but here we use only one standby node.

## WRITE AHEAD LOG

wal_level                      = replica

synchronous_commit      = on

## REPLICATION

max_wal_senders                  = 3

wal_keep_segments                = 8

synchronous_standby_names       = ‘slave1’

Restart the postgresql service once done.

Replication Settings on Slave Node

Let’s make changes to the slave node. Before that we need to stop the postgresql service.

• Make a base backup in slave node using master db cluster

Before receiving WAL files, it is necessary to have a base backup of a master node. Even though there are many ways that we can achieve this, preferred candidate is pg_basebackup tool. Make sure to empty the data directory of slave node before executing following command. (backup any config file inside data directory if you already configured any settings. I will assume this is a fresh installation of PostgreSQL)

pg_basebackup -h x.x.x.master -D /var/lib/pgsql/9.6/data/ -P -U replicator –xlog-method=stream

Provide the password for the user replicator and we are good to go.

After successfully getting base backup, make sure postgres user has sufficient permissions to the data directory and its sub directories.

• Configure postgresql.conf file to set up the slave node

Making hot_standby mode to on, we allow queries to run during recovery (slave node is always in recovery mode unless in a failover scenario)

hot_standby = on

• Create recovery.conf file in the data directory 
  • This file can be created as a new file in the data directory or copy the sample  file located in /usr/pgsql-9.6/share/ to the data directory and make the following changes.
  • Standby_mode must be set to on in order to start this node as a slave node.
  • primary_conninfo has the necessary parameters to connect to the master in order to receive WAL files. (applicaton_name is set to value of synchronous_standby_names defined in master node)

standby_mode = on

primary_conninfo = ‘host=x.x.x.master port=5432 user=replicator  password=pppp                                                     application_name=slave1’

Start the postgresql service once done.

Test it !

Running following command in master node, can get information about all the nodes.

select * from pg_stat_replication;

Note :  Large differences between pg_current_xlog_location and sent_location field might indicate that the master server is under heavy load, while differences between sent_location and pg_last_xlog_receive_location on the standby might indicate network delay, or that the standby is under heavy load.

Additional Notes

• PostgreSQL streaming replication does not support multiple master servers.
• Slave node is read-only in this setup, so cannot execute data modifying transactions on the slave node.
• If the server returns error when starting up, good place to start troubleshooting is the postgresql log files, which are located in pg_log in the data directory.
• Configuration files are always messy. Keeping it clean can be useful not only to enhance the readability but also to save some precious time. Found a nice article about keeping it clean.

This is a basic setup of synchronous replication. For sake of simplicity, points like WAL archiving, failover mechanisms have been omitted in this post.

Creating Dynamic Charts Using Matrix – SSRS

Creating multiple dynamic charts based on a group of values can be easily done by a matrix in SSRS.  In that way it won’t be required to change design when new entries are added to the dataset. In addition to that, group value can be used to filter data in the chart. So using one dataset we can achieve this task.

Here we have an example from AdventureWorks db. I have written a simple query to get all the items grouped by their color, quantity and related product category.

What we need to display using this data set ?

I need a separate column chart which will display total value by color, for each product category.

How we are going to achieve this ?

It needs to be dynamic charts which will change according to number of items in product category, therefore we cannot create multiple charts. So basic solution would be to create matrix and insert a chart inside data column.

Steps

First of all insert a matrix to the report and set ProductCategory as row group. Then add a chart inside the data column just like below figure.

Once chart is inserted, we need to set color and total value fields accordingly just as a basic column chart. Set the chart title as ProductCategory column.

That’s pretty much all. But it’s better to hide first column in the matrix and format matrix border colors, so it won’t look like a table. Let’s see how it looks in the preview.

Connecting to PostgreSQL Using SSIS

This post is about few tips that we have to tackle when dealing with postgreSQL databases. First of all what we need is to install correct postgreSQL driver, which is 32bit version in our case, in order to align with SSIS .  You can find drivers from postgresql.org .

Next thing is adding new data source to odbc data source administrator. In order to do that, odbc data source administrator  should be opened from following path (for 32 bit drivers). C:\Windows\SysWOW64\odbcad32.exe . Then add the data source using driver which was just installed. Server name, port, user name and password must be provided in this step.

Well, then it’s time to move towards SSIS. We can test data source by creating new connection manager in SSIS package. Create a new ODBC connection manager and select the created data source name in previous step. That’s pretty much it!

Don’t forget to specify the database in connection string.  Otherwise you will get following error.

Test connection failed because of an error in initializing provider. ERROR [08001] connection string lacks some options.

 

Extract Data From Flat Files in FTP Directory

Download flat files from ftp directory can be useful in many situations. Assume a situation where we need to load data from flat files into database table. In order to do that, first we need to download them to our local server. I came across this sort of scenario recently and thought to share it. This script, which I have written in VB, can be used inside a script task in SSIS control flow.       

Public Sub Main()

        Dim con_manager As ConnectionManager
        Dim ftp_client As FtpClientConnection
        Dim FolderNames() As String
        Dim FileNames() As String
        Dim FileName() As String
        Dim ie As Int32

Try

            Dim FTPConnectionManager As ConnectionManager

            FTPConnectionManager = Dts.Connections(<em>[FTP Connection Manager Name]</em>)
            FTPConnectionManager.Properties("ServerPassword").SetValue(FTPConnectionManager, Dts.Variables([FTP Password])
            FTPConnectionManager.Properties("ServerUserName").SetValue(FTPConnectionManager, Dts.Variables([FTP UserName])

             con_manager = Dts.Connections([FTP Connection Manager Name])
            ftp_client = New FtpClientConnection(con_manager.AcquireConnection(Nothing))

            ftp_client.Connect()
            ftp_client.SetWorkingDirectory([FTP Path])
            ftp_client.GetListing(FolderNames, FileNames)

            ReDim Preserve FileName(FileNames.Length - 1)

            ie = 0
	    For Each file_Name As String In FileNames
                FileName(ie) = file_Name
                ie = ie + 1
            Next file_Name

            ftp_client.ReceiveFiles(FileName, [Destination Path], True, False)
            ftp_client.DeleteFiles(FileName)
            ftp_client.Close()

            Catch ex As Exception

End Try

Dts.TaskResult = ScriptResults.Success

End Sub

MCSA – SQL Server 2012

It’s a great self satisfaction to pass another milestone, MCSA SQL Server 2012.

Querying Microsoft SQL Server 2012

Happy to announce that I have successfully completed the Querying Microsoft SQL Server 2012 exam.

https://www.microsoft.com/learning/en-us/exam-70-461.aspx

IN clause – MDX

SELECT * FROM table1 WHERE code IN (subquery)

How are we going to achieve this sort of query in mdx ? I faced scenario like this where mdx subquery couldn’t be much helpful.

EXISTS

That’s right. Exists is the function that we are going to use here.

SET [AgentCodes] AS
EXISTS([Agent].[AgentKey].Members,
[Agent][AgentCode].Members
)

Simply this helps a lot where there are duplicate agent codes and we want to find each code’s key member.  we can input filtered set into second parameter of EXISTS function to retrieve all the key members.

If I input set of {A001,A002,A003} then I will get 100,101,102,103,104,105 set as the result according to following data set.

Keys	Codes
100	A001
101	A001
102	A002
103	A002
104	A002
105	A003

 

 

Filtering Parent-Child Hierarchy in SSAS

When it’s coming to parent child hierarchies in SSAS dimensions, we can’t filter those as same as other hierarchies. Well, I’m not going to write about parent-child hierarchies here. But in short, we have to create parent-child hierarchies for self referencing fields in tables. Such as employee table, where employees also behave as managers.

So there was a situation where I wanted to filter all the parents using one attribute in the same dimension without affecting to the children. I had agent table with agent parent key and agent key and agent designation(senior manager, manager, associate manager). Simply, I wanted to filter all the parent agents who are “senior managers” and their children. If we filter using designation code as we do normally, we won’t get the correct set of members. Definitely I had to use DESCENDANTS function to get all the children in mdx and before use that I had to filter relevant parents.

this is the normal DESCENDANTS function ,

Descendants(
{[Agents].[Agent Parent].Members},,SELF)

and with FILTER function,

Descendants(

Filter([Agents].[Agent Parent].Members ,[Agents].[Agent Parent].CurrentMember.Propoerties(“Agent Designation”)=”Senior manager”) ,,SELF )

Self in the above example implies that I want only parent members in the result set.

 

Now this set can be used to retrieve a relevant measure like follows.

With Set FilteredAgents AS (

Descendants(

Filter([Agents].[Agent Parent].Members ,[Agents].[Agent Parent].CurrentMember.Propoerties(“Agent Designation”)=”Senior manager”) ,,SELF)

)

Select [Measure] ON 0,

FilteredAgents ON 1

FROM [Cube]