Category Archives: Business Intelligence

sqlserver

Selling Management on SQL 2012

2012 is going to be a big year in the SQL world. No, the world isn’t going to end. SQL 2012 should get released by Microsoft, hopefully in the first half (cross your fingers for the first quarter!) of the year. Great! But many out there are now on SQL 2005, or 2008, or 2008 R2, some even on SQL 2000 (SP4 – still get support?) but you want to get to SQL 2012. What can you do to make that transition easier? You need to sell the features and benefits, just like anything else.

Clustering

If you have any kind of clustering environment, or mirroring, or are even thinking about doing clustering, then SQL 2012 is going to be what you want to do. With AlwaysOn, it makes it dead simple to create and manage clusters. If you look back over the versions of SQL, and think clustering, you might shutter. With 2012, things become much easier and management has to see this benefit, as with anything, to make your systems more available with the new AlwaysOn

Master Data Services and Data Quality Services

Microsoft came out with their first round of Master Data Services (MDS) in SQL 2008 R2, but it was lackluster. The interface is clunky, weird, and hard to use. Most “end users” of MDS aren’t going to be that technical. You need something simple, like SharePoint, or Excel. MDS is neither (even though its a weird version of SharePoint). With 2012, MDS is vastly improved and actually something viable where an Enterprise could use it for a Master Data Management (MDM) solution. Couple that with Data Quality Services (DQS) and you get tons of bang for your buck. with MDS and the excel add on, this will be just what the doctor ordered for MDM groups in businesses.

Business Intelligence

Near and dear to my heart of course, is Business Intelligence. What a huge release for BI folks in 2012. First off, a whole new analysis services type, Tabular. Columnar Vertipaq type cubes. Reverse engineer PowerPivots right into SSAS Tabular and then tweak to release out to the Enterprise.

Then the enhanced SSIS stuff, better IDE, better management of packages, and more. Of course the integration with the Visual Studio 2010 IDE is a welcome feature, especially for those of us that also need to work on C# and .NET 4.0 stuff!

But don’t forget the potential biggest thing yet out of the BI tools for 2012 – Power View (yes the space is intentional, not sure why.. but now we have PowerPoint, PowerPivot and Power View). Naming aside, Power View could be a HUGE analytics tool to get more BI out to the people in an Enterprise. First off, they plan on making it work on iOS! Power View works on tabular cubes, so you see the tie in there. The one big thing with Power View, is it just works inside of SharePoint. No stand alone editor. You better have SharePoint 2010 and a pretty good SharePoint admin along side your BI team to get all this stuff working. Some of the enhanced end user alerting in SSRS integrated mode looks nice as well. But once again, you need SharePoint! DON’T for get the SharePoint!

There is much more in SQL 2012 that will make DBA’s lives easier, and BI pros development streamlined. Too much to outline in just one post. But if you are trying to sell SQL 2012 upgrade to management, the “big three” things I outlined above are a good starting point. One thing to be aware of though is that the licensing model has changed in SQL 2012 to core based, so you would want to read up on that.

I’m excited for SQL 2012 bits to hit and I hope you are too!



pass

#sqlpass 2011 Summit Thoughts

Want to get a quick post out before I forget everything cool I have been thinking about the 2011 SQL Pass Summit in Seattle WA.

The conference started out great with a first timer reception (this was my second year), and I knew a few first timers so that would great.

The first day keynote was good, and then the sessions I hit were on a full range of things, new semantic search stuff with file table, and more.

I missed a couple of sessions on day 1 because I was in a Microsoft led focus group on “BI in the cloud” some very cool ideas thrown out and excited to see what is coming up in the future.

More good keynote on day two, a deeper dive into the stuff in SQL Server 2012 (formerly Denali). Went to some good sessions on Data Quality Services (DQS) and Vertipaq vs OLAP, and Power View (remember the space!) SharePoint and Power View integration, etc. Some very cool stuff coming soon with the 2012 release.

Tabular cubes vs Multidimensional cubes. OLAP vs Vertipaq, etc. Big debates and questions here, when to put things in Tabular vs MDX, etc. DAX solves some hard problems we run into with MDX.

Another thing I went to a couple sessions on was StreamInsight. This is some very cool technology for complex event processing, using .NET and LINQ. The StreamInsight guys are crazy smart. Also integrating StreamInsight with Azure, and doing some crazy processing and analysis of tons of data in near real time. I see this technology making a difference somewhere down the road.

Power View was another big winner from the summit. This integrates with SharePoint 2010 as a Silverlight (SL still isn’t dead?) report builder and viewer. Not even sure they are called “reports” anymore, but “views”. You create views and you can interact/edit them in real time. Export to PowerPoint too. These views work off “tabular” vertipaq BISM (BI semantic model) cubes. This technology is going to change how we deliver information to the business.

Being able to take a PowerPivot (no space!) and reverse engineer it into SQL Server Data Tools (formerly BIDS – in VS2010 shell) to create a BISM tabular cube and publish to SSAS Tabular instance looks great. Solving problems like crazy snapshotting and many to many and time analysis using DAX looks very useful.

Of course, GameWorks appreciation night was fun, and I found a Mcmenamins (Six Arms) up the road from the convention center, so I got get a taste of Ruby again. Good conversation and times at the Taphouse and around the Sheraton and Convention center was good too. Seattle is a nice city and the weather was pretty good I thought. Another great summit.



ReportBuilder Icon

SSRS Report – No Page Breaks For You!

One thing I usually run into when creating SQL Server Reporting Services (SSRS) Reports is this: You have a smallish size dataset back, maybe somewhere between 50 and 150 rows, but if it hits that row limit on the page break, you get 3 records on the next page. Annoying. What I usually do for reports like this is just make them all one big page. How? Pretty simple actually. Set the “Interactive Height” to 0 on the report.

Open up Report Builder (or BIDS) and get to your “Report” Properties, and then under “Interactive Size” set the height property to zero. Thats it.

Once you make this minor change, you report will just list your row with no page breaks. Pretty useful for that report with 3 more rows than a page!



Agile: Creating an SSRS Burndown Chart Part 3

In the previous 2 parts (see Part 1 and Part 2) of this series I showed you how to get your data ready, and how to get your report started and your Datasets and parameters where you need them. In this part, we will get the graph functional, and in the next part, we will make it pretty.

Start by adding title to your report “Agile Burndown”, then add a Line Chart to your report. Make it somewhat big, delete the Chart Title and Axis Titles,  and remove the “Details” from the Category Groups. You should have something that looks like this:

 

image_thumb15

Now to get the data on and finish it off!

Drag your values over to your Chart Data Values area like this:

image_thumb[17]

One thing we need to tweak, and this is on the PointsLeft Value. Right click on the PointsLeft series and go to “Series Properties”. To the right of the Value field, click the Fx button (for Expression Functions).

We need to change this series to not write out anything to the graph if there are no points greater than today. Why? If you don’t do this, your graph line for PointsLeft will drop off to zero for dates in your sprint after the current day, and we don’t want this. This is what the expression should be:

 

=IIF(Sum(Fields!PointsLeft.Value)=0 And Fields!Date.Value > DateTime.Now,Nothing,Sum(Fields!PointsLeft.Value))

 

Pretty cool, your graph should actually work now and function as a working burndown chart. But of course we need to pretty it up! Look for the next and final post soon.



SQL 2008 R2 IntelliSense May Stop Working After Updating to VS2010 SP1

Ran into this tonight. The other day I updated to Visual Studio 2010 SP1, and my IntelliSense in SQL Server Management Studio (SSMS) 2008 R2 stopped working.

I updated by SQL 2008 R2 install to CU6 and the IntelliSense started working again.



SharePoint 2007 2nd Stage Recycle Bin and Content DB Size

Recently, have been running into space issues from a content database perspective in SharePoint (MOSS) 2007. The DB server is still running SQL 2000 on a semi-old box, but since there are physical hard drives, hard to extend without a lot of surgery. Anyways, did some digging and found some things that may be useful for others..

1. You need to be Site Collection Admin and “God” on the servers.

I thought I was, but was mistaken. Was set up a while ago by consultant and I wasn’t involved, and added later. I had some pretty good rights, but not what I needed. I had Central Admin “admin” and other but not on the root site. Also some of the domain users that have rights I don’t have passwords for anywhere as an oversight, so kind of at a loss. I had to get that first before moving forward. I found a solution on this blog. I ran the following from the command line on the central admin server for my site and domain and got the access I needed to run what I needed to run.

stsadm -o siteowner -url http://sharepoint.test.com/sites/[sitename] -ownerlogin [DOMAIN\netID]

 

2. I ran Quest’s free server reporting tool.

This is pretty cool, you can go here: http://sar.ondemand.quest.com and install a little WCF service and then run the reports right from your browser, you just have to have the admin rights to the SharePoint site collection to do it. I ran this and found only around 5 GB of usage, but my database was at 222 GB with 278 MB free space not utilized.

image

image

After digging around, I found the issue. First, the recycle bin in SharePoint was full, but also, the “Second Stage Recycle Bin” was ultra full. and Ultra being 217 GB full. What is the second stage recycle bin? Well, users can delete from the site, and it basically changes a db flag to hide the file, then, after 30 days (by default) it moves it to the 2nd stage recycle bin. There it sits and sits and sits. You need to clean this one up, but if you don’t you end up in a situation where I am at.

You can’t turn off the 2nd Stage Recycle Bin because there is too much stuff in it!

3. Delete Items from the 2nd Stage Recycle Bin using PowerShell.

I found this cool PowerShell script here and modified it. First I changed the rowlimit to 10k, and I added a date variable to output the date so I could see that progress was being made. I also am checking my DB size as I run it and watching the free space go up, up up!

#################################################
#
# flushrecyclebin.ps1
#
# Invoke this from a Powershell prompt by calling "./flushrecyclebin.ps1 http://webapp/siteurl".
# This script will only delete items in the second stage (site collection) recycle bin, so you
# will need to manually flush items you wish to delete from the first stage into the second
# stage.
#
#################################################
 
# we accept this parameter on the command line. If you have several sites' recycle bins to
# flush, you could easily turn this into a foreach loop
param($param_site);
 
# set this to be an acceptable number of records to delete in one batch. If your recycle bin 
# has more items in it than this, you will need to rerun it
$rowlimit = 10000;
 
#################################################
#
# Don't change below here
#
#################################################
 
[void] [System.Reflection.Assembly]::Load(”Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c”)
 
$site = new-object Microsoft.SharePoint.SPSite("$param_site");
 
$query = new-object Microsoft.SharePoint.SPRecycleBinQuery;
$query.ItemState = "SecondStageRecycleBin";
$query.RowLimit = $rowlimit;
 
$itemcoll = $site.GetRecycleBinItems($query);
foreach ($item in $itemcoll) {
    $a = Get-Date
    $id = $item.ID;
    write-host -nonewline $a " ... Deleting .. " $item.Title " ... ";
    $itemcoll.Delete($id);
    write-host "Done";
}

 

So where does this leave you? Well, first, on a new SharePoint 2007 setup you want to setup the Recycle Bin Settings so you don’t get caught like this. If you go to Central Administration > Application Management > Web Application General Settings and at the bottom you will see the Recycle Bin settings:

image

Like I said, with a full 2nd Stage Recycle Bin, you are stuck and can’t really do much here. But once clean, you probably want to reduce the number of days to like 7 and also possibly turn off the 2nd stage Recycle bin or reduce the size.

After cleanup like I did, you will want your DBA to probably shrink the file, this is one of the times that shrinking makes sense.

Happy SharePoint’ing!



PowerPivot, Excel Services, SharePoint 2010 Farm, and You

The last few days I have spent an exorbitant amount of time (5-6 hours?) getting PowerPivot and Excel Services working on a SharePoint 2010 Farm. I just want to get out there some of the things I had to do to get it all working, and why (at least what I think is why).

First off, most dev setups are using one box for SharePoint, which, in my eyes, masks most every issue you will run into.

Most prod environments are multiple boxes, Web Front Ends, App Servers, etc. This leads to the most pain when setting up these services (PowerPivot, Excel Services) as there are hundreds of different configurations and setups. You need to get it juuuust right.

Anyways, I will explain as best I can.

First, assuming you have PowerPivot running on an app server and Excel Services running to. You upload your PowerPivot workbook to a PowerPivot gallery and you go home happy. But wait, does the data refresh correctly? Can you even open it?

1. If your site is running https, you have to tweak the Excel Services settings.

By default, only http:// shows up here (I think?) so you need to add https:// or you can’t even really get into your PowerPivot.

image

2. You probably are going to have to up the upload size limit

Some PowerPivot workbooks are big, like 80-100 MB big. Default in SharePoint 2010 for Excel Services is something like 10 MB, and SharePoint default is 50 MB, you need to change for both settings.

3. If you don’t have Kerberos set up, it is tougher.

PowerPivot refreshes data from some system somewhere to its own “cube”, then Excel Services refreshes data from that cube into Excel Web. You have to setup a way for the data to get refreshed into PowerPivot’s cube. No Kerberos? Then you need to use the Secure Store Service and set up a credential, and set that up as the unattended service account for PowerPivot refresh. Then, at least you can get data from somewhere else into PowerPivot’s “cube”

Second step is getting Excel Services to refresh from that cube into Excel web. What I had to end up doing was creating another credential in the Secure Store service for Excel Services Refresh (set up as farm account for now, it has the stroke it needs). And then set that up in Excel Services settings as well for Excel Services unattended refresh account. But also! – you need to change your workbooks before you upload or whatever.

In your workbook, go to your connection, properties, and get to the authentication, and change to “none” instead of “windows authentication”, then your data will refresh from the PowerPivot cube to Excel in SharePoint.

4. Same thing goes for the PowerPivot Management Dashboards

The management dashboards are set to “windows authentication” so they wont work either, you need to change to “none”, in the Management Dashboard site, goto all site content, PowerPivot Management, <some guid> folder, and then 1033 (US English), edit the two workbooks to use “none” and save, and your management dashboard will actually work!

I’m sure there is a ton more I go delve into here, but this is the high level. As Rob (@powerpivotpro) would say – “make sure to click on the slicers!”



MADPASS (Madison Area SQL Server User Group) Kickoff Meeting Tonight!

We are kicking off the MADPASS SQL Server Users group tonight in Madison. I am in the initial “Director of Membership”, keeping track of minutes and members, etc. If you are available tonight, come check it out. Overview of BI from Dave DuVarney. Pizza and Beer provided, should be good info and networking event as well.

Chapter Kickoff & Microsoft BI Overview

Many of today’s organizations rely on SQL Server and SharePoint to run their businesses.  Included with these products is a tremendous ability to also derive valuable insight from an organizations data.  In this session, Dave will walk through where the Microsoft BI solutions have come from, where they are today and where Microsoft is headed in the future.  After leaving this session, you should have a better understanding of what Microsoft offers and how best to apply it in your environment.
 

Date:
Wednesday, February 23rd, 2011  5:30 PM
 
Speaker:
Dave DuVarney is an independent BI consultant and trainer. He has broad technical knowledge and  has been involved in multiple software development projects ranging from contract management systems to human rights auditing.  He is proficient in numerous development languages as well as Microsoft Business Intelligence technologies.  Most recently he has been consulting and developing with SQL Server 2008 Analysis Services, Reporting Services and Integration Services. Dave is the co-author of Professional SQL Server Reporting Services 2000, Professional SQL Server Reporting Services 2005 and SQL Server 2008 Analysis Services Step by Step.
 
Location:
2310 Crossroads Drive
Madison, WI 53718

Park behind the building and walk in main entrance.
Our room will be straight ahead on the first floor.
 

Please register for the event on EventBrite here: http://madpass.eventbrite.com/?ref=ebtn

Also, follow @MADPASS on twitter. http://twitter.com/madpass  and check out http://madpass.sqlpass.org/ for more information.



#sqlpass Summit General Thoughts

Was at #sqlpass last week. Great conference/trainings, and people. Hopefully I will go next year (I am planning on it!) General thoughts/overview..

1. Should have done pre/post cons
2. Drink more water (funny thing is, no water during refreshment breaks? just juice/coffee/soda)
3. Leave room in your luggage for schwag
4. Bring more business cards.
5. Introduce yourself to more people
6. Realize that the level #’s on the courses aren’t always indicative of the content.
7. Go to sessions with ambiguous titles, they are usually the new stuff/undercover stuff
8. The Daily Grill (restaurant attached to the Sheraton) is the most expensive place in the world
9. Plan flights with more time in between if connecting. We missed our connecting flight because of delays.
10. Put the thing on your door from Sheraton to not upkeep your room, you get 5 $ coupon a day
11. Wifi sucks, use your 3G or EVFO/Mifi or whatever
12. They keynotes each day can be hit or miss, but still make sure to go to all of them.
13. Leave sessions 2 min early if you want to get a good seat at a highly anticipated next session
14. Take the back stairs from level 6 to level 4 instead of the escalator for a shortcut
15. “game” gameworks – my team knows what I mean :)
16. Bush Garden is small. But you can still rock #sqlkaraoke . Just don’t sit on the couch in the bathroom.
17. You can walk pretty much anywhere (Pike St, Space Needle, etc)
18. Go talk to vendors, but don’t let them suck you in. Stick mainly to the PASS area and Microsoft area (at least that is what I think)
19. Try to take advantage of the games and contests held throughout (Foursquare, Vendor check ins, Wheel of SQL) but don’t let it consume you.
20. If you go into a session that you find boring or not relevant, don’t feel afraid to leave and go to another – the sooner the better.
21. Order the DVD’s
22. Take advantage of BOF luncheon.
23. Follow the twitter stream .. #sqlpass
24. Tweet your encounters and info
25. Blog about it after :)

So much more to talk about, I will try to decompress it all and throw up a few posts, but yeah, well worth it, so much information and people and just thinking outside the box, and it just gets you to think and become motivated. Great time!




SSRS Report Creation Checklist

You can whip out reports in SSRS (SQL Server Reporting Services) very quickly with the report builders (2.0 and 3.0 are money).

But what should you remember to do each time, or information to get?

  1. Where does the data come from (GL, Sales, etc) – we could use a cube or datawarehouse, or staging, or Other system, etc.
  2. Report Name (on Report Manager)
  3. Short description of the purpose – to appear under report name on report manager
  4. Should it go into an existing folder, or a new folder, or the user’s folder?
  5. Who should have access?
  6. How should it be consumed? Email (and what, excel, pdf, web archive, etc), To a file share, User Ran?
  7. What parameters should be available? a. What are the defaults?
  8. Can we get a rough mockup of how it should look? a. Can we get an existing report (if avail, crystal, or excel, etc)
  9. Does it need to print on a 8×11 page?
  10. How often are you going to run it? (Hourly,Daily, Weekly, Monthly, ad-hoc)
  11. Is it going to be informational, or used to export data and manipulate? (if export – is there something else we can do?)
  12. If tabular data, does it need to be sortable?
  13. Do snapshots need to be taken? a. How many do we save? b. How often to take them? c. Do you need ability to delete them?

you get the idea, the list could go on and on. So creating that report is simple, but actually getting and doing all the things necessary to get it done “correctly” is more time consuming.

What other things can you think of for the list?