SQL Server Integration Services provide a versatile way of reading Excel files into SQL Server. A task like this illustrates the advantages of the graphical approach of SSIS. Andy Brown explains.  Read more...

Microsoft has introduced a new BI product that will help simplify the data discovery phase for Excel users. “Data Explorer” is an Excel add-in that enhances the self-service Business Intelligence experience in Excel by simplifying data discovery and access. Data Explorer is a preview product so these features may appear differently in the final release.  Read more...

This article details an Excel 2010 function to return the cell address of min and max functions.  Read more...

In this tip we would look using an existing SSRS report to provide the data for PowerPivot in Excel.  Read more...

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly...  Read more...

The goal of this article to show how to get unique transactions per client per month from the data set containing multiple transactions per client per month.   Read more...

A brief introduction to getting started with QlikView.  Read more...

Bill Pearson, business intelligence architect and author, exposes the DAX COUNTROWS() and FILTER() functions, while generally exploring, comparing and contrasting the nature and operation of calculated columns and calculated measures, in the second Level of our Stairway to PowerPivot and DAX series.  Read more...

Instructions on how to set up the connection string and SSIS package to allow a 64 bit server to export to a 32 bit version of Excel  Read more...

Recently while working on a project to import data from an Excel worksheet using SSIS, I realized that sometimes the SSIS Package failed even though when there were no changes in the structure/schema of the Excel worksheet. I investigated it and I noticed that the SSIS Package succeeded for some set of files, but for others it failed. I found that the structure/schema of the worksheet from both these sets of Excel files were the same, the data was the only difference. How come just changing the data can make an SSIS Package fail? What actually causes this failure? What can we do to fix it?  Read more...

This article will demonstrate a method of creating an Excel-based CPU/scheduler performance dashboard for SQL Server 2005+.  Read more...

So you want to group your data for charting in Excel? Here's how, the easy way!  Read more...

Recently while working for a client that was running SQL Server 2008 R2 I was tasked with loading an Excel...  Read more...

A new feature of SSRS 2008 R2 is the ability to name sheets in exported Excel workbooks. This article explains how to utilize this feature.  Read more...

A CLR procedure utilizing the NPOI library to export the results of a passed stored procedure to an Excel spreadsheet.  Read more...

Today Steve Jones talks about a new use for cloud computing with Excel to analyze large sets of data.  Read more...

Excel files includes a very good functionality called Macro execution. And here we can use this functionality to write one macro that will generate INSERT statements.  Read more...

Using opendatasource to retrieve data from Excel files as if querying a table.  Read more...

This article demonstrates using SQL Server's data mining capabilities with the Excel.  Read more...

It is not only the data within the database that requires protection but any report generated using SQL Server needs to be protected before being mailed  Read more...