John's Technical Blog

Friday, 24 March 2017

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

Thursday, 23 March 2017

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

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

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

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

Open SSDT or BIDS and create a new SSIS Project.

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

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

In the Server URL textbox, specify the web service address: "http://www.webservicex.net/globalweather.asmx?WSDL" and press the Test Connection button.
Create an empty wsld file. For this example I created a file called mywsld in the "C:\Webservice" folder.
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.

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

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

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.
Run the SSIS project.
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;

}

web confir for sharepoint reports