Difference between SSIS 2008 and SSIS 2012

Since its initial release in Microsoft SQL Server 2005, Integration Services has had incremental changes in each subsequent version of the product. However, those changes were trivial in comparison to the number of enhancements, performance improvements, and new features introduced in SQL Server 2012 Integration Services.
The first change that you notice as you create a new Integration Services project is that Business Intelligence Development Studio (BIDS) is now a Microsoft Visual Studio 2010 shell called SQL Server Data Tools (SSDT). The Visual Studio environment alone introduces some slight user-interface changes from the previous version of BIDS.These enhancements to the interface help you to learn about the package-development process if you are new to Integration Services, and they enable you to develop packages more easily if you already have experience with Integration Services. If you are already an Integration Services veteran, you will also notice the enhanced appearance of tasks and data flow components with rounded edges and new icons.

Add New Project Dialog Box

To start working with Integration Services in SSDT, you create a new project by following the same
steps you use to perform the same task in earlier releases of Integration Services. From the File menu,

point to New, and then select Project.

General Interface Changes

After creating a new package, several changes are visible in the package-designer interface
 SSIS Toolbox You now work with the SSIS Toolbox to add tasks and data flow components to a package, rather than with the Visual Studio toolbox that you used in earlier versions of Integration Services. You learn more about this new toolbox in the “SSIS Toolbox” section of this chapter.
Parameters The package designer includes a new tab to open the Parameters window for a package. Parameters allow you to specify run-time values for package, container, and task properties or for variables, as you learn in the ”Parameters” section of this chapter.
Variables button This new button on the package designer toolbar provides quick access to the Variables window. You can also continue to open the window from the SSIS menu or by right-clicking the package designer and selecting the Variables command.
SSIS Toolbox button This button is also new in the package-designer interface and allows you to open the SSIS Toolbox when it is not visible. As an alternative, you can open the SSIS Toolbox from the SSIS menu or by right-clicking the package designer and selecting the SSIS Toolbox command.
Getting Started This new window displays below the Solution Explorer window and provides access to links to videos and samples you can use to learn how to work with Integration Services. This window includes the Always Show In New Project check box, which you can clear if you prefer not to view the window after creating a new project. You learn more about using this window in the next section, “Getting Started Window.”
 Zoom control Both the control flow and data flow design surface now include a zoom control in the lower-right corner of the workspace. You can zoom in or out to a maximum size of 500 percent of the normal view or to a minimum size of 10 percent, respectively. As part of the zoom control, a button allows you to resize the view of the design surface to fit the window.
Shared Connection Managers
If you look carefully at the Solution Explorer window, you will notice that the Data Sources and Data Source Views folders are missing, and have been replaced by a new file and a new folder. The new file is Project.params
Undo and Redo
A minor feature, but one you will likely appreciate greatly, is the newly added ability to use Undo and Redo while developing packages in SSDT. You can now make edits in either the control flow or data flow designer surface, and you can use Undo to reverse a change or Redo to restore a change you had just reversed. This capability also works in the Variables window, and on the Event Handlers and Parameters tabs. You can also use Undo and Redo when working with project parameters.To use Undo and Redo, click the respective buttons in the standard toolbar. You can also use Ctrl+Z and Ctrl+Y, respectively. Yet another option is to access these commands on the Edit menu.
Note The Undo and Redo actions will not work with changes you make to the SSIS Toolbox, nor will they work with shared connection managers.

Expression Task
Many of the developer experience enhancements in Integration Services affect both control flow and data flow, but there is one new feature that is exclusive to control flow. The Expression Task is a new item available in the SSIS Toolbox when the control flow tab is in focus. The purpose of this task is to make it easier to assign a dynamic value to a variable. Rather than use a Script Task to construct a variable value at runtime, you can now add an Expression Task to the workflow and use the SQL Server Integration Services Expression Language.When you edit the task, the Expression Builder opens. You start by referencing the variable and including the equals sign (=) as an assignment operator. Then provide a valid expression that resolves to a single value with the correct data type for the selected variable. Figure 6-6 illustrates an example of a variable assignment in an Expression Task.
Note The Expression Builder is an interface commonly used with other tasks and data flow components. Notice in Figure 6-6 that the list on the left side of the dialog box includes both variables and parameters. In addition, system variables are now accessible from a separate folder rather than listed together with user variables.
Execute Package Task
               
                                  The Execute Package Task has been updated to include a new property, Reference Type,which appears on the Package page of the Execute Package Task Editor. You use this property to specify the location of the package to execute. If you select External Reference, you configure the path to the child package just as you do in earlier versions of Integration Services. If you instead select Project Reference, you then choose the child package from the drop-down list. In addition, the Execute Package Task Editor has a new page for parameter bindings, as shown in Figure 6-7. You use this page to map a parameter from the child package to a parameter value or variable value in the parent package.

Source and Destination Assistants
The Source Assistant and Destination Assistant are two new items available by default in the Favorites folder of the SSIS Toolbox when working with the data flow designer. These assistants help you easily create a source or a destination and its corresponding connection manager

ODBC Source and Destination
The ODBC Source and ODBC Destination components, shown in Figure 6-8, are new to Integration
Services in this release and are based on technology licensed by Attunity to Microsoft. Configuration of these components is similar to that of OLE DB sources and destinations. The ODBC Source supports Table Name and SQL Command as data-access modes, whereas data-access modes for the ODBC Destination are Table Name – Batch and Table Name – Row By Row.
Embedded qualifiers Another challenge with the Flat File source in previous versions of Integration Services was the use of a qualifier character inside a string encapsulated within qualifiers. For example, consider a flat file that contains the names of businesses. If a single quote is used as a text qualifier but also appears within the string as a literal value, the common practice is to use another single quote as an escape character, as shown here.
ID,BusinessName
404,'Margie''s Travel'
406, 'Kickstand Sellers'

In the first data row in this example, previous versions of Integration Services would fail to interpret the second apostrophe in the BusinessName string as an escape character, and instead would process it as the closing text qualifier for the column. As a result, processing of the flat file returned an error because the next character in the row is not a column delimiter.This problem is now resolved in the current version of Integration Services with no additional configuration required for the Flat File source.

DQS Cleansing Transformation
The DQS Cleansing transformation is a new data flow component you use in conjunction with Data Quality Services (DQS). Its purpose is to help you improve the quality of data by using rules that are established for the applicable knowledge domain. You can create rules to test data for common misspellings in a text field or to ensure that the column length conforms to a standard specification.
To configure the transformation, you select a data-quality-field schema that contains the rules to apply and then select the input columns in the data flow to evaluate. In addition, you configure error handling. However, before you can use the DQS Cleansing transformation, you must first install and configure DQS on a server and create a knowledge base that stores information used to detect data anomalies and to correct invalid data, which deserves a dedicated chapter. We explain not only how DQS works and how to get started with DQS, but also how to use the DQS Cleansing transformation in Chapter 7, “Data Quality Services.”

Collapsible Grouping
Sometimes the data flow contains too many components to see at one time in the package designer,
depending on your screen size and resolution. Now you can consolidate data flow components into groups and expand or collapse the groups. A group in the data flow is similar in concept to a sequence container in the control flow, although you cannot use the group to configure a common property for all components that it contains, nor can you use it to set boundaries for a transaction or to set scope for a variable.
To create a group, follow these steps:
1. On the data flow design surface, use your mouse to draw a box around the components that you want to combine as a group. If you prefer, you can click each component while pressing the Ctrl key.
2. Right-click one of the selected components, and select Group. A group containing the components
displays in the package designer, as shown here:

3. Click the arrow at the top right of the Group label to collapse the group.

Data Viewer
The only data viewer now available in Integration Services is the grid view. The histogram, scatter plot,
and chart views have been removed.
To use the data viewer, follow these steps:
1. Right-click the path, and select Enable Data Viewer. All columns in the pipeline are automatically included.
2. If instead you want to display a subset of columns, right-click the new Data Viewer icon (a magnifying glass) on the data flow design surface, and select Edit.
3. In the Data Flow Path Editor, select Data Viewer in the list on the left.
4. Move columns from the Displayed Columns list to the Unused Columns list as applicable (shown next), and click OK.
Variables
A common problem for developers when adding a variable to a package has been the scope assignment.
If you inadvertently select a task in the control flow designer and then add a new variable in the Variables window, the variable is created within the scope of that task and cannot be changed. In these cases, you were required to delete the variable, clear the task selection on the design surface, and then add the variable again within the scope of the package. Integration Services now creates new variables with scope set to the package by default. To change the variable scope, follow these steps:
1. In the Variables window, select the variable to change and then click the Move Variable button
in the Variables toolbar (the second button from the left), as shown here:
2. In the Select New Scope dialog box, select the executable to have scope—the package, an
event handler, container, or task—as shown here, and click OK:

Expression Result Length
                       
                             Prior to the current version of Integration Services, if an expression result had a data type of DT_WSTR or DT_STR, any characters above a 4000-character limit would be truncated. Furthermore, if an expression contained an intermediate step that evaluated a result exceeding this 4000-character limit, the intermediate result would similarly be truncated. This limitation is now removed.

The SQL Server Integration Services Expression Language now has four new functions:
LEFT You can now more easily return the leftmost portion of a string rather than use the SUBSTRING function:
LEFT(character_expression,number)
REPLACENULL You can use this function to replace NULL values in the first argument with the expression specified in the second expression:
REPLACENULL(expression, expression)
TOKEN This function allows you to return a substring by using delimiters to separate a string into tokens and then specifying which occurrence to return:
TOKEN(character_expression, delimiter_string, occurrence)
 TOKENCOUNT This function uses delimiters to separate a string into tokens and then returns the count of tokens found within the string:
TOKENCOUNT(character_expression, delimiter_string)

Supported Deployment Models
The latest version of Integration Services supports two deployment models:
 Package deployment model The package deployment model is the deployment model used in previous versions of Integration Services, in which the unit of deployment is an individual package stored as a DTSX file. A package can be deployed to the file system or to the MSDB database in a SQL Server database instance. Although packages can be deployed as a group and dependencies can exist between packages, there is no unifying object in Integration Services that identifies a set of related packages deployed using the package model. To modify properties of package tasks at runtime, which is important when running a package in different environments such as development or production, you use configurations saved as DTSCONFIG files on the file system. You use either the DTExec or the DTExecUI utilities to execute a package on the Integration Services server, providing arguments on the command line or in the graphical interface when you want to override package property values at run time manually or by using configurations.

Project deployment model With this deployment model, the unit of deployment is a project, stored as an ISPAC file, which in turn is a collection of packages and parameters. You deploy the project to the Integration Services catalog, which we describe in a separate section of this chapter. Instead of configurations, you use parameters (as described later in the “Parameters” section) to assign values to package properties at runtime. Before executing a package, you create an execution object in the catalog and, optionally, assign parameter values or environment references to the execution object. When ready, you start the execution object by using a graphical interface in SQL Server Management Studio by executing a stored procedure or by running managed code.
For More
Microsoft_Press_eBook_Introducing_Microsoft_SQL_Server_2012

Power Pivot with DAX Expressions

PowerPivot is an innovative adaptation of the "Business Intelligence" software used by large companies to analyze data.   It is available from Microsoft as a free add-in to Microsoft Excel 2010.
You do not need to be a programmer to use PowerPivot. It was designed for the end user.
Every quarter your team spends countless hours preparing reports for the board meeting. Getting data from different reports, entering data into Excel, preparing exhibits and checking and rechecking formulas. After you get the first set of charts ready then you need even more – to explain this quarters variations. So your team spends more hours slicing and dicing the data, preparing exhibits and checking results.  

Best of all, to update the report for a new month or quarter all you have to do is press a “refresh” button. No more entering data, rebuilding charts of rechecking formulas. When you need more detail to explain the results you can use the “slicer buttons” on the right to instantly change the analysis.

To convince him you prepared a walk thru of the four steps you used to prepare the dashboard. Each to the four steps is covered in an appendix to this document.

Step 1:  Think about the data you want to measure.

This is the most important step in the process. Whatever you want to improve, start by measuring it. An easy to maintain dashboard will help to focus everyone’s attention on improvement.

After deciding what to measure you need to find the data. This might require meeting with a technical resource. In this example you got the data from six interconnected database tables and two Excel worksheets.

Appendix 1 - Think about the data you want to measure.

You want to compare actual sales data with the sales budget and with marketing expenses. 

• Actual sales data is in the company’s database.
• The sales budget and marketing expense data are in Excel spreadsheets.

You will want to meet with the database technical support person to identify the tables where the data is stored. If your database is like most you will have to use several tables to get the data you want. This example uses a database sample from Microsoft where the Sales Order Detail contains the sales amount and product code, the Sales Order Header table contains the sales date and territory, the Product table contains the Product Name and Product Sub Category Code, the Product Subcategory contains the Subcategory name and Product Category Code and the Product Category table contains the Category name.

This all sounds complicated but when PowerPivot imports the data from the database it also imports the relationships. There usually is nothing for you to do before you can actually use the data. You don’t need to understand anything about databases!

As in most analyses data you need is not all in the database. In this example the sales budget and marketing expense data are in an Excel spreadsheet. No problem, the PowerPivot database can combine data from multiple sources. Since each row of the Excel data contains the same Territory ID and Product ID codes that are in the database, PowerPivot can combine the data.

Step 2: Connect Excel to the data

PowerPivot has all you need to connect to databases, Excel, Access, text files or sources of data from the web. Once connected PowerPivot stores a copy of the data in Excel. Millions of rows of data can be effectively stored by PowerPivot using a new technology that compresses the data.

When you are ready to update the data you just click on the PowerPivot “Refresh” button and an updated copy of the data in copied into the PowerPivot database.

Appendix 2 – Connect Excel to the data

Starting from a blank workbook, you first open the PowerPivot Window, connect to the sales database and select the sales order header and detail tables, the sales territory table and the product, product sub category and product category tables:

Opening the PowerPivot Window and connecting to the database. 

After connecting to the database, data from the six tables has been imported to the PowerPivot database in the Excel workbook.

Even though PowerPivot has lots of data (121,317 Sales Order Detail records in the above example), workbook size remains manageable.

Pressing the refresh button will reimport an updated copy of the data.

Now you connect to the Excel data. First step is to copy the sales budget into the blank workbook. Make the copied list into a table and create a liked table in PowerPivot.

 

Sales budget copied to Excel.

Add the Excel table to the PowerPivot database.

And the table is included in the PowerPivot database                            .

 

You follow the same steps to copy the Marketing Expenses into Excel and then add to PowerPivot.

One last thing to do in the database is to make a “Date Table”. This is always a good idea for two reasons:

You will want to use a different date measure in your report than exists in the data. In this example you want to report by month and the data is by month, day and year. Also, PowerPivot will sort February before January so it will be helpful for the date table to format the months as yyyy – mm (2011 -12).

You need to filter the actual sales data, budget data and marketing expense data on sales date. Having all three types of data linked to a common date table will speed this process.

 

You build the date table in Excel and link to PowerPivot.

PowerPivot has already imported these relationships from the database:

You need to add the relationships in yellow below so that you can filter across the data month and year.

 

The last three relationships relate the sales budget and the marketing expenses to the sales territory and product tables. These relationships would be automatically added by PowerPivot the first time you produced a report of the data. Since you had to add the date relationships you thought it was easier to add them at the same time.

 

Step 3: Prepare Excel charts to show the measures

You can make Excel pivot tables and pivot charts from the data in the PowerPivot database. Add one pivot table to a worksheet. If you need a pivot chart add the chart to the same worksheet. These worksheets will be hidden in the final dashboard. Use them to get your data and measures right.

Appendix 3 – Prepare Excel charts to show the measures

You request a new pivot table from the PowerPivot window.

The PowerPivot pivot table can combine data for all 9 data tables.

You choose to sum line total and budget sales by category.

Next you add a Pivot Chart

 

 

Step 4: Copy the charts to a single worksheet and add slicers

Copy charts from the pivot table worksheet to your dashboard sheet. Arrange the charts on the dashboard. Then add “Slicers” which are groups of buttons used so filter the data in your charts. Each slicer can be attached to multiple pivot tables. When you click on a year in the slicer on the dashboard then each of the pivot tables attached to that slicer will show data only for that year. In this way slicer buttons on the dashboard worksheet control what data is shown on the dashboard charts.

Your boss is not very fluent in Excel so as you expected his first question was “Why couldn’t you do this with Excel 2003?” You explained that Excel 2010 has three new features that are all part of the new Excel PowerPivot Self-Service BI capability.

Feature 1: Excel PowerPivot allows you to create a simple database within Excel. In the example above you created a database inside of Excel with 9 tables: 6 from the company’s database system and 3 from Excel worksheet data. You were able to specify relationships between the tables so that data could be combined in my charts.

Feature 2: Excel PowerPivot pivot table and charts allowed you to quickly prepare the charts. Unlike the lightweight pivot tables in Excel 2003, these pivot tables can combine data from different tables and come with new formulas that are essential for serious analysis.

At first glance PowerPivot pivot tables and pivot charts are similar to the pivot table and charts that have been in Excel since 1993. But the new PowerPivot pivot table and pivot charts have two very significant differences:

1. They can combine data from multiple tables. (The old pivot tables required you to get all the data in one table.)
2. The new Data Analysis Expression (DAX) formula language that makes it easy to do calculations within the pivot table and line up comparative measures like “”Prior Year To Date”.

Feature 3: Excel Slicers provide a way of selecting data so that multiple charts can be shown together and controlled by one set of buttons.

You told your boss that this is the level of BI that fits your company’s current needs. You know it wouldn’t meet the needs of a much larger company with more IT resources but it can make a real difference in what your company is able to accomplish with Excel.

Appendix 4 - Copy the charts to a single worksheet and add slicers

You copy the Pivot Chart and paste to a new blank worksheet. Then you add slicers.

One slicer can attach to multiple pivot tables.

 

Your dashboard is well underway!

Data Analysis Expressions (DAX) is the standard PowerPivot formula language that supports custom calculations in PowerPivot tables and Excel PivotTables. While many of the functions used in Excel are included, DAX also offers additional functions for carrying out dynamic aggregation and other operations with your data. Bill's new series serves as a progressive introduction to PowerPivot and DAX formulas, examining the functions, operators and values involved, and offering a wealth of practical examples.

As we know DAX and MDX expressions can be used to query Tabular Model in Analysis services. Keep in mind that DAX does not work in SSAS Multidimensional Cube for querying data. We will understand basics of producing results from DAX same as MDX. DAX is promisingly giving quick result than MDX.  So if you are using Tabular model cube solution, then prefer writing DAX unless a complex logic came up which can only be resolved through MDX.

Behaviour of DAX expressions:

• DAX is working as a SQL language with difference of functions and syntaxes
• We need not to write JOINS and GROUP BY clauses in DAX as it already understand that the purpose is to retrieve data in this form. Relationships among the tables of Tabular Model/ Power Pivot model are used for getting JOINS in the DAX query execution.
• We need to understand that DAX is not a query language, it’s a query expression. So we are using some table expressions in DAX to retrieve data in table format and further it is used as result-set/ dataset for the SSRS report.
• To evaluate DAX expressions use MDX query pane in SSMS

Functions

We can now move on to some useful functions for writing DAX as in query language. Please note that list of DAX function is very big but here we are trying to find out the functions which can be used to get data in the form of result set, same as MDX and DAX.
In this series next functions are following:

EVALUATE:

• First step to start with DAX Query Reference. This function can be used to get the data in result set from a table.
  EVALUATE <<Tabular Expression or Name of the table>>
  Argument to the Evaluate function can only be a table name or any expression that return a table. Evaluate   function is added up with different other functions (like VALUES, SUMMARIZE, ADDCOLUMNS, TOPN etc.) to produce different results as per requirements.
  e.g.  Get table data of Employee table in “AdventureWorks Tabular Model SQL 2012”.
  

  EVALUATE 'Employee'

  As per behaviour of DAX, this is similar to SQL as:
  

  SELECT * from [Employee]

  We cannot compare here MDX for this DAX, as there is no way of getting table level attributes in MDX. We need to specify attributes required in MDX SELECT statement.

SUMMARIZE:

• Function return required totals and counts, based on several groups (of different columns or say attributes from different tables in Tabular Model).
  We use SUMMARIZE along with EVALUATE for getting desired result in SSMS.
  SUMMARIZE (<<Table>>, <<Grouping Column1>>, <<Grouping Column2>>,,,<<Name for desired Aggregation1>>, <<Expression for Aggregation or Calculated MeasureName1>>, <<Name for desired Aggregation2>>, <<Expression for Aggregation or Calculated MeasureName2>>)
  Although it looks like a complex syntax but use is very simple and there are so many things we can do with this function.
  E.g. Let say you want to see Currency, Customer ID and Internet Sales from Adventure Works tabular model. Here a SQL query on database can be like:
  

  SELECT [CurrencyName], [CustomerID],SUM([InternetTotalSales]) FROM [InternetSales] A LEFT JOIN Currency B ON B.CurrencyID = A.CurrencyID LEFT JOIN Customer C ON C.CustomerID = A.CustomerID GROUP BY [CurrencyName], [CustomerID]

  Easy, but in MDX it can be as:
  
  

  SELECT NON EMPTY [Currency].[CurrencyName].[CurrencyName].MEMBERS* [Customer].[Customer Id].[Customer Id].MEMBERS ON 1 ,[Measures].[Internet Total Sales] ON 0 FROM [AdventureWorks_Test]

  Again easy.
  But in DAX, don’t worry it’s again easy. We need to keep in mind SUMMARIZE will need first Primary table (more of table which can be base of relationships among desired columns from different tables). Mostly these primary tables can be fact table, which relates different tables. Other than that JOINS and GROUB BYs are not required as SUMMARIZE take care of creating relationships and aggregating data for you.
  DAX for same situation is:
  

  EVALUATE SUMMARIZE('Internet Sales' ,'Currency'[CurrencyName] ,'Customer'[Customer Id] ,"Internet Sales",'Internet Sales'[Internet Total Sales]) ORDER BY 'Currency'[CurrencyName], 'Customer'[Customer Id]

  Here 'Internet Sales'[Internet Total Sales] is already a calculated measure, so Name for Measure is given as "Internet Sales". We can replace it as SUM([InternetTotalSales]), which is aggregation that we need.
  ORDER BY is same as SQL ORDER BY.

Most of the complex queries in DAX revolve around SUMMARIZE, so it’s important to understand its basics.
ADDCOLUMNS:

• The coolest function I’ll say. It gives you a power of creating calculated Column in a table (or tabular expression) at query Run time. I believe there is no way of doing this in MDX (can’t create an Attribute itself in MDX). ADDCOLUMNS(<Table or Tabular Expression>, “<Calculated column Name1>”, <Expression for Calculated column1>,. . . . . . )
  As in SQL we can create a new column while writing query.
  For example (simplest as possible):
  

  SELECT [FirstName], [MiddleName], [LastName] , ([FirstName] + ' ' + [MiddleName] + ' ' + [LastName]) AS FullName FROM [dbo].[DimEmployee]

  Part of expected Result is:

  Now in DAX same query will be:
  

  EVALUATE SUMMARIZE( ADDCOLUMNS('Employee' ,"Full Name" ,CONCATENATE('Employee'[First Name], CONCATENATE(" ", CONCATENATE('Employee'[Middle Name], CONCATENATE(" ", 'Employee'[Last Name]) )))) ,[Full Name] )

  I know that String manipulation in DAX is quite a job, but there is no other way.
  Here if we do not use SUMMARIZE and EVALUATE on ADDCOLUMNS, it will give all the Columns in table Employee with added column as Full Name.
  But if you just want to see one column out of your new table (with added column) use SUMMARIZE to be specific.
  This function makes your life easy to get what you want at the level of Table, and solved many of my issues in SSRS reporting.
• Some other functions are
• Format
• Search
• Filter
• Top N
• 
  

  Compare DAX vs MDX

• Once I was asked to give an opinion on MDX vs DAX. But we know in Software world everything has some pros and cons when compared to other, otherwise if it just have cons it would be thrown to dustbin (say deprecated).
• Well I tried a lot to look for direct comparison of MDX and DAX and to bring my best opinion as per Project Requirements but as usual,no answer in Google for “Compare MDX vs DAX”.
  I prepared a comparison as per my experience in both the languages. Keep in mind DAX is not a query language but it can be used as one, using DAX Query Syntax Reference.
  So below are some comparison points and I wish these will help you out.
  

  S.no	DAX	MDX	Prefer
  1	Performance
  	DAX has some part of better performance on Tabular Model. I was looking at CPU Time comparison for same result set given by MDX and DAX. DAX wins all the time.	Performance is good but when we compare with DAX (on tabular model), it’s a bit low.	DAX
  2	Support:
  	Support In-Memory, DirectQuery and Hybrid query modes of Tabular Model	No support for DirectQuery modes of Tabular Model.	DAX
  3	Query Language:
  	Not a query language, still we can leverage it by few work around (using tabular expressions):a)      Use it as query language in SSRS by writing it in place of DMXb)      Writing and executing it on MDX pane of SSMS to validate the query and data	A well known query language and being used in SSRS and SSMS as separate query option of MDX.	MDX
  4	Ease of writing:
  	Difficult to write as compare to MDX (although many blogs says it’s easy, with perspective of end user).Reason: It behaves as SQL query and we expect a Multidimensional Behaviour. Need use of lot of functions for even few small requirements. Need to know the appropriate relationship between the tables used (in case of non-related table in query, no result will come) Difficult to handle Non Empty behaviour through query Difficult to manipulate filtration in queries. Specially multi-valued filters.	Easy to write as compared to DAX.	MDX
  5	Reporting supports:
  	Long way to go for this investigation. (Decide your reporting tools requirement and investigate or query here in blog)It works with SSRS ( in place of DMX) and ADOMD.net (please refer the link)	Works with SSRS, PPS and ADOMD.net code and lot many other reporting tools and codes.	MDX
  6	Support for Ad-hoc reporting:
  	a)      Power View Reporting: No user interaction is required for querying the Tabular Model. So no use of DAX or MDX.b)      Power Pivot: End user needs to know DAX for creating new measure	a)      Power View Reporting: No use.  b)      Power Pivot: No use	Depends on end user, if he/ she know DAX.
  7	Data Mining:
  	Can’t be used for Data Mining purposes even in Excel.	MDX is required for Data Mining	MDX

  Assumptions:
  Project is on SQ Server 2012, BI Semantic Tabular Model. Else DAX will not work on Multidimensional SSAS solution anyhow.
  Conclusion:
  Out of 7 points, 4 support MDX and 2 DAX.
  But along with that, I believe for a long run MDX is better. It’s been used in many legacy system, developers are more conformable and it provides more flexibility on different reporting services (as per my knowledge).
  Note: I will soon post on query behaviors of MDX and DAX. Saying DAX has better performance than MDX is not always true.

• Slicers

  
  
  Excel 2010 makes it possible to insert slicers to quickly and easily filter pivot tables. However, using the report filter gives the exact same result.

  Below you can find a two-dimensional pivot table. Go back to Pivot Tables to learn how to create this pivot table.

  

  To insert a slicer, execute the following steps.

  1. Click any cell inside the pivot table. The PivotTable Tools contextual tab activates.

  2. On the Options tab, insert a slicer.

  

  3. Check Category and click OK.

  

  4. For example, Click Fruit to only show the fruit exported to each country.

  

  Note: notice how the report filter changed to Fruit. Hold down CTRL to include fruit and vegetables.

  Did you find this information helpful? Show your appreciation, vote for us.

For more see this

http://www.sqlservercentral.com/stairway/75426/
http://www.pivotdashboard.com/Pages/PowerPivotExample.aspx

• 
  
• 
  
• 
  

Is DISTINCT faster than GROUP BY?

I have often seen a recommendation that using DISTINCT instead of GROUP BY is better from a performance perspective. DISTINCT is definitely a more readable alternative, but I wanted to test for myself as to what the truth was.
To compare the performance of the two, I wrote the following simple queries.
The query below helps in identifying the number of records that SQL Server will need to process. On my machine, when executed without the GROUP BY or the DISTINCT clause, the query generates records in excess of 380,000.

1.USE AdventureWorks2012;

2.GO

3.SELECT  COUNT(so1.object_id)

4.FROM    sys.objects AS so1

5.CROSS JOIN sys.objects AS so2 ;

6.GO

The queries below fetch the same information – one uses the GROUP BY whereas the other query uses a DISTINCT.

01.USE AdventureWorks2012;

02.GO

03.--Clean the buffers and procedure cache

04.DBCC DROPCLEANBUFFERS;

05.DBCC FREEPROCCACHE;

06.GO

07. 

08.--Evaluate GROUP BY

09.SELECT  so1.object_id,

10.so1.name

11.FROM    sys.objects AS so1

12.CROSS JOIN sys.objects AS so2

13.GROUP BY so1.object_id, so1.name ;

14.GO

15. 

16.--Clean the buffers and procedure cache

17.DBCC DROPCLEANBUFFERS;

18.DBCC FREEPROCCACHE;

19.GO

20. 

21. 

22.--Evaluate DISTINCT

23.SELECT  DISTINCT

24.so1.object_id,

25.so1.name

26.FROM    sys.objects AS so1

27.CROSS JOIN sys.objects AS so2 ;

28.GO

Before executing the two queries (one with the GROUP BY and the other with the DISTINCT), I had turned on the actual execution plan.

Upon studying the execution plan, the following observations can be made:

1. When the SQL server database engine detected that the 2^nd query did not have any aggregation function, it was smart enough to convert the GROUP BY into a DISTINCT when the query plan is generated
2. There is no difference in the execution plan across both the queries

In conclusion,

DISTINCT is not faster than GROUP BY. In fact, Both DISTINCT and GROUP BY demonstrate equal performance impact.
When aggregations are not being used, DISTINCT is more readable when compared to GROUP BY.

How to add 5 business days to all values of a date column?

First of all do not get confused between 5 days and 5 business days. There is a big difference between these two. 5 Business days means 5 working days(Not holidays). 5 days can be any day. It can be working day or holiday.

This seems to be very difficult question but, we will feel it very easy after knowing the answer. Please follow my clues below to qrite the query.

Clue 1: We need to keep three cases in mind while writing this query. 

• Saturday
• Sunday
• Weekday

Clue 2: If it is Saturday:

• Exclude Sunday(1 day) + Add 5 days which are business days(Mon - Fri) -- So if it is Saturday, add 6 days to the date.

Clue 3: If it is Saturday:

• Exclude 0 days + Add 5 days which are business days(Mon - Fri) -- So if it is Sunday, add 5 days to the date.

Clue 4: If it is Any Weekday:

• Exclude Saturday & Sunday which come in between(2 days) + Add 5 days which are business days -- So if it is Weekday, add 7 days to the date.

I hope now you do not need my query. You can write the query yourself. But I am sharing my query as my gift to you :)

---------------------------------------------------------------------------------------------
USE [ADVENTUREWORKS2012]
GO
---------------------------------------------------------------------------------------------
-- Add 5 Business days to the date.
---------------------------------------------------------------------------------------------
SELECT TOP 3 [ModifiedDate],

CASE WHEN DATENAME(WEEKDAY,[ModifiedDate]) = 'Sunday'
THEN DATEADD(dd,5,[ModifiedDate])

WHEN DATENAME(WEEKDAY,[ModifiedDate]) = 'Saturday'
THEN DATEADD(dd,6,[ModifiedDate])

ELSE DATEADD(dd,7,[ModifiedDate])

END AS Add_5_Business_Days

FROM [Person].[PersonPhone]
---------------------------------------------------------------------------------------------

To add Weekday name column 

select top 10 modifieddate,datename(weekday,modifieddate) as weekname,

CASE WHEN DATENAME(WEEKDAY,[ModifiedDate]) = 'Sunday'
THEN DATEADD(dd,5,[ModifiedDate])

WHEN DATENAME(WEEKDAY,[ModifiedDate]) = 'Saturday'
THEN DATEADD(dd,6,[ModifiedDate])

ELSE DATEADD(dd,7,[ModifiedDate])

END AS Add_5_Business_Days

FROM [Person].[PersonPhone]

Note: 

Business days means we need to exclude our holidays also. We can keep a where condition to the above query to filter that criteria.