ALD

 Deployment Guide

Version 1.0

 

 

 

Table of Contents

1      Document Control 2

1.1       About this document 2

1.2       Purpose of this document 2

1.3       Audience for this document 2

2      Server Configuration Steps. 3

2.1       Install following drivers. 3

2.2       Server Prerequisites. 3

2.3       Kerberos Configuration. 3

2.4       IIS Configuration. 4

3      Web Application Deployment 6

4      SSRS Deployment 7

4.1       Deploy SSRS Report in SharePoint 7

5      Report Subscription db configuration steps. 7

6      Appendix A – SSRS URLs. 8

6.1       BU Dev. 8

6.2       SIT. 8

6.3       UAT. 8

6.4       SIT. 8

6.5       SUPPORT. 9

6.6       PROD. 9

 

 

1   Document Control

1.1     About this document

Author
Distribution	BI Hosting Team
Published	02/01/2017
File Location
Status	Draft
Confidentiality	This document is confidential and must not be distributed outside of IMS Health.
Copyright	© 2014 IMS Health Incorporated or its affiliates. All rights reserved.

 

Changes made to this document

Version	Author	Date	Summary
1.0	Chaitanya Boorlagadda	02 Feb 2017	Document Created

 

1.2     Purpose of this document

This document describes the deployment steps for the ALD Web application.

1.3     Audience for this document

BI Hosting team.

 

2   Server Configuration Steps

There are a number of steps required to deploy the application.

2.1     Install following drivers

·        Microsoft ReportViewer 2010 Redistributabl

·        Microsoft SQL Server 2014 Analysis Management Objects

 

2.2     Server Prerequisites

Each Web Application server requires the following:

 

·        .Net Framework Version 4.0

·        IIS 7.0 with standard Role Features enabled plus the Management Service

 

 

2.3     Kerberos Configuration

·        SPN registration for the ALD URL to the ALD service account

·        Delegations as follows:

o   Decision Center Service Account to ALD SPN

o   ALD Service Account to Decision Canter SPN

o   ALD Service Account to SSRS SPN

o   ALD Service Account to OLAP server SPN

o   TMG Server Account to ALD SPN

 

2.4     IIS Configuration

A Web Application needs to be created as follows:

 

·        In IIS Manager highlight the computername and select ISAPI and CGI Restrictions

·        Set the ASP.NET v4 entries to Allow

 

·        Create a new Application Pool with the following configuration:

o   Name: ALD

o   Classic pipeline mode

o   Service account relevant for that environment (passwords from BI Hosting Infra Team)

o   .Net Framework version 4.0

 

 

 

·        Create a new empty folder at:

o   C:\inetpub\ALD

·        Create a new Web Application with the following configuration:

o   Name ALD

o   Physical path: c:\inetpub\ALD

Use the Application pool created above

·        Authentication :

o   ASP.NET Impersonation and windows Authentication should be enabled

 

3   Web Application Deployment

 

·        Checkout the code from Harvest and download locally. Note that the files will all be read-only. Right click the root folder and select Properties & take the read only flag off.

·        Copy the code within the ALD folder from harvest and paste it in below path:

o   C:\inetpub\ALD

·        Go to IIS and right click and tap on “Add Application”.

·        Add Alias name as Project name (ALD) and give the virtual path of the code location (ALD) in Physical Path and provide External URL has host name based on environment and then click on ok. After adding the application go to bindings and add another binding as internal URL

 

Environment	External URL
BU Dev	http://budev-ald.imsbi.rxcorp.com
UAT	https://uat-ald.imsbi.com
Sup	https://support-ald.imsbi.com
PROD	https://ald.imsbi.com

 

·        Click on Default document and move ‘defalut.aspx’ to top.

 

·        Go to ALD application folder in IIS, edit web.config and do the following changes for every specific environment’s

 

o   Report server path and ReportFolder

 

(URL’s are listed in the next step, below is the highlighted where the URL need to be changed

 

Environment	ReportServerPath
BU Dev	http://budev-auth.imsbi.rxcorp.com/_vti_bin/reportserver
UAT	http://uat-auth.imsbi.rxcorp.com/_vti_bin/reportserver
Sup	http://support-auth.imsbi.rxcorp.com/_vti_bin/reportserver
PROD	http://authoring.imsbi.rxcorp.com/_vti_bin/reportserver

 

             

 

 

 

o   In the path \inetpub\ALD\edit web.config in the connection string based on the environment:

 Change the Connection string based on the environment to allow Web Service connecting to a OLAP Cube

 

 

 

Env	<ConnectionStrings>
BU Dev	<add name="cubeSource" connectionString="Data Source=CDTSOLAP937D;Initial Catalog=WHSVC_UK_M_IMS_1" />
UAT	<add name="cubeSource" connectionString="Data Source=CDTSOLAP556U;Initial Catalog= WHSVC_UK_M_IMS_1" />
Support	<add name="cubeSource" connectionString="Data Source=CDTSSQL187O;Initial Catalog= WHSVC_UK_M_IMS_1" />
PROD	<add name="cubeSource" connectionString="Data Source=;Initial Catalog= WHSVC_UK_M_IMS_1" />

 

o   SQLwrapperconfigdb changes, please contact BI Hosting infra team for generating a SQL bd account and password and this account details will not be shared to any ALD teams,

 

4   SSRS Deployment

4.1     Deploy SSRS Report in SharePoint[c1] 

 

5   Report Subscription db configuration[c2]  steps

         

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

6   Appendix A – SSRS URLs

Use the following URLs for SSRS Deployment:

6.1     BU Dev

TargetDataSetFolder:

 

TargetDataSourceFolder:

 

TargetReportFolder:

 

TargetReportPartFolder:

 

TargetServerURL:

 

6.2     SIT

TargetDataSetFolder:

 

TargetDataSourceFolder:

 

TargetReportFolder:

 

TargetReportPartFolder:

 

TargetServerURL:

 

6.3     UAT

TargetDataSetFolder:

 

TargetDataSourceFolder:

 

TargetReportFolder:

 

TargetReportPartFolder:

 

TargetServerURL:

 

6.4     SIT

TargetDataSetFolder:

 

TargetDataSourceFolder:

 

TargetReportFolder:

 

TargetReportPartFolder:

 

TargetServerURL:

 

6.5     SUPPORT

TargetDataSetFolder:

 

TargetDataSourceFolder:

 

TargetReportFolder:

 

TargetReportPartFolder:

 

TargetServerURL:

 

6.6     PROD

TargetDataSetFolder:

 

TargetDataSourceFolder:

 

TargetReportFolder:

 

TargetReportPartFolder:

 

TargetServerURL:

 

 

 

---

 [c1]John can you please include the steps involved for the deployment of SSRS reports and also Datadriven subscriptions steps.

 [c2]John can you please mention the steps for ReportSubscriptions and related to the SSIS job which triggers to refresh the reportsubscription database

Best Practice SQL, SSAS, SSRS

SQL

1.      Build partitions based on month

2.      Add non clustered index based on month

3.      Add non clustered index based on month + portioning strategy in cube

SSAS

1.      Ensure all reference dimensions are materialized

2.      Only have measures that are used in the cube

3.      Only have attributes in dimensions that are used

4.      Split partitions into 2-20million sizes

5.      Build usage based aggregations based on most common queries

6.      Do not create dynamic sets in cube [Can build measure in background sql to load]

7.      Ensure many to many are <=1 million rows max [latest period only]

 

SSRS

1.      Build MDX efficiently:

a.      Do not have huge sets, build in SSAS

b.      Remove filters – instead have exists or flag attribute in cube

c.      Remove any measures not required in query

d.      Try to push top count into cube

2.      Remove lookups and build into MDX

3.      Remove unnecessary  parameter datasets not required

4.      Try to merge datasets if possible

SSIS Call a web service

A web service is a method over the network used to share information between applications. Web services are very common these days to retrieve game matches, the weather, stock quotes, etc.
In this tip, we are going to work with a web service that pulls weather information.  To begin with, your server running SSIS must have internet access.

Sample Web Service - Global Weather

1. Using your browser, open the http://www.webservicex.net/globalweather.asmx web service and click on the "GetWeather" link.
   
   
   


2. Specify the City and the Country and press the "Invoke" button.
   
   
   


3. You will be able to see weather information such as wind, temperature and other related information in an XML format.
   
   
   

As you can see, this web service is simple. It is a XML file with results that are published on the web and you can invoke it with your application. Let's retrieve this information using SSIS.

Setting up SSIS package to call a web service

1. Open SSDT or BIDS and create a new SSIS Project.
   
   
   


2. Drag and drop the Web Service Task onto the Design pane.
   
   
   


3. Double click on the Web Service Task and create a new HTTP connection.
   
   
   


4. In the Server URL textbox, specify the web service address: "http://www.webservicex.net/globalweather.asmx?WSDL" and press the Test Connection button.
   
   
   
5. Create an empty wsld file.  For this example I created a file called mywsld in the "C:\Webservice" folder.
   
   
   
6. In the WSDL File property specify the path of the file created in the step 5. Make sure that the OverwriteWSDLFile option is set to true and press the Download WSDL button.
   
   
   


7. In the Input tab, specify the City and the Country. In this sample, we used New York and United States. Press OK.
   
   
   


8. In the Output tab, in the File properties, select "New Connection".
   
   
   
9. Select the "Create file" option in the Usage type.
   
   
   


10. In the File textbox, specify the path of the file to store the results of the Web Service and press OK. This is the file where the XML data will be written to when the package is run and the web service is called.
    
    
    
11. Run the SSIS project.
    
    
    
12. Open the file created in the path specified in step 10 and you can see the XML output that was created from the web service.
    
    
    

As you can see, the process to invoke a Web Service is very simple. If you have any questions, feel free to write your comments and questions below.

SSIS Call Web Method

Create a script task in SSIS and enter the following code: 

public void Main()



{

string url = (string) Dts.Variables["User::URL"].Value;

WebRequest request = HttpWebRequest.Create(url);

WebResponse response = request.GetResponse();

StreamReader reader = new StreamReader(response.GetResponseStream());

string urlText = reader.ReadToEnd();

Dts.TaskResult = (int)ScriptResults.Success;



}