Best ETL Tool

Please volunter yourself for 1 minute survey:

https://docs.google.com/spreadsheet/viewform?fromEmail=true&formkey=dElSZkwzLVZIREk2R3gwa2VZUHYtWWc6MQ

and following are the poll results:

https://docs.google.com/spreadsheet/ccc?key=0AkCj0lCS4PZ7dElSZkwzLVZIREk2R3gwa2VZUHYtWWc

Image | Posted on by | Leave a comment

Microsoft “Data Explorer” Preview for Excel ( Self-service ETL )

On February 27, Microsoft announced a future addition to self-service line-up with Microsoft Data Explorer Preview for Excel. “Data Explorer” is self-service ETL for the Excel power user. The integration between “Data Explorer” and the rest of the Self-Service BI offering in Excel 2013 (including PowerPivot and PowerView) is as easy as it can get… Simply land your data into the Excel sheet, or load it into the Excel Data Model in a single click. If you haven’t seen it, check out the video below to see how Data Explorer enhances the BI experience.

“Data Explorer” is an Excel add-in that enhances the self-service Business Intelligence experience in Excel by simplifying data discovery and access. This preview provides an early look into upcoming features that enable users to easily discover, combine, and refine data for better analysis in Excel. As with most previews, these features may appear differently in the final product.
Prerequisites:
Requires Microsoft Office 2013.
Microsoft “Data Explorer” Preview for Excel supports 32-bit or 64-bit machines.

Requires Windows Vista (with .Net 3.5 SP1 or greater), Windows Server 2008 (with .Net 3.5 SP1 or greater), Windows Server 2008 R2, Windows 7 or Windows 8.

Download & Install Microsoft “Data Explorer” Preview for Excel.

Microsoft “Data Explorer” Preview for Excel will appear as a new tab in the Excel ribbon.

data_Explorer

 

Posted in Microsoft Excel | Leave a comment

Parametrized MDX

When you are building SSRS reports from cube and in your reports you have certain number of parameters and you want to verify MDX before putting into the report. Like SQL you can create parameterized query in MDX using the XMLA .following is the syntax for parameterized query in SSMS.

Syntax:

</Execute

You need to put your MDX in the command tag as mentioned below and run in the XMLA editor of SSMS.

QUERY:

select [Measures].[Account Code Alternate Key] on 0,

FILTER ( [Dim Geography].[Country Region Code].members,

[Dim Geography].[Country Region Code].currentmember.name=@CountryName )on 1

from [Adventure Works DW]

CountryName

AU

Output

following is the output and you get your desired result in the CellData tag with appropriate data type.

[Dim Reseller – Geography – Sales Territory].[Sales Territory Country].[All]

All

[Dim Reseller – Geography – Sales Territory].[Sales Territory Country].[(All)]

0

8

[Dim Reseller – Geography – Sales Territory].[Sales Territory Group].[All]

All

[Dim Reseller – Geography – Sales Territory].[Sales Territory Group].[(All)]

0

5

364312

364312

Posted in MDX | Leave a comment

Microsoft Business Intelligence at a Glance Poster

The poster summarizes the benefits of Microsoft’s BI technologies and depicts them by layer: client (such as BI in Excel), the presentation subsystem (such as SharePoint 2013), information sources (such as Reporting Services), and data sources (such as relational databases). It focuses on the enterprise architect and IT implementer audience. It includes Office 2013 client and server, SQL Server 2012 with SP1, and BI services in Windows Azure.

Please download the PDF from the following link:

Microsoft Business Intelligence Poster

Posted in SQL Server features | Leave a comment

SSAS NameColumn Property format Issue :

I am trying to format the Name column through the format property. Rather than to create the named calculation in the DSV and then use it as a name column. I did this test both in 2008 r2 and 2012 and both have the same behaviour that format is not going to change and it’s showing the original value.

SSAS Name Column Format

Even I put my name in the format though process run successfully.

I did some investigation and found very useful post of Dan English in which he got response from Microsoft:

http://social.msdn.microsoft.com/Forums/zh/sqlanalysisservices/thread/dee2dccc-7321-492c-a68d-2e1375efe237

Response from Microsoft:
The “Format” string for Attribute names is a stub for a later addon and is not implemented. Attribute names will only accept WChar types. Any formatting should be done either in the data source view as a “Named Calculation” or in the source table/view on the relational source.
The above reply is in June 2007 and now is February 2013 this issue still persist in 2012 release.

Conclusion:
Don’t use the Name column format property, use the named calculation technique which mentioned above.

Please read/vote for this Microsoft Connect suggestion:

https://connect.microsoft.com/SQLServer/feedback/details/779761/ssas-namecolumn-property-format-issue

Posted in SSAS | Tagged , | Leave a comment

SSIS for Azure and Hybrid Data Movement

SQL Server Integration Services (SSIS) can be used effectively as a tool for moving data to and from Windows Azure SQL Database, as part of the total extract, transform, and load (ETL) solution and as part of the data movement solution. SSIS can be used effectively to move data between sources and destinations in the cloud, and in a hybrid scenario between the cloud and on-premise. This paper outlines best practices for using SSIS for cloud sources and destinations and for project planning for SSIS projects to be used with Azure or hybrid data moves, and gives an example of maximizing performance on a hybrid move by scaling out the data movement.

To review the document, please download SSIS Hybrid and Azure the Word document.

Posted in SQL Server features | Leave a comment

SQL Server 2012 Edition Highlights

SQL_SERVER_2012_Features

Image | Posted on by | Leave a comment