Uing Custom assembly in SSAS

Sometimes default function and features of SSAS are not sufficient to fulfil the requirement. Therefore, Analysis Services lets you add assemblies to an Analysis Services instance or database. Assemblies let you create external, user-defined functions using any common language runtime (CLR) language, such as Microsoft Visual Basic .NET or Microsoft Visual C#. You can also use Component Object Model (COM) Automation languages such as Microsoft Visual Basic or Microsoft Visual C++.

Following is an example where we have used an assembly to create a custom drillthrough.

Requirement

Requirement is to have huge text column (BLOB) to be displayed when doing a drillthrough in SSAS using excel as tool.

Problem statement

1. By default column which included into drillthrough needs to be present in cube. If we don’t have columns in cube we can not see then in drill through.
2. Having huge text column into the cube will increase the cube storage as well will degrade the performance of cube. Also it will increase cube processing time.

 If we keep the required drillthrough columns in cube, our cube performance will be impacted, if don’t keep it we wont be able to drilldown on it usnign default drillthrough functionality.

Solution

Using an assembly

We come up with a solution where we will not keep the required BLOB columns into the cube, but will use an assembly to query background SQL DB and send a result back into excel.

  

• A dot net based assembly having function to which will call database stored procedure or T SQL. This function will take MDX syntax based parameter as input.  

• To make a call to this assembly use action tab of cube designer and use following kind of syntax –

Call MyAssembly.MyClass.MyFunction(a, b, c)

• This function can internally (within assembly) call multiple functions.
• Function will have a connection string to SQL DB. 
• In Action tab select the return type as dataset. This will allow you to display return result in a new excel sheet.
• This assembly and its function can be used in multiple ways, instead of returning a result set it can also return a web page URL or SSRS report URL. 

I have used assembly created by "https://asstoredprocedures.codeplex.com/" and used it as basic template. i modified it for my purpose and use.

Deploy

This assembly can be deployed using SQL Server Management Studio.

SSMS -> Expand Assemblies folder -> Right click -> Click on New Assembly -> it opens Register Server Assembly window -> choose type and through File Name add your assembly

Security

Whenever we use custom assembly we also faces security challenges.

By default SSAS provide a security options as follows -

                  

Permission Setting	Description
Safe	Provides internal computation permission. This permission bucket does not assign permissions to access any of the protected resources in the .NET Framework. This is the default permission bucket for an assembly if none is specified with the PermissionSet property.
ExternalAccess	Provides the same access as the Safe setting, with the additional ability to access external system resources. This permission bucket does not offer security guarantees (although it is possible to secure this scenario), but it does give reliability guarantees.
Unsafe	Provides no restrictions. No security or reliability guarantees can be made for managed code running under this permission set. Any permission, even a custom permission included by the administrator, is granted to code running at this level of trust.

Conclusion 

Using custom assembly is very easy. It lets you customized your cube/MDX in multiple ways. 

References  

https://asstoredprocedures.codeplex.com/

Connecting to TERADATA using SSIS

Connecting to Teradata using SSIS can be tough a task if you miss installing important prerequisite.

It is best to have some Teradata Expert with you to help you understand required prerequisite installations. I was luck to have friend who provided me all the perquisite installation file in a shot.

Following is a step by step guide

1. Instllation of Teradata prerequisite

Following is list of Drive I have install ( in a sequence)

1. Microsoft Dot net 3.5
2. Shared ICU Libraries For Teradata 14.00.0.1
3. Teradata GSS Client V 14.0.2.2
4. Teradata Data connector V 14.00.0.10
5. .NET Data Provider for Teradata V 14.00.0.1 (For .NET Driver of SSIS)
6. ODBC Driver for Teradata V14.00.0.4  (For having ODBC connection)
7. Teradata SQL Assistant V 14.01.0.2 ( A SQL browser like Todd, help you check connectivity with TERADATA)
8. Teradata Administrator v14.00.0.2
9. Teradata CLIv2 v14.00.0.4
10. Teradata BTEQ v14.00.0.5
11. Teradata ARC v14.00.0.4
12. Teradata MultiLoad v14.00.0.8
13. Teradata FastLoad v14.00.0.7
14. Teradata FastExport v14.00.0.5
15. Teradata TPump v14.00.0.6
16. Java Runtime Environment
17. Teradata Parallel Transporter Base v14.00.0.8
18. Teradata Parallel Transporter Stream v14.00.0.8

Above all are very simple setup file and required no input from installer, just double click and couple of next button will get you through.

Once you installed all these drive I recommend having a restart of your machine/server if possible. 

2. Creating a Data Source connection

·        Create a Shared Data source like following

·        You will get option to select a Tera Data Driver.

·        Select the “.Net Data Provide for TeraData”

·        Fill the details of Server, credentials and database name

·        After filling details do a test connection.

3. Using connection in Source

·        Drag and drop “ADO .NET Source”

·        Double click on this to configure source component like below

·        Simply select data source you have created in above steps and after that you will get option to browse the table list, select the table you want to pull data from.

That’s it. Connect this source further in transformation or destination as per your requirement. 

You will be able to source data from TERADATA.

Apart from this there is another way of connecting is by using Attunity, which you can get it from following –

https://www.microsoft.com/en-us/download/details.aspx?id=44582

if you have followed all the above steps, you should not be facing any problem using this driver. 

You can also view Microsoft documentation on this here http://download.microsoft.com/download/D/B/D/DBDE7972-1EB9-470A-BA18-58849DB3EB3B/SSIS_Teradata_Connectors.docx

MDX Functions

Once you know basics we can have look at the basic functions and properties of MDX.

I hope you have read the previous blog MDX  Introduction.  

In the last post I have shown you a very basic MDX, it just show one member, if you want to see all the members than

SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS,

[Date].[Calendar Year].ALLMEMBERS on 1

FROM [Adventure Works]

Using . ALLMEMBERS to a dimension attribute will give you all the members of that attributes.

Just like this we have some more properties and function

PREVMEMBER

 Gives you previous member of a Dimension Member.  

SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS,

[Date].[Calendar Year].&[2009].PREVMEMBER on 1

FROM [Adventure Works]

So you will get data across Year 2008.

RANGE

Range is not a function but it’s a way of writing MDX, by which you can see data for a range of dimension member.  

SELECT {[Measures].[Internet Order Count]} on 0,

{[Date].[Calendar Year].&[2005]:[Date].[Calendar Year].&[2007]} on 1

FROM [Adventure Works]

FILTER

If you want to filter data on Axis based on a condtion you can use filter function.  Like following

SELECT [Measures].[Internet Sales Amount] ON 0,

FILTER

(

[Date].[Date].[Date].MEMBERS

, [Measures].[Internet Sales Amount]<10000 o:p="o:p">

) ON 1

FROM [Adventure Works]

FILTER takes a set and a condition as input parameter.

In above example we are showing all the dates for which we have Internet sales amount more than 10000.

Instead of a measure you can also have Dimensions as Filter condition.

EXCEPT

Same as FILTER there is Except

--Except

SELECT [Measures].[Order Quantity]

   ON COLUMNS,

   Except

      ([Product].[Product Categories].[All].Children ,

         {[Product].[Product Categories].[Components]}

      ) ON ROWS

FROM

   [Adventure Works]

It gives you result except the condition, so its other way around of Filter.

So it takes a set and a condition as input parameter, but here result will exclude the condition and gives you result.

This are few function I have explained you above, in next post will see more of those. 

MDX Introduction

MDX: Well you must have heard of it. So like you need T SQL or SQL to communicate with a RDBS, same way you need MDX to communicate with Multi-Dimensional OLAP (MOLAP).

MDX is Multi-Dimensional Expression. .  It is part of the OLEDB for OLAP specification which was developed by Microsoft but is now supported by multiple vendors.

Main purpose of MDX is to create query statements for data retrieval. We can also perform some of the management functions, such as the UPDATE CUBE statement etc.

It is good if you have knowledge of SQL as that will help in learning MDX.

MDX seems to be tough, but believe me once you have an understanding of it, it will not be complex and you will enjoy writing it.

Let see how MDX statement looks like –

SELECT, FROM and WHERE clause you have seen in SQL as well, so they are self-explanatory.

 SELECT is to select , FROM is to from where do you want to select. In MDX it will a cube or a sub query. And WHERE to filter your selection that we call it SLICING.

In addition to it, here you define Axis. Well its Multi-Dimensional, isn’t it??  So need different dimensional Axis here.

Let see an example here -

 I consider you have basic knowledge of Facts and Dimensions and you have Adventure Works DB & Cube installed.

SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS,

[Date].[Calendar Year].&[2009] on 1

FROM [Adventure Works]

Well its very simple SELECT statement, where we have selected measure Sales Amount with respect to Calder Year 2007 of date dimension.

Result will be as follows –

So writing first statement was not that tough??

You can put different member of different dimension and can try by your Self.

In the Next blog I will go more detail about MDX and will discuss some of the function of MDX.

SQL Server Installation - Things to Remeber

Some Important things to remember before you go for SQL Server Installation.

1. For local installations, you must run Setup as an administrator. If you install SQL Server from a remote share, you must use a domain account that has read and execute permissions on the remote share.

2 You may need to apply cumulative updates to the original media before you install SQL Server 2008 R2, if you are affected by a known issue in the setup program.

3 Typical stand-alone instances of SQL Server 2008 R2, whether default or named instances, do not use a nondefault value for the Instance ID check box.

   

4  Instance root directory — By default, the instance root directory is C:\Program Files\Microsoft SQL      Server\100\. To specify a nondefault root directory, use the field provided, or click Browse to locate an  installation folder.

5  If you specify nondefault installation directories, ensure that the installation folders are unique to this instance of SQL Server. None of the directories in this dialog box should be shared with directories from other instances of SQL Server.

6  The VIA protocol is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

7  Microsoft SQL Server 2008 R2  requires  Windows Installer 4.5 and .Net Framework 3.5s.

8   Domain Account: This account is a part of your domain that has access to network resources for which it is intended to have permission for. It is always advised to run SQL Server and related services under a domain account with minimum privilege need to run SQL Server and its related services.The Domain Account Password should not  expire.

NOTE:Microsoft recommends that you do not use the Network Service account for the SQL Server or the SQL Server Agent services if an account with lesser privileges is available, because Network Service is a shareable account. Network Service is appropriate for use as a SQL Server service account only if you can ensure that no other services that use the account are installed on the computer. Local User or Domain User accounts that are not a Windows administrator are more appropriate for SQL Server services.

9  Before you upgrade SQL Server, enable Windows Authentication for SQL Server Agent and verify the required default configuration: that the SQL Server Agent service account is a member of the SQL Server sysadmin group.

10  For Analysis Services instances that you deploy in a SharePoint farm, always use SharePoint Central Administration to change the server accounts for PowerPivot service applications and the Analysis Services service. Associated settings and permissions are updated to use the new account information when you use Central Administration.

11  If you have no domain accounts or running under multiple domains and Your SQL Server needs access to other resources in the network, you can create an user account with same username and password across all Servers and use that as service account in your servers.VN:F [1.9.13_1145]

Secondary Axis in SSRS Chart

Secondary Axis Chart

From SSRS 2008 we have Secondary Axis option, ie we can have two measures on two different axis

To do so on your normal chart Drop another Measure/ Numeric Field in “Data Filed” of chart.

Just like following

Then Right click on the second data filed , click on change cart type and then select line chart.

Result will be something like following

After that right click on 2^nd data filed which you made it line chart and go to the series property

è On property window Now go to” Axes and chart Area tab”  à select value axis as a secondary

Press ok and preview your chart.

Now you have two axis in one chart for different data filed like following

ToolTip in SSRS Charts

Well many of us had problem for not having tool tip option in SSRS.

From SQL SERVER 2008 version you have tooltip options in chart.

Just right click on the series and go to series property

On series property you will have Tooltip property option, where you can define what do want to show on tool tip

This is it, set the property according to your requirement and you are good to go. You can see some tool tip just like following -

SSRS 2008/2008 R2 New Features

Data Source

Microsoft SQL Azure data source – connects to SQL SERVER database in cloud.

Microsoft SQL Server Parallel Data Warehouse data source – Connect SQL SERVER parallel data warehouse.

Haven’t really worked with this one. ;).so not much clue on this one.

Microsoft SharePoint List data source – Connects to SharePoint List and pull information from there.

Lot of clients ask for this one and is really is a charm.

Secondary Axis Charts:

Now SSRS Reports can have two Axis. Which allow us to create 2 types of chart in a single chart area, Like combination of bar and Line chart. That has been asked by many clients in past, I am glad now this can be easily achieved.

Tool tip functionality:

Now we can have tooltip on the data point’s gives end user to get the exact number at any data point of a chart.

Sparklines, Data Bars and Indicators

Sparklines and data bars are charts that can be used within table or matrix. This will help to compare information at same time.

Indicators are red, yellow and green light or symbol to visualize data in row.

Text Rotation

Text Rotation Functionality is available, now we can rotate text to 270 degree make it fit into our text boxes.

New Charts and Gauges

Charts and gauges have been improved. They look more attractive.

Map and Spatial data functionality

New Map features allow you to connect your spatial data with Map. Mapping was the key element missing from SQL Server reporting. Including it made SSRS a complete Reporting package.

Integration with SharePoint 2010

Is more effective. Now subscriptions and drill-through links will work directly. End User can access new report builder from SharePoint and create and deploy reports.

Well that’s all for now, will be working on this individual point and publish individual example for every one of this features. Thanks.