SSIS - Use Dynamic SQL in Oracle Source of Oracle Attunity connector

Recently I was working with Oracle Attunity source to pull the data from Oracle source. There was a requirement we need to pass a dynamic SQL to the source. But what I could see in the Oracle source only two options Table name & SQL Command. There is no option for sending SQL Command as variable.

But by the below way we can pass the dynamic SQL to Oracle source.1.Choose the data flow task in which the Oracle source is implemented.2.Go to properties and click on the Expressions.3.There you can see the properties [Oracle Source].[SQLCommand] .4.Set the expression for the above property for the dynamic SQL.

Chennai SQL Server User Group - November 2013 Session - SQL Server 2014 New Features

On November also i gave a session at Chennai SQL Server User group on SQL Server 2014 New Features

The slide deck and the sample codes can be downloaded from here

http://sdrv.ms/1jfoYlU

Chennai SQL Server User Group - March 2013 Session - Data Warehouse Design Concepts

On March also i gave a session at Chennai SQL Server User group on Data Warehousing design concepts and the session was well received.

In this session , i have presented on below

1. What is Data Warehouse and Need for it
2. OLAP vs OLTP
3. Data warehousing Architecture
4. Dimension modelling and Types of models
5. Types of Dimensions
6. Types of Facts
7. Types of Measures
8. Cube Structure
9. Cube Storage

The presentation can be downloaded from the below link

http://sdrv.ms/14YzWd5

Chennai SQL Server User Group - Feb 2013 Session - Power Pivot & Tabular Model

I had an oppurtunity to present in Chennai SQL Server User group and it was an awesome experience.The Session contents of Feb 2013 session can be downloaded from the below link

http://sdrv.ms/16Haogt

As the session was on Febrauary , i understand this is too late in uploading this content. But better late than never :)

In that session , I have presented on the below

1. Using Power Pivot.
2. How Power Pivot is similar to SSAS tabular Model.
3. Features in Tabular Model.
4. Demo on the Tabular Model.

Hope the session was usefull to whomever attended. All the contents of the session is available in the above link.

What-If analysis – Various ways to implement using MSBI

Presently I am working on the What-If analysis capability in MSBI. So I went ahead on researching on what are the various ways we could implement What-If analysis in MSBI.

Per my current research, there are three ways we could be able to achieve What-If analysis. (If there are more options, Please feel free to add in the comments of this blog)

1.       Using Excel – Allowing user to modify the Cells values in the Pivot Table.

2.       Using Excel – With Predefined Slicer Values.

3.       Using SSRS – With Predefined Slicer Values.

Using Excel – Allowing user to modify the Cells values in the Pivot Table

• We need to enable the Write back option in the cube partition in order to do the what-If analysis.
• User can directly change the values in the cells to see the impact
• User can change the value at the higher level at a hierarchy also. For example, What-if the sales from one quarter should be moved to other quarter.

 Using Excel – With Predefined Slicer Values

• We do not need the write back option to be enabled.
• We need Excel 2010 and above to use this. Use Insert Slicer to use a slicer as shown below.
• User can just select the Slicer dimension attributes to see the impact.

Using SSRS – With Predefined Slicer Values.

• Very similar to the previous one. But it will be a static report in SSRS
• The slicer will be the parameters. The parameter needs to be chosen and click on generate report will show the impact.

 

Also 2 weeks back power view for SSAS multidimensional CTP has been released.

http://blogs.msdn.com/b/sqlrsteamblog/archive/2012/11/29/power-view-for-multidimensional-models-preview.aspx

 I will work on that also to understand the What-If capabilities in power view. I am pretty sure we can generate even better interactive analysis.

SSRS – Easy way to create Stepped Tree Reports

Recently I was asked to create a report in which the user should be able to see a tree kind of structure for each level in geography with expanding & collapsing capability and summary at the each level.  User wanted to have all the level values in the same column. We have designed, developed and delivered the report.

Below is a sample report of the same kind.

After that for another client I was asked to prepare for a demo on Adhoc querying capability. When I was working in report builder I was surprised to see the capability of creating such report using the wizard itself in a pretty simple way.

Below are the steps to create this kind of report

     1.  Launch the report manager URL and open the report builder tool.

     2. Choose the Table or Matrix report from the window and proceed further in the wizard by selecting existing Data set or create a new one by using query designer or existing query.

     

     3.  For this demo i have chosen the Geography columns to be on the row groups and Date columns on the column group with sales value on the values.

    4. In the next screen , there are 3 options shown as below. Choose Stepped , Sub total above . Also check the Expand \ Colapse groups check box.

   5. In the next step choose a style as you want and click finish . For this demo , Ocean has been used.

   6. Now , the report is ready for above specification tree structure in single column with expand \ colapse feature with total & subtotal at each level. This will for sure will save some development effort when we do reports in such format.

   

   Then , i was thinking why this feature is not available in our BI Development studio wizard and i could find an option "Enable Drill down" in the window where we choose page level and group level columns.

  But with this option it is possible to have expand and collapse capability as blocked. But not stepped as per our requirement. Also the Subtotal and total are not displayed.

   

   Conclusion 

        Using the Report Builder Wizard , we will be able to build reports of below three styles

                                  1. Blocked Sub total Below.

                                  2. Blocked Sub total Above.

                                  3. Stepped Sub total Above.

        Report Builder is primarily meant for business users to generate Adhoc reports. But at times developers also can make use of this wizard to save the development effort needed to generate such report manually.Hope this information is helpful. Will meet you all next week with another blog. Till then take care and Bah bye !!!

Microsoft HDInsight Server for Big Data

Microsoft has release HDInsight server for BigData which is Apache Hadoop Compatible. This is released in two variants

HDInsight Server for Windows Server.
HDInsight Services for Windows Azure.

Both of this are available for download and sign up in the below link

http://www.microsoft.com/sqlserver/en/us/solutions-technologies/business-intelligence/big-data.aspx

Also you can do a read about HortonWorks

http://hortonworks.com/blog/Enabling-Big-Data-Insight-for-Millions-of-Windows-Developers/

I am also pretty much excited about this product. I am very sure Microsoft would have made this very user friendly in its style as like its other products. Planning to try out this in forthcoming weeks and will share the learnings here. Will meet you all next week and hope with a detailed blog. Till then Bah Bye.