Excel Service in SharePoint 2013

Excel services in SharePoint allows use simply, Share and manage the excel workbooks at enterprise level. In excel service we have three sub components for interaction in excel services. Excel calculation service, excel web access, Excel web service, User defined functions, ECMA Script, and REST service as shown in the image below. Excel web access, web services, Calculation service, User defined functions, ECMA script are divided into two groups as Web Front end (front-end server) and application server (back-end application server)

Excel Web Services, REST API, ECMA Script can be divided in to components of front-end server and  User Defined Functions and Excel Calculation services are divided in to components of Application Server.

Excel web access renders the workbooks on a webpage and allows users to interact with the workbooks and explore them. Web Access is the excel service component that visible to the user without installing anything in the client machine.

Excel web services is the component that provides programmatic access to the web service. We can develop the applications to calculate, set and get the values form excel workbooks by using the excel web services.

Excel calculation services helps to load the workbooks, calculate, to call custom user defined functions and refreshes the external data. It will maintain the sessions in the interactions with workbook. Excel services also maintain the caching for opened excel workbooks, calculation states and data query results for improved performance.

User defined functions allows us to call the custom managed code functions and deployed to SharePoint server.

REST API enables to access workbook elements using URL. The URL contains a path for entry point to an aspx page. Excel services REST API enables the developers to explore the content workbook.

We can publish the excel workbooks through excel service in SharePoint. We can manage and secure the published work books according to the enterprise needs. Excel services was introduced in SharePoint 2007 and available in SharePoint 2013 enterprise version. The primary usage of the excel services is business intelligence. Excel workbooks can be connected in to external web sources and workbooks can be published in SharePoint doc library. As mentioned earlier, Excel services will helps to render the excel work book in the browser.

Create Excel Service application

To create the excel service application; “Excel Calculation Service” must be started in the server. To start the excel calculation service, click on “Manage Services on server” link in Application Management.

Select the server where we want to start the service above the services list and in the services list, start the excel calculation service.

Navigate to “Application Manage management” page and Click on “Manage Service Applications” link to create new Excel service application.

Click “New” button and select “Excel Services Application”

Enter the Name of the excel service application in create new excel service application page.

Select “Create new application pool” radio button and enter application pool name and specify the service account to run the application pool. Check “Add this service application's proxy to the farm's default proxy list. ” Check box to add the service to the default proxy list.

SharePoint Machine Translation service error – Immediate transactions for machine translation service are disabled

While exploring machine translation service in SharePoint 2013 I got the issue saying that “immediate translations for machine translation service are disabled”. In SharePoint 2013, many features are depending on the machine translation service synchronous mode. If immediate translations disabled in the machine translation service all those features will not function as expected.

To enable synchronous translations in machine translation service,

In SharePoint Central Administration select Manage Service Applications in Application Management section.

Click on Machine translation service in the service application list.

In “Maximum number of synchronous translation requests (per server)” text box in “Maximum Synchronous Translation Requests” section, enter the values from 1-300. If the value is “0”, then the synchronous transactions are disabled.

SharePoint 2013 - Configure external access for sharepoint sites through mobile devices

We can access the SharePoint sites from mobile devices by using mobile browser or rich client application. To access the site from external devices we need to enable external access. We can configure the external access in three methods,

Reverse proxy server: In this method, reverse proxy server enables us to publish the SharePoint sites on the firewall. To do this we need to configure the reverse proxy server and publish the SharePoint site in that reverse proxy server. After publishingthe site collection , we have to configure the alternate access mapping to the SharePoint site. We have to add that SharePoint site to a zone that allows the cross-firewall access. We configure the cross-firewall access by using the steps below. We need to have the farm admin access to configure the cross-firewall access zone.

Navigate to System Settings in SharePoint Central Application.

Select “Configure cross firewall access zone” in System settings page Farm Management section.

Select the web application to be published the firewall in Cross Firewall Access Zone page Web Application section.

Select the zone that to be published across the firewall in Zone selection for cross firewall access list in Cross Firewall Access Zone section.

Mobile proxy server: Mobile proxy servers pass the mobile browser access directly from SharePoint 2013. This method helps mobile devices work within company IT infrastructure.

Direct access through internet: Here we can place the SharePoint site in extranet and we can access the site by passing the URL directly. Only basic authentication supports this method. Using policy safeguards with the combination of technology is recommended in this method. 

Configuring Machine Translation service in SharePoint 2013

Previous post, I have mentioned how to create new machine translation service. After creating the machine translation service we can configure the service. We can do this by using Central Administration or Power Shell commands.

By Using Central Admin, Go to Application Management -> Manage Service Applications

Select Machine Translation Service

In the machine translation service page, “Enable File Extensions” section, check the file types that we want to translate. If we uncheck any of the check box that file type will disable for translation.

In “Item Size Limits” section we have three text boxes for size limit,

• “Maximum file size of binary files in KB. Microsoft Word documents are binary files” box for Microsoft word document size. The range is between 100-524288. Default value is 51200. Files more than that size will not be translated.
• “Maximum file size of text files in KB. Plain-text, HTML, and XLIFF documents are text files” box for plain text, HTML docs, XLIFF documents. The range is between 100-15360. Default value is 5120. Files more than that size will not be translated.
• “Maximum character count for Microsoft Word document” box for word document. The range is between 10000-10000000. Default value is 500000. Files more than that size will not be translated.

“Online Translation Connection” Section will help Machine Translation service to connect online transaction service. Default is “Use default internet settings”. If we select “Use proxy specified”, we have to enter proxy server name and port number. If we select “Use proxy specified” option, we have to restart Machine translation service.

“Translation Processes” section allows us to specify the number of transaction processes created on the each service application. This value equals to the number of translations that process simultaneously. Default value is “1”. If we change the value, we have to restart the service.

In “Translation Throughput” section allows us to specify the frequency with which groups of translations are started and the number of translations within each group. we have two text boxes,

• In “Frequency with which to start translation” text box, we have to specify the frequency that which groups of translations to be started. Range is 1-59. Default value is 15.
• In “Number of Translation to start” text box, we have to mention the number of translations per process. This specifies that the number of translation started per process every time translations are started. Range is 1-1000 and default value is 200.

“Maximum Translation Attempts” section represents the number of times a translation tried before its status set to fail. Range is 1-10 and default value is 2.

In “Maximum Synchronous Translation Requests”, we have to specify the maximum number of synchronous transaction requests that processed at a time on each server for service application. Range is between 0-300. Default value is 10.

“Translation Quota” section limits the usage of the service queue mode for high throughput values. We have two sections here.

• In “Maximum number of items which can be queued within 24-hour period”, No limit is the default. If we select Limit per 24 hours, we have to type the maximum of number of items that can be queued in 24-hour period. The value must be between 100-1000000.
• “Maximum number of items which can be queued within a 24-hour period per site subscription” No limit is the default. If we select Limit per 24 hours, we have to type the maximum of number of items that can be queued in 24-hour period per site subscription. The value must be between 100-1000000.

“Completed Job Expiration time” specifies the expiry time for the completed job history. Value range is 1-1000 and default value is 7 days. We can select “No expiration” without expiry.

“Recycle Threshold” specifies the number of documents translated by a translation process before it is restarted. Value range is 1-1000 and default value is 100. If we change this value, we have to restart the service.

“Office 97-2003 Document Scanning” specifies whether we need to disable security scanning for 97-2003 office documents. We have to enable this setting only when trust the documents to be converted. Default value is “No”. Click on “OK” to save the configurations.

By Using Power Shell script we can configure the Machine translation service, here is the script do that, 

Set-SPTranslationServiceApplication -Identity "<Machine Translation Service Application Name>" -EnableAllFileExtensions -UseDefaultlnternetSettings -TimerJobFrequency <Frequency of Timer Job> -MaximumTranslationAttempts <Maximum Translation Attempts> -JobExpirationDays <Job Expiration Days> -MaximumSyncTranslationRequests <Maximum Sync Translation Requests> -RecycleProcessThreshold <Recycle Process Threshold> -DisableBinaryFileScan <Disable Binary File Scan>

Here,

“<Machine Translation Service Application Name>” - Name of the service application,

“<Frequency of Timer Job>” - Timer job frequency

“<Maximum Translation Attempts>” – Maximum translation attempt before showing as failure.

“<Job Expiration Days>” – days to kept the job history in history logs

“<Maximum Sync Translation Requests>” – maximum number of translation requests

“<Recycle Process Threshold>” – number of documents to be converted before conversation process.

“<Disable Binary File Scan>” – set the file scan or not.

Example:

Set-SPTranslationServiceApplication -Identity "Machine Translation Service Application" -EnableAllFileExtensions -UseDefaultlnternetSettings -TimerJobFrequency 30 -MaximumTranslationAttempts 3 -JobExpirationDays 14 -MaximumSyncTranslationRequests 20 -RecycleProcessThreshold 300 -DisableBinaryFileScan 1

We have to check Office Web Apps and an Internet connection on our front-end servers are properly configured or not. Once we check that by navigating in to documents, select and open the document using office web app.

In the info section we can “Translate” option to translate the document.

By clicking that we can see the prompt to change the language as shown the image below. 

Machine Translation service in SharePoint 2013

In SharePoint server 2013 we have many new service applications. Machine Translation service is one of the new in service applications. It allows us to translate the site and user content. This service will have a list of APIs, REST and Client Side Object Model support. By using API’s content can pre-translate when required or content can be translated on the fly by the users. Here the process will happen Asynchronously, Synchronously or using streaming.

Machine translation service will translate the text from one language to another language. Here the translation process will happened by using cloud-hosted machine translation service. Machine translation service will share many components with word automation service timer jobs, Documents Queue etc. Here is the simple image that explaining Machine translation service.

Machine translation service will process the translation requests asynchronously and synchronously. Jobs that submitted to the machine translation service will process synchronously when translation timer job executes configurable. Default interval for this is 15 min. We can translate Asynchronous requests manually by running SharePoint translation Service timer jobs through Central Admin or using Power Shell script.

We can create new Machine translation service by using the steps below,

To Configure the Machine Translation service we need to Configure App Management service, Server-server authentication should be configured, App authentication should be configured and User Profile Application proxy be configured as default proxy group.

In Central Admin, Application Management, select Manage Service Application

Select New Machine Translation Service.

In the “Create New Machine Translation Service Application form”, enter the Name. In the Application Pool section, if we want to use existing application, we can select “Use existing application pool”, and select the app pool wanted to use. Else, Create new Application Pool. In my case Iam creating new App pool. The account using for App pool must have the full permissions to User Profile service application.

In the “Partitioned Mode”, we have to select “Run in partitioned mode” if we are going to provide hosting services for other sites. Select “Add this service application’s proxy to the farm’s default proxy list” in the “Add to Default Proxy List” section. When we have multiple web applications and wanted to use them in different set of services, we don’t have to check the check box.

In the Database section, we have to specify the DB server name, DB name and authentication methods. Click on “OK” button.

We can configure the Machine translation service by using the Power Shell script below,

New-SPTranslationServiceApplication -Name "ServiceAppName" -DatabaseName "DBName" -DatabaseServer "DBServerName" -ApplicationPool "AppPoolName" –Default

Here ServiceAppName is the name of the Translation service, DBName is the database name for translation service, DBServerName is name of the DB server, AppPoolName is application pool name. Here is the sample script to create new translation service,

New-SPTranslationServiceApplication -Name "Machine Translation Service Application" -DatabaseName "MachineTranslationService_DB" -DatabaseServer "DBServer" -ApplicationPool "MachineTranslationServiceAppPool" –Default 

In my next post i will write how to configure machine translation service.