Tag Archives: Excel

Exporting Data from SQL Server to Excel Directly

Had a request to export data out from SQL Server to Excel directly. There are a few ways you can do this. BCP is one, another is OPENROWSET. SSIS, etc.  Here is the OPENROWSET method, using Jet (I think this only works on 32bit servers as well)

First you need to enable a setting on SQL, so.. Start->Programs->Microsoft SQL Server 2005->Configuration Tools->SQL Server Surface Area Configuration, then click on the “Surface Area Configuration for Features” at the bottom.

image
Under database engine, make sure “Enable OPENROWSET and OPENDATASOURCE support” is checked under Ad Hoc Remote Queries option.

image

Then, you need to create an empty excel workbook, with the columns of the query you want to export out. Create one on C:Testing.xls with “Name” and “Rating” columns

Then run this query:

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:testing.xls;', 
'SELECT Name, Date FROM [Sheet1$]') 
SELECT 'Steve','1'
Union
SELECT 'Joel','2'
GO

And if everything worked, your xls should have 2 rows in it.

 

Now, if you want output to other spreadsheets, you could dynamically set the filename in the statement, or other trickery!

Advertisements

Office 2010: Excel 2010, What-If Analysis aka Microsoft Finally has Built in Cube/OLAP Writeback!

Digging into the blog post from earlier this summer I wanted to see what was new and exciting in Excel 2010.

Recently I have been working on an cube and we want to be able to budget right from the cube. There are also many other cubes/scenarios where the ability to writeback to the cube would be awesome. Some BI tools have had this for many years! Microsoft had something similar with a Excel 2002/2003 add-in, but it has been removed. Also, there are many 3rd party tools to allow this. You could also write your own macros or VBA/.NET code to do this as well, but what was always missing was the ability to do writeback directly from an Excel (OLAP) PivotTable. With Excel 2010, this functionally finally shows up.

First, you need a cube, and you need to enable writeback on a partition. This will automatically create a table in your database where you have your data warehouse. Lets say you have a table FactBudgets, and you enable writeback, SSAS will create a table WriteTable_Budgets. This is a trivial example I went through to test this functionality, but I just wanted to exhibit the feature of the writeback.

01_writeback

Turn on writeback, deploy and process your SSAS cube, and then open Excel 2010. Connect to your cube, and then in the PivotTable ribbon menu, on options, there is a button to turn on “What-If Analysis”. Turn it on. :)
02_whatif

Once you turn on the setting, you can then begin writing back values to your cube, right from Excel. If you click on a cell in your writeback value, you can just change it. See on the screen shot below, the value I changed, the little purple triangle, tells me it has a changed value from what is in the data source.

03_valuechanged
04_valuechangedmenu

You can see its telling me the value changed, I can Discard the change and other options. After you have your values set, you want to publish them, which you do back on the PivotTable ribbon menu:

05_publishchanges

As you can see, Microsoft has finally created a viable solution for writing back values into your OLAP cubes without the need for 3rd party software or coding. Finally!

One thing to note, if you try to write back to a value that is in a partition that doesn’t have writeback enabled, you will get an error.

06_error

Now, think of the possibilities with SSAS OLAP writeback and Excel, now that we can actually use it out of the box!

Excel 2003-2007 Assistant. NO EXCUSES to not upgrade!

Last week, the Business Analyst at work sent me a link, Office 2003/2007 Assistant

What the link will show you is the differences in commands between Excel 2003 and 2007, so users can learn how to do things with the Ribbon.

Excel 2007 really should be used when hitting SQL 2005+ OLAP Cubes, but companies are reluctant to upgrade because of the “jolt” of learning the Ribbon. Not anymore, with that assistant you can find out how to do anything you could in 2003 (not just with PivotTables – with anything)

What this means, is that there are NO MORE EXCUSES to not upgrade to 2007. Hey, with Office 2010 around the corner – March 2010, you better get ready for it, and there no time like now to upgrade from 2003!

Excel 2007, OLAP Cubes: Customizable, User Defined Named Sets in Excel 2007 using VBA

In a perfect world, your master data and master data management (MDM) is set up so everyone can see things how they want. Categories of Items, Regions, etc, etc are all defined in your dimension data, and you can create hierarchies, etc that make sense and everyone is happy. But, this is not a perfect world :)

Some users want to see “their” items, “their” regions, etc. And they ask and ask for you to add it to the cube, but you have to deny them. Why? Because if you add their named set, you have to add all of them that are requested, that don’t make sense to 99% of the other people using the cube.

You probably could even go about doing some crazy MDX or something in your cube to create the named sets per user, but then you still have to manage it all in your MDX script in your SSAS project.

I blogged a while ago about the OLAP Pivot Table Extensions on CodePlex , which, is an awesome toolkit, but it is geared around Calculated Measures. I downloaded the code and took a gander here and there and it would be pretty easily modified to work with Named Sets (change xlCalculatedMember to xlCalculatedSet , add a radio button on adding a new calc, etc) – but, its in VS2005, and I have VS2008 (2005 BIDS, not C#), and right now I don’t feel like mucking around with that, maybe someone with more time, and more ambition can do it :) – Or maybe when I get some free time I will take a look, but for now here is a solution.

I took the idea outlined at the bottom half of this blog http://blogs.msdn.com/excel/archive/2008/02/05/common-questions-around-excel-2007-OLAP-PivotTables.aspx  about named sets and creating them in macros..

Sub AddNamedSet()

Dim pvt As PivotTable
Dim strName As String
Dim strFormula As String
Dim cbf As CubeField

Set pvt = Sheet1.PivotTables("PivotTable1")
strName = "[My Mountain Bikes]"
strFormula = "[Product].[Product Categories].[Bikes].[Mountain Bikes].children"
pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedSet
Set cbf = pvt.CubeFields.AddSet(Name:="[My Mountain Bikes]", Caption:="Mountain Bikes")

End Sub

That macro is pretty sweet, you can add a named set to your workbook. But, its pretty static. You have to edit the macro every time you want to add more items, it doesn’t update or remove the sets you might have, etc.

What I whipped up quick was an updated macro that lets you build customizable named sets based on data in another worksheet in your Excel file (you can imaging it coming from other sources – another Excel file, a SQL table, etc, etc). I did this with a cube we use at work, but for this example, I changed it to pull off the AdventureWorks DW SSAS DB, AdventureWorks Cube, (SQL 2005 edition)

First off, here is the macro: 

Text File of Macro: Custom_NamedSets_Macro.txt</a

Excel 2007 Workbook with Macro: Custom_NamedSets.xlsm

Sub AddNamedSet()

    ' data sheet top row must be set name [Set Name] and then item numbers after
    Dim sourceData As Worksheet
    Set sourceData = Worksheets("Data")
    
    Dim strName As String
    ' get the name of the set, including brackets []
    strName = sourceData.Range("A1").Value
    
    ' used range is the whole column, rangeVals is the item numbers
    Dim usedRange As Range
    Dim rangeVals As Range
    Dim maxRow As Integer
    
    Set usedRange = sourceData.usedRange
    
    maxRow = usedRange.Rows.Count
    
    ' get the item numbers
    Set rangeVals = sourceData.Range("A2:A" & maxRow)
    
    Dim strFormula As String
    Dim i As Integer
    
    ' loop through and build forumla
    strFormula = "'{"
    
    For i = 1 To rangeVals.Count
       strFormula = strFormula & rangeVals(i, 1) & ","
    Next i
    
    ' remove last comma and add last curly and tick
    strFormula = Left(strFormula, Len(strFormula) - 1) & "}'"

    ' get pivot table object
    Dim pvt As PivotTable
    Set pvt = Sheet1.PivotTables(1)
    
    ' Add a calculated member titled "[MySet]"
    pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedSet
    
    ' Add a set to the CubeField object.
    Dim cbf As CubeField
    Set cbf = pvt.CubeFields.AddSet(Name:=strName, Caption:=Replace(Replace(strName, "[", ""), "]", ""))


End Sub

Sub DeleteNamedSets()

    Dim pvt As PivotTable
    Set pvt = Sheet1.PivotTables(1)
    
    Dim i As Integer
    
    For i = 1 To pvt.CalculatedMembers.Count
        pvt.CalculatedMembers.Item(i).Delete
    Next i
    pvt.RefreshTable
    
End Sub

Now, this was a 15-20 minute VBA hack. It could really use some cleanup, but works. What I did to make it work with Advendture Works was just put the whole “member” string in the data tab of the spreadsheet. In my case at work, I just was using item number and had some of the member string in the macro. But really you can see you could add a named set on each column, or something, you really could make this powerful. And since I added the delete, if they re-run the add, it will recreate the set, so they can modify the data and re-add. It should be expanded on and made a little more robust, but you get the idea. Now, think of how you could use this with your cubes, and how you could get your user’s thinking about ways to use custom named sets!

SQL Server Management Studio and Excel – Column Headings on Copy/Paste

Here is a tip about a setting that I think should be on by default, but it isn’t. How many times are you writing T-SQL in SQL Server Management Studio (SSMS) and you need to just copy the results out, paste to Excel and either do more analysis or send off to someone. What happens when you have a result set with 20 columns? 30? 50?

Well, the old way to get the column headings in Excel was to just type them in. Ouch. This gets old realllly fast. But there is a setting in SSMS that most people don’t even know exists, and then once they find out about it, they are like, dang! I wish I would have known about that.

Well, fire up SSMS, Tools->Options, Query Results, SQL Server, Results to Grid. Then check the “Include column headers when copying or saving the results” Hit OK, and the any new query window you have will automatically copy the column headings from your result set with the data!

 

image

Microsoft BI Client Tools: Creating PivotTables in Excel 2007 that work in Excel 2003

Recently I blogged about easing the transition to Excel 2007 from Excel 2003, by changing your PivotTable design and options.

This post is going to be about creating PivotTables that connect to OLAP cubes (and PivotTables in general) that will work in either client, or sometimes called “Compatibility Mode

If you create a PivotTable in Excel 2007 (a “Version 12” PivotTable) and save the xlsx file to an xls (Excel 2003 or “Version 10” PivotTable), you can still open the spreadsheet in Excel 2003, but you cannot refresh the data – it will still work if you open the .xls in 2007 though.

What that means is that the version 12 PivotTables will not downgrade to version 10 PivotTables and won’t be refreshable in Excel 2003. This presents a problem if you still have people you want to share the PivotTable with that are using Excel 2003, and you are using Excel 2007. What you need to do is create the PivotTables as version 10 PivotTables, but from Excel 2007, and this is how you do that.

The easiest way to create the version 10 PT in Excel 2007 is create it in compatibility mode. To do that, open a new Excel 2007 workbook and save it as Excel 97-2003. Open that file in Excel 2007 again and you will be in compatibility mode. Once you create a PivotTable and save it, you can then share it with anyone that just has Excel 2003 and they can open it and refresh it. Simple, huh?


Microsoft BI Client Tools: Easing the Transition from Excel 2003 to Excel 2007 – PivotTable Layout and Design

Being that we are now in the year 2009, you would think that most people are currently using or have been using the Office 2007 suite for a couple of years now. The truth is that there are many businesses “in the wild” that are still standardizing on the Office 2003 suite.

Why? Well there are a variety of reasons. Some places might cite cost to upgrade (as in dollars), where others might cite backwards compatibility with legacy applications. Some others might just say that end users “won’t understand” the new ribbon interface, and they might think that the pain and time of training and helpdesk support outweighs the benefits of using Office 2007.

Over the past year I have been in three different places and they all are standardized on Office 2003, and it puzzles me that it isn’t a harder push to upgrade. The benefits of Office 2007 are huge, once you get used to the new interface, and I could go into the benefits but that is probably another blog post, but Outlook 2007 a GTD (getting things done) booster.

As a Business Intelligence guy, it really works for me if every user is on the same client tool, same interface, some quirks and same training, etc. Excel 2007 adds many things when using cubes and pivot tables, and especially with SQL Server Analysis Services 2005, it is a no brainer to use Excel 2007 with SSAS 2005.

In trying to get users of cubes using 2007, there are a few things that I have encountered that can make the transition easier, and today I am going to talk about PivotTable layout and design.

Users of Excel 2003 are used to a pivot table that is laid out in a tabular form, and no subtotals, and maybe grand totals or not. When they use Excel 2007 by default, the are shell shocked by the default pivot table layout and get confused and maybe even sometimes “scared” of what they have gotten into with 2007.

Well, the thing is, it is really easy to get your pivot table to look like a 2003 pivot table in 2007. When you insert a pivot table into Excel, you see this kind of layout.

You can see under the “Pivot Table” tools there is an “Options” and a “Design” tab. Click on the “Design” tab before you set up any dimensions or measures or filters on you pivot table.

These settings on the design tab you can set how you want your Pivot Table to look. To make it “2003 style”, on Subtotals, pick “Do not show subtotals”, On Report Layout, choose “Show in tabular form”. If you don’t to see Grand Totals, then you can turn those off as well. And you can fiddle with the various design options as well.

One thing not on this tab is changing the setting for the +/- on the rows. On the Pivot Table options tab, under the Pivot Table name way on the right, there is an options button.

Here you can tweak other various settings, but you can uncheck “Display expand/collapse buttons” to remove the +/-. As you can see, you can also make the Pivot Table a “classic pivottable” if you really want.

Moving from Excel 2003 to Excel 2007, at least in the Pivot Table and OLAP cube browsing area, shouldn’t be a hard move, and you shouldn’t be scared of it, as you can see you can make your pivot tables look like 2003, or go wild and shift to the new 2007 style.