Logic Apps, Flow, PowerApps – Extracting List Data to Excel, One Record at a Time.

Today I was faced with an enjoyable challenge.  I was asked to determine if it was possible to create an entry form within Office 365 that could collect data and then email the data collected within the entry form to someone as an attachment.  The attachment had to be in a Excel compatible file format with column headers.

I started off by creating a simple list in SharePoint Office 365. In this test case, my list is titled; Job Requisition.  Here I have a few columns of different data types titled “Job Title” (Single Line of Text), “Job Description” (Multi-line Text Box), “Department” (Choice) “Hire By Date”(Date), “Salary” (Number)

powerapp1

I created a mobile PowerApp form to modernize the user input experience, however a regular list input forms work just as well.  Once a user fills out the input form, data is saved to the list.

powerapp2.PNG

In this example, I decided to use Flow as my programming platform, though in production, Logic Apps may be the preferred solution of choice.

The Flow has 4 steps:

  • Initiation
  • Composition
  • CSV Creation
  • Send Email

powerapp3.PNG

Step 1 – launches the Flow based around a new item being created

 

Step 2 – is a data operation step where we extract the data from the current item list entry into an  array.  I must admit this step was a bit tricky as the syntax isn’t well documented.  

I decided to use the Data Operation – Compose action and then utilize the expression builder to extract the array from the trigger output of the current item that the Flow is running against.

Here we can see how the expression builder is being used to create the array for the object.

powerapp4.PNG

Here is the syntax for the expression.

createArray(triggerOutputs()?[‘Title’]?[‘Job Description’]?[‘Department Value’]?[‘Hire By Data’]?[‘Salary’])
You’ll notice the syntax is a little goofy.

 

Step 3 – Next we use this object created from the array to create a CSV table using another data operation action.

You are also able to create column headers and determine the “values” under the header from the object created by the array.

This allows us to select the display value and not the stored value for drop downs.  We can also use this to help strip out html and markup from our data results

powerapp5.PNG

Step 4 – We use the Send an Email action to build our email. The trickiest part here is specifying the file format we are using and defaulting this to an Excel friendly format.  Since we are using the Create CSV Table action in Step 3, we are able to give our file a generic name with the “.csv” extension for Microsoft Excel to automatically open the file.

powerapp6

Here is the email that is sent.

powerapp7

Here is the CSV / Excel file that is generated.

powerapp8.PNG

Pretty cool and useful. This is also functionality that was not available in InfoPath unless code behind was used.

Advertisements

Scaling SharePoint 2016 On-Premise for Office 365 like performance

I’d like to preface this post with that fact that I will not be focusing on high-availability, disaster recovery, Site Collection and Database allocation, disk subsystems, physical verses virtual server environments, bandwidth, and backups.

Perhaps it is a combination of Microsoft’s release cycle and the changing of the seasons but lately I have been seeing an influx of SharePoint 2016 On-Premise deployments.  Most of these SharePoint 2016 On-Premise deployments are upgrades to SharePoint 2010 and SharePoint 2013 On-Premise environments for customers whose organization is not Cloud ready yet for one reason or another. This recent end of the year demand has led me to come up with a way of comparing Office 365 SharePoint and SharePoint 2016 performance and readiness experience.

The Client’s whose deployments I would like to ‘blog’ about are ranging from 150-500 users with approximately 2 terabytes of SharePoint content.

If we follow Microsoft’s performance scaling for my SharePoint 2016 environment, Microsoft would recommend the following approximate configuration.

Server Specifications – Sample 1

Server Item Quantity
SharePoint 2016 Application / Web Front End CPU 4 Cores – 64bit
  RAM 24GB
  Approximate Hard Drive Configuration C Drive: 100GB
    D Drive: 300GB
  Operating System Windows Server 2016
  Application SharePoint Server 2016
     
SQL Server CPU 4 Cores – 64bit
  RAM 24GB
  Approximate Hard Drive Configuration C Drive: 100GB
    D Drive: 2000GB
    E Drive: 2000GB
    L Drive: 2000GB
    T Drive: 500GB
  Operating System Windows Server 2016
  Application SQL Server Standard 2016

While the above configuration will run SharePoint 2016 the first performance concern we will notice will be related to Search Indexing.

When we attempt to index and crawl content in the above configuration, the page serving performance of our SharePoint environment will be dramatically degraded.  To solve that challenge we need to add at least one dedicated Indexing / Crawling Server.

Server Specifications – Sample 2

Server Item Quantity
SharePoint 2016 Application / Web Front End CPU 4 Cores – 64bit
  RAM 24GB
  Approximate Hard Drive Configuration C Drive: 100GB
    D Drive: 300GB
  Operating System Windows Server 2016
  Application SharePoint Server 2016
     
SharePoint 2016 Search Index CPU 4 Cores – 64bit
  RAM 24GB
  Approximate Hard Drive Configuration C Drive: 400GB
    D Drive: 500GB
  Operating System Windows Server 2016
  Application SharePoint Server 2016
     
SQL Server CPU 4 Cores – 64bit
  RAM 24GB
  Approximate Hard Drive Configuration C Drive: 100GB
    D Drive: 2000GB
    E Drive: 2000GB
    L Drive: 2000GB
    T Drive: 500GB
  Operating System Windows Server 2016
  Application SQL Server Standard 2016

Of course, now we will have adequate page loading times without performance being degraded by Search Indexing, however our Search Query performance may slow.  To address this performance degradation, we will need to add a dedicated Search Query server.

Server Specifications – Sample 3

Server Item Quantity
SharePoint 2016 Application / Web Front End CPU 4 Cores – 64bit
  RAM 24GB
  Approximate Hard Drive Configuration C Drive: 100GB
    D Drive: 300GB
  Operating System Windows Server 2016
  Application SharePoint Server 2016
     
SharePoint 2016 Search Index CPU 4 Cores – 64bit
  RAM 24GB
  Approximate Hard Drive Configuration C Drive: 400GB
    D Drive: 500GB
  Operating System Windows Server 2016
  Application SharePoint Server 2016
     
SharePoint 2016 Search Query CPU 4 Cores – 64bit
  RAM 24GB
  Approximate Hard Drive Configuration C Drive: 100GB
    D Drive: 100GB
  Operating System Windows Server 2016
  Application SharePoint Server 2016
     
SQL Server CPU 4 Cores – 64bit
  RAM 24GB
  Approximate Hard Drive Configuration C Drive: 100GB
    D Drive: 2000GB
    E Drive: 2000GB
    L Drive: 2000GB
    T Drive: 500GB
  Operating System Windows Server 2016

Now we will have adequate Search and page loading performance, but what if we start to utilize a lot of background services and workflows, our page load times will decrease as our Application server is also our Web Front End, so again, we must increase our server footprint.

Server Specifications – Sample 4

Server Item Quantity
SharePoint 2016 Application CPU 4 Cores – 64bit
  RAM 24GB
  Approximate Hard Drive Configuration C Drive: 100GB
    D Drive: 300GB
  Operating System Windows Server 2016
  Application SharePoint Server 2016
     
SharePoint 2016 Web Front End CPU 4 Cores – 64bit
  RAM 24GB
  Approximate Hard Drive Configuration C Drive: 100GB
    D Drive: 100GB
  Operating System Windows Server 2016
  Application SharePoint Server 2016
     
SharePoint 2016 Search Index CPU 4 Cores – 64bit
  RAM 24GB
  Approximate Hard Drive Configuration C Drive: 400GB
    D Drive: 500GB
  Operating System Windows Server 2016
  Application SharePoint Server 2016
     
SharePoint 2016 Search Query CPU 4 Cores – 64bit
  RAM 24GB
  Approximate Hard Drive Configuration C Drive: 100GB
    D Drive: 100GB
  Operating System Windows Server 2016
  Application SharePoint Server 2016
     
SQL Server CPU 4 Cores – 64bit
  RAM 24GB
  Approximate Hard Drive Configuration C Drive: 100GB
    D Drive: 2000GB
    E Drive: 2000GB
    L Drive: 2000GB
    T Drive: 500GB
  Operating System Windows Server 2016
  Application SQL Server Standard 2016

And last but not least, our user base really would like the features of Office Online Server with the ability to Co-Author and work on Microsoft Office documents within the web browser, so once again, we must expand our configuration to the following.

Server Specifications – Sample 5

Server Item Quantity
SharePoint 2016 Application CPU 4 Cores – 64bit
  RAM 24GB
  Approximate Hard Drive Configuration C Drive: 100GB
    D Drive: 300GB
  Operating System Windows Server 2016
  Application SharePoint Server 2016
     
SharePoint 2016 Web Front End CPU 4 Cores – 64bit
  RAM 24GB
  Approximate Hard Drive Configuration C Drive: 100GB
    D Drive: 100GB
  Operating System Windows Server 2016
  Application SharePoint Server 2016
     
Office Online Server CPU 4 Cores – 64bit
  RAM 24GB
  Approximate Hard Drive Configuration C Drive: 100GB
    D Drive: 100GB
  Operating System Windows Server 2016
  Application SharePoint Server 2016
     
SharePoint 2016 Search Index CPU 4 Cores – 64bit
  RAM 24GB
  Approximate Hard Drive Configuration C Drive: 400GB
    D Drive: 500GB
  Operating System Windows Server 2016
  Application SharePoint Server 2016
     
SharePoint 2016 Search Query CPU 4 Cores – 64bit
  RAM 24GB
  Approximate Hard Drive Configuration C Drive: 100GB
    D Drive: 100GB
  Operating System Windows Server 2016
  Application SharePoint Server 2016
     
SQL Server CPU 4 Cores – 64bit
  RAM 24GB
  Approximate Hard Drive Configuration C Drive: 100GB
    D Drive: 2000GB
    E Drive: 2000GB
    L Drive: 2000GB
    T Drive: 500GB
  Operating System Windows Server 2016
  Application SQL Server Standard 2016

As you can see, without mentioning backups, high-availability, and disaster recovery; the footprint for a SharePoint 2016 On-Premise environment with comparable performance to SharePoint within Office 365 will be rather large and costly.

Converting Content Types, Records Center, and PowerShell

I recently started a project with a customer who wanted to implement a SharePoint Records Center and complex Record Retention Policies based around the age of documents, metadata, and Content Types. There was one major problem, the Client was currently using the out of the box “Document” Content Type across all sites and Site Collections and was using library specific Metadata.

My solution was to create Content Types whose Site Column Metadata matched that of the Metadata in specific libraries (using Managed Metadata was not practical for this Client) we then had to programmatically update the Content Types in every library before implement the Records Center, Content Organizer, and Retention Policies.

To do this, I wanted to be able to iterate through a Document Library, all of its Folders, and based conditionally around an existing Content Type, convert to another Content Type  while preserving Metadata including ‘Modified’ and ‘Modified By’ fields….Behold PowerShell to the rescue. 

I will openly admit, this script is not all entirely my original ideas, I leverage a few concepts from other individuals online and combined them together.

Feel free to use this script and modified it as needed.

 

#loading SharePoint Powershell Snapin
Add-PSSnapin Microsoft.SharePoint.PowerShell
write-host “setting web variable”
#The URL of the SharePoint site and subsite that contains the library
$web = Get-SPWeb “https://MySharePointSite/Subsite”
write-host “after setting web variable”
write-host “before setting list variable”
#below is where we define the Library name
#use the library name and not the library URL string.  Spaces are acceptable
$list = $web.Lists[“Documents”]
write-host “after etting list variable”
#Get Item by ContentType
write-host “before foreach item loop”
foreach ($item in $list.Items) {
    write-host “inside foreach item loop”
            
    #Inside the ‘if statement’ below, enter the name of your original content type
    #I left this as “Document”  
   
    #here is where we set the original content type name
    if($item.ContentType.Name -eq “Document”) {
        write-host “inside conditional ContentType logic”
                                        
        write-host “before modifying content type”
        # disable event firing / stop changing modified date
        $myAssembly = [Reflection.Assembly]::LoadWithPartialName(“Microsoft.SharePoint”);
        $type = $myAssembly.GetType(“Microsoft.SharePoint.SPEventManager”);
        $prop = $type.GetProperty([string]”EventFiringDisabled”,[System.Reflection.BindingFlags] ([System.Reflection.BindingFlags]::NonPublic -bor [System.Reflection.BindingFlags]::Static));
        $prop.SetValue($null, $true, $null);
        write-host “setting new content type”
        #here is where we enter the new content type name
        $item[“ContentType”] = “New ContentTypeName”
        write-host “saving changes to content type without modifing the Modified or Modified By Fields”
        # saving content type changes
        $item.SystemUpdate($false)
        # enable event firing
        $prop.SetValue($null, $false, $null);
    }
                                        
}

 

 

On Premise Databases with Office 365 and Power BI

This post is meant to be a semi-technical primer into connection On-Premise data to Office 365 and Power BI…

Behold, the On-Premise Data Gateway.  The On-Premise Data Gateway is a free tool developed by Microsoft that allows the connection of multiple on-premise data sources to connect to an Office 365 environment.  This tool allows developers to build applications within Office 365 that reference on-premise data sources.

A word of caution, in this example, the On-Premise Data Gateway will be running on my local machine, in production, the On-Premise Data Gateway should be executed from a machine that is always on and connected to the internet, perhaps a Window Server.

The On-Premise Data Gateway can be downloaded from the following link.  http://go.microsoft.com/fwlink/?LinkID=820925

Installation

The installation process is essentially a series of “Next”

Step 11

Step 22

Step 33

Step 44

Step 5 – In Step 5 we start the process of entering our Office 365 login credentials.5

Step 66

Step 7 – Here we name our gateway and create a “Recovery Key”7.PNG

Step 8 – Magic!  Our gateway is created and connected to Office 365 and Power BI8.PNG

In this case, I am using the “AdventureWorks Internet Sales” tabular data model, though other data sources exist.  I am leverage SQL Server 2017 Developer Edition running locally on my laptop where my On-Premise Data Gateway is also running.

10.PNG

First, you must log into your Office 365 instance and have the appropriate access to Power BI.  You can use the same credentials that you are using to create your On-Premise Data Gateway to access Office 365 and Power BI and to create your Power BI dashboard, though this is not required.

We will then need to select the gear at the top of the Office 365 / Power BI and select “Manage Gateways

21.PNG

Here we will select “Add data source to use the gateway”  On this screen we enter the data source of our Analysis Services Tabular model and provide on-premise credentials that can access Analysis Services.

22.PNG

Now we will go back to our workspace home page of Power BI and select the “Get” button under “Databases” under “Import or Connect to Data”

9.PNG

Then select “SQL Server Analysis Services11.PNG

Then select “Connect12.PNG

Now we will see a warning and it appears we are unable to connect.13.PNG

However, we need to select the drop down arrow at the top of the screen launch the download of “Power BI Desktop”14.PNG

Power BI Desktop installs by selecting “Next” multiple times and accepting the license agreement.

Once installed, you will receive a prompt to create a Power BI account or to sign in with an existing account.

15.PNG

Once inside of Power BI Desktop, we can then select “Get Data” and choose “Analysis Services16.PNG

On the next screen we enter our Analysis Services Tabular instance and select connect live.  17.PNG

We then navigate to our Tabular Data Model.18.PNG

Now we can build a simple dashboard.  I am going to select “Clusterd column chart” with “Sales Amount” as my Axis and “Order Quantity” as my Value.19.PNG

Then we can select Publish, save our dashboard, and publish the dashboard our workspace within Power BI.20.PNG

Once this is completed, we will now see a successful message.23.PNG

Note, that the success screen states that the …report has been configured to use an enterprise gateway.  Enterprise Gateway was the old name for “On-Premise Data Gateway

 

SharePoint Saturday – Cincinnati 2017

I have just returned from SharePoint Cincinnati 2017 with a renewed sense of purpose in my career.  It is always great to meet new people who share your profession.  It has been a few years since I have been to Cincinnati and I couldn’t find the city anymore welcoming.

At the actual SharePoint Saturday event I felt like I received a crash course on the upcoming changes and enhancements to Office 365 and SharePoint that I missed at Ignite 2017.  There was a strong focus on Flow, PowerApps, and the SharePoint Framework as well as how Microsoft is consolidating platforms such as Skype for Business and Teams..   I learned about the new features and functionality that are coming within the Office 365 ecosystem as well as with SharePoint as a whole.  More and more I am realizing that for most of my Client’s the entire Office 365 stack makes sense.  There was also a lot of great content from the business analyst angle of implementing SharePoint and Office 365 solutions.

I’m excited for the new features and functionality coming along with Office 365 and Microsoft SharePoint.

After SharePoint Saturday and a beer at SharePint, I had the opportunity to take an Uber to downtown Cincinnati to experience the Blink Light, Art, and Projection event.  It was a great time, take a peak at their website.  http://www.blinkcincinnati.com