November 25, 2016

Wrangling Large Data in Python (Part 1)

Present Day (TL;DR)

Before you go ahead and peek at the answer before understanding how it came about, I urge you to read the backstory and then come back to this section. By the way, what I will show you below is just like 30% of the answer. You can fill in the rest as per your use case.

You make a skeleton like so:

file_name = "folder/largefile.dat"  
nrows, frames = 0, []
for chunk in pd.read_csv(file_name, sep = "|", chunksize = 2500000, usecols = ["Col1""Col2"]):
        nrows += chunk.shape[0]
        print("%d rows processed." % nrows)
    except Exception, e:

The place of interest is the line in bold.
I am merely building a list of data frames by chunk based on the condition that Col1 has values between 0 and 9 inclusive. But you can do a lot of things over there.
Here’s a hint: Create a function where you just take inputs such as the file name, columns of interest, and, most importantly, the function you want to apply to each chunk. Maybe you do not want to extract records. Maybe you want to build count tables, apply transformations, and about a million other things. Good news is: the limit to what you can do is your creativity.
One issue I faced is that when there is an “EOF character found” exception, the skiprows argument does not skip that record. It just stops the iterator and no exceptions are thrown.
My immediate idea for next steps is to try parallelizing this relatively snail-paced version that serially processes the file chunk by chunk.

Early in November 2016 (The month Trump became US President)

The day started like any other day. I came to the office, fired up my machine, and went to fetch my customary cup of java.

This was the day when everything changed. My faithful Jupyter Notebook environment was inundated with super-massive delimiter-separated files upward of 10 GB in size. Suddenly, 30 GB of RAM was not enough. The application stuttered. MemoryError exceptions were everywhere.
It was a welcome challenge to work with such large files without immediately resorting to Spark or such.

I wrote a simple first version that used nrows and skiprows arguments to manually iterate through chunks of the file. This version basically used a loop counter variable that I multiplied by a chosen chunk size. Then I’d manually set the values as nrows = <chunk size> and skiprows = i * <chunk size>. One exercise for the reader is to write this “manual” version.

There was nothing wrong with how the above way worked. But why not use something built-in and Pythonic? For now, the first way, which uses the chunksize argument, works well, and we use it for anything we do with large files.

In the next part of this post, we will discuss performance comparison. We will also begin considering more optimal ways of working with very large files including parallelism.

Watch this space for other posts and articles! If you have any questions you can reach out our team here.

November 23, 2016

Default Print button in SSRS Report is not showing/working in Chrome and Firefox browsers

SSRS toolbox provides Print functionality. But it's browser dependent. It works fine only with Internet Explorer browser, not compatible with Firefox and Chrome browsers.

To achieve this, we've to use custom Print button and JavaScript code which executes on button click.

HTML code for Print button and Report viewer:
 <asp:Button runat="server" CssClass="btn-addschedule-bot" Style="margin-left: 10px;" ID="btnPrint" CausesValidation="true" ValidationGroup="vgSubmit" OnClientClick="printReportClick();" Text="Print Report" />  
 <div style="border: 1px solid #A7B0E8; margin: 0px 10px; padding: 5px; float: left;">  
 <rsweb:ReportViewer ID="rptViewer" runat="server" Height="500px" Style="-ms-overflow-y: scroll" Width="1100px" ShowToolBar="False" ShowParameterPrompts="False" ShowCredentialPrompts="False"></rsweb:ReportViewer>  

JavaScript Code to print a report in Chrome and Firefox:
 <script type="text/javascript">  
     function printReport(report_ID) {  
       var rv1 = $('#' + report_ID);  
       var iDoc = rv1.parents('html');  
       // Reading the report styles  
       var styles = iDoc.find("head style[id$='ReportControl_styles']").html();  
       if ((styles == undefined) || (styles == '')) {  
         iDoc.find('head script').each(function () {  
           var cnt = $(this).html();  
           var p1 = cnt.indexOf('ReportStyles":"');  
           if (p1 > 0) {  
             p1 += 15;  
             var p2 = cnt.indexOf('"', p1);  
             styles = cnt.substr(p1, p2 - p1);  
       if (styles == '') { alert("Cannot generate styles, Displaying without styles.."); }  
       styles = '<style type="text/css">' + styles + "</style>";  
       //--- Reading the report html  
       var table = rv1.find("div[id$='_oReportDiv']");  
       if (table == undefined) {  
         alert("Report source not found.");  
       //-- Generating a copy of the report in a new window  
       var docType = '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "">';  
       var docCnt = styles + table.parent().html();  
       var docHead = '<head><title>Printing ...</title><style>body{margin:5;padding:0;}</style></head>';  
       var winAttr = "location=yes, statusbar=no, directories=no, menubar=no, titlebar=no, toolbar=no, dependent=no, width=720, height=600, resizable=yes, screenX=200, screenY=200, personalbar=no, scrollbars=yes";;  
       var newWin ="", "_blank", winAttr);  
       writeDoc = newWin.document;;  
       writeDoc.write(docType + '<html>' + docHead + '<body onload="window.print();">' + docCnt + '</body></html>');  
       // The print event will fire as soon as the window loads  
       // uncomment to autoclose the preview window when printing is confirmed or canceled.  
       // newWin.close();  
     function printReportClick() {  
       printReport('<%=rptViewer.ClientID %>');  

Print Preview in Chrome browser:

I hope this will help you out to make print functionality working in Chrome and Firefox.

If you have any questions you can reach out our SharePoint Consulting team here.

November 22, 2016

SharePoint 2013/SharePoint 2010: Service Unavailable - HTTP Error 503. The service is unavailable.

Sometimes we get error in SharePoint 2010 or SharePoint 2013 like "Service Unavailable - HTTP Error 503. The service is unavailable" when we try to access a SharePoint site.

Cause of the issue:
IIS - Application Pool of specific Web Application is Disabled/Stopped.

Resolution Steps:
Important Note: If SharePoint Farm is multi tier farm, then, below steps need to be verified in all web servers.

1. Login to SharePoint Server as administrator.
2. Open Internet Information Services (IIS) Manager.
3. Navigate to Application Pools option under server name as shown in below screenshot. And verify that Application Pool for specific web application is in "Started" status. In screenshot, we can see that status of specific Application Pool is stopped.

4. Right click on this Application Pool and click "Start".
5. Open the site in browser and it should work as expected.

If you have any questions you can reach out our SharePoint Consulting team here.

November 15, 2016

How to Clear cache in SharePoint Designer?

When do you need to clear SharePoint Designer cache? 
  • Once you have opened SharePoint site with one user in SharePoint Designer, SPD will save connection details. Now, next time when you try to open same SharePoint site from SPD, It will open that site with saved connection details. If you want to open that site with different connection details (Credentials) then you need to clear SharePoint Designer cache. The cache is not even cleared after doing a reinstall, so this really is the only way to clear it.
  • Sometimes, SPD shows files are already checked out even though they aren't. While trying to check-in, SPD gives error "Cannot perform this operation. The file is no longer checked out or has been deleted." This is due to SharePoint Designer is out of sync with SharePoint and you have to clear cache in order to rebuild it.
  • The cache is not even cleared after doing a reinstall. So, how would you do it? Here, is the only way - detailed steps to clear cache in SharePoint Designer.

Steps to Clear SharePoint Designer Cache:

Step 1: 
  • Close SharePoint Designer if it is open.

Step 2: [Clear Website Cache
  • Open Windows Explorer, Click the address bar and Paste “%USERPROFILE%\AppData\Local\Microsoft\WebsiteCache”.
  • Delete everything within this location.

Step 3: [Clear Web Server Extensions Cache]
  • Open Windows Explorer, Click the address bar and Paste “%APPDATA%\Microsoft\Web Server Extensions\Cache”.
  • Delete everything in this location.

Step 4: [Disable “Cache site data across SharePoint Designer sessions”]
  • Open SharePoint Designer 2010/2013.
  • Navigate to the "File" menu then select "Options" -> "General" -> "Application Options".

  • On the “General” tab, under the “General” heading, uncheck “Cache site data across SharePoint Designer sessions”.

If you have any questions you can reach out our SharePoint Consulting team here.

November 14, 2016

Exchange Server 2013 - EAC/OWA is not accessible

With Exchange Server 2013, there are certain scenarios wherein we might face issues to access Outlook Web Apps and Exchange Admin Center. This might be due to lack of permission or invalid configuration of Exchange Server 2013.

Such errors are as -
  1.  "400 Bad Request Error".
  2. "500 internal server error".
  3. "Sorry, something went wrong".
  4. "404 file not found".  

And for these kind of errors, there are certain remedies that can be followed to resolve.

We faced such issues after re-installing Exchange Sever 2013 on a server. After spending few hours, we've taken various steps from different sources to resolve the errors, And here in this article, the possible solutions are specified that can be applied to resolve it.

Solution - 1 [Check Account Permission]:

Service account that is being used for administrating Exchange Server, should be member of "Organization Management", "Records Management" and "Recipient Management" groups. To check this, run below command in Exchange Management Shell.

Get-RoleGroupMember "Organization Management"
Get-RoleGroupMember "Records Management"
Get-RoleGroupMember "Recipient Management"

If, Exchange Admin user account is not in results, then, just add Exchange Admin account in above mentioned groups in Active Directory.

Open your ECP/OWA URL in browser to verify whether issue is resolved or not. If not, then, proceed further with below steps.

Solution -2 [Check .NET Framework for Application Pools]:

Check that OWA and ECP Application Pools are running on .NET Framework v4.0. It may be incomplete installation of framework that causes the error.

If this is the case then, try running below command as Administrator in Command Prompt:

%windir%\Microsoft.NET\Framework\v4.0.30319\aspnet_regiis.exe -i and then restart IIS.

Error should get resoled now, if not then follow solution -3 as below.

Solution -3 [Rebuild OWA interface]: 

  1. Login to your Exchange 2013 CAS server.
  2. Start the Exchange Management Shell.
  3. Navigate to Exchange 2013 binaries location, for example:"C:\Program Files\Microsoft\Exchange Server\V15\Bin".
  4. Execute the UpdateCas.ps1 PowerShell script to rebuild OWA interface.
  5. Now, execute UpdateConfigFiles.ps1 PowerShell script. [It is recommended to execute this PowerShell script after installing latest cumulative update of Microsoft Exchange 2013].
Ok, now, it's time to check whether the error is resolved or not. If you are still facing the error then follow the below steps.

Solution -4 [Delete Canary Data]:

  • For detailed information on canary data and for guidelines to delete canary data, kindly refer Microsoft Article.
Now, if you are still struggling with the errors, then, you can proceed with next step, re-creating AD schema and system mailbox attributes.

Solution - 5 [Recreate System mailbox attributes and AD schema]:

Next step is to proceed with creating new virtual directories, if you are still struggling with the errors.

Solution - 6 [Create new virtual directories for ECP and OWA]:

Now, it's time to create new virtual directories for ECP and for OWA. So, as first step, we will remove ECP virtual directories with below command in Exchange Management Shell.

  • Remove-EcpVirtualDirectory -Identity "BRExchange1\ecp (Default Web Site)"
  • Remove-EcpVirtualDirectory -Identity "BRExchange2\ecp (exchange back end)"

Now, we will create new virtual directories with below command. Here we have to specify internal and external URL for ECP.

  • New-EcpVirtualDirectory -Server BRExchange1 -InternalUrl "" -ExternalUrl "" -WebSiteName "Default Web Site"
  • New-EcpVirtualDirectory -Server BRExchange2 -InternalUrl "" -ExternalUrl "" -WebSiteName "Exchange Back End"

In second step, we will remove OWA virtual directories as below. Below steps is only required if your facing above error while opening mailbox.

  • Remove-OwaVirtualDirectory  -Identity "BRExchange1\owa (Default Web Site)"
  • Remove-OwaVirtualDirectory  -Identity "BREXchange2\owa (exchange back end)"

Now, we will create new virtual directories with below command. Here we have to specify internal and external URL for OWA.

  • New-OwaVirtualDirectory -Server BRExchange1 -InternalUrl "" -ExternalUrl "" -WebSiteName "Default Web Site"
  • New-OwaVirtualDirectory -Server BRExchange2 -InternalUrl "" -ExternalUrl "" -WebSiteName "Default Web Site"

Hope the possible solutions listed here help you out to resolve the Exchange 2013 - EAC/OWA errors.

If you have any questions you can reach out our SharePoint Consulting team here.