Matt How

Matt How's Blog

Power BI Mobile Feature Review

In March Microsoft released Deep Touch integration for iOS amongst several other improvements to the Power BI Mobile application. This blog will look at a few of those features and examine some of the areas that still need work.


So far it seems Microsoft are doing a pretty good job of developing Power BI for the mobile platform and this is most apparent when they exploit some of the built-in functionality that make mobiles so handy! One of the best features of the latest iOS is 3D Touch integration and Power BI has fully grasped this bull by the horns. Using a Deep Touch, you can launch a pop-up menu offering some of the most useful features such as search and notifications but also quick access to your recently accessed dashboards.
 2017-04-05-T10-17-08_thumb2_thumb

 

Another big issue that the Power BI team have tackled head on is how to make rich visualisations mobile optimised. For this they have two solutions, the first being the desktop and mobile view options within Power BI desktop.

Desktop view of Report

Mobile view of Report

clip_image002_thumb1_thumb

clip_image004_thumb_thumb

 
The mobile view essentially de-constructs your report and lets you drag and drop your visualisations into the mobile template. By default, this view will always be displayed when viewing the report on a mobile device unless you rotate the device into landscape mode in which case the desktop version loads. I have mixed feelings about this feature. On the one hand, I like that I can see both views but if the report were to remain in mobile view but expand to fill the horizontal space as well, this could open up a lot more opportunities for mobile reporting.

 
However, despite gaining some control in how the report looks on a mobile device there are some pretty major limitations for the time being. Firstly, you cannot specify different properties, such as text size, for the desktop and mobile view. This means that you would need to consider both views when creating a report that will be both mobile and desktop otherwise your visual will be sacrificed as seen above in the deliveries and fragile items cards.


Another drawback is that each visual element has to be snapped to the prescribed grid and this includes the transparent grab handle/border that is used to select the visual. This border is half a grid square tall in the mobile view which means that you get a lot of white space, something you want to reduce in a mobile view.

 
Finally, visuals cannot be overlaid. Where I have circles around some of my cards in the desktop view, this is not possible in the mobile view.  Whilst you can add an image or logo you could not make use of any backgrounds whether they be an image or just a flat colour.
Thankfully, all custom visuals will work in mobile view and any cross filtering, auto play or auto zoom features (maps) are preserved perfectly.
 
Microsoft’s second solution is focussed around dashboards. From within the Power BI service you can arrange your pinned visuals into a mobile dashboard by switching the view as shown below.
image_thumb1_thumb

 

However, the best part is that it if you access a dashboard that doesn’t already have a mobile view then the app will automatically optimise the visuals into a mobile view so you don’t have to!


One of Power BI’s most notable features is Q&A – a method to query your data using natural language. Using a recognisable messenger format this feature is really well replicated in the mobile app and adds another layer of accessibility for non-techie, non-deskbound users.
 2017-04-05-T12-00-43_thumb2_thumb

 

A relatively new feature to Power BI is the ability to add custom links to a dashboard visual. This can be utilised quite nicely in the mobile app as it will make use of the deep linking technology in iOS so that I can launch a relevant app instead of just a web page. Here I have set a google maps URL as the custom URL for the map visual. Clicking on this in the mobile app launches my google maps app, not a webpage!
 2017-04-05-T12-10-49-p1bcut03ue1uis1[2]_thumb

 

Overall I can see that the Power BI mobile team have not tried to just recreate the desktop version but have embraced the mobile platform and have made use of existing features within iOS to present a feature rich app that has a familiar feel to it. Whilst there are some limitations, my next blog will look at how to create a mobile optimised report right from the start so that your users can benefit from mobile BI straight away!

Extracting Data from Salesforce using SSIS

This blog looks at a couple of methods of extracting data from Salesforce and gives a demo of how to output tables and queries into flat files.

 

Salesforce is HUGE! How do I find what I’m looking for?

Thankfully SFDC make it easy for us to find the tables that custom objects as they are suffixed with “__c” (e.g. “Assessment_Questions__c”). This convention carries through to Column level as well, meaning that when a Salesforce standard table is used, such as SFDCs Event table, any custom columns are also suffixed with “__c”.

 

Can I use SQL?

Salesforce utilises a modified version of SQL called SOQL (Salesforce Object Query Language). There are some Keyword similarities such as starting a query with “SELECT” and calling a table with “FROM” but if you want to limit a query result to a fixed number of rows you need to add “LIMIT n” to the end of the query string, not the start!

Additionally, SFDC provide some handy functions to make SOQL slightly easier on us developers. I have listed a few that I used below but a full documentation site is available here - https://help.salesforce.com/articleView?id=customize_functions.htm&type=0

Function

Usage

Example

LAST_YEAR

Provides a data range from Jan 1st of the previous year to Dec 31st of the previous year.

SELECT AccountId FROM Customers WHERE CreatedDate > LAST_YEAR

LAST_N_MONTHS: n

Provides a data range from the last day of the previous month and continues for the past n months.

SELECT Id FROM Account WHERE CreatedDate > LAST_N_MONTHS:12

BLANKVALUE

Similar to ISNULL. Will provide a substitute value if the field is null

SELECT BLANKVALUE(AddressLine2,”N/A”)

FROM…

 

How do I connect to Salesforce?

In order to access the Salesforce system, you will need a username, password and security token. From my research there are two viable methods for connecting to Salesforce using SSIS which are using an API component or using a linked server. Both have their pros and cons but generally I have found the API route to be more efficient. There are a number of 3rd Party providers around and I have listed a few of these below.

Provider

Comments

Link

C – Data (API)

 

http://www.cdata.com/drivers/salesforce/ssis/

Task Factory (API)

Forces you to down load all 50 components. Can use SSIS variables and SOQL

http://pragmaticworks.com/Products/Task-Factory/Free-Trial

Kingsway Soft (API)

Simple interface and can use SSIS variables. Auto generates SOQL. Cant edit error output from main component

http://www.kingswaysoft.com/products/ssis-integration-toolkit-for-salesforce/download

DB – Amp (Linked Server)

Allows you to use OLE DB Connection manager

http://forceamp.com/trial.htm

 

You can use these steps below to access SFDC and start retrieving data.

1.      Download an SFDC SSIS component. For this demo I have used the Kingsway Soft component available from the link above.

 

2.      Run through the install wizard and once complete, open Visual Studio.

3.      Create a new SSIS project and add a Data Flow Task. Rename this to Kingsway Soft Demo.

 

clip_image002[12]

 

4.      Go into the data flow and right click in the toolbox, hit “Refresh Toolbox”. Now you should see 2 new components called “Salesforce Destination” and “Salesforce Source”.

 

clip_image005[12]

 

5.      Drag a Salesforce Source onto the design surface and double click to edit the properties. Drop down in “Connection Manager” and select “New”. Fill out the details required using the below connection info.

 

Username: mih@adatis.co.uk

Password: S4lesforce

Security Token: oKar72dhKcBAWlq0i4M0RF7ua 

 

clip_image007[12]

 

6.      Test the connection, and click “OK” once it has connected successfully. From the component properties screen use the Source Type to select “Object Query”. Add this SOQL statement into the text editor below. Click “OK”.

 

clip_image009[12]

TIP!! – If you select “Object” from the Source Type drop down and choose your table you can then switch the source type back to “ObjectQuery” and the full SOQL statement to retrieve all columns will be generated automatically.

 

7.      Add a flat file destination and configure the connection manager. Enable the data viewer between your SFDC component and the flat file destination and preview the data that is being extracted from SFDC.

 

clip_image011[12]

 

8.      From here can then repeat the process to build up a package that will bring down all the files that you might need for your solution.  

There are additional tools such as Salesforce Workbench that will allow developers greater access to the underlying solution but these will be covered in a different blog. If this solution does or doesn’t work for you then let me know!