Category: Business Intelligence

Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

#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!


Categories
Business Intelligence

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?

Categories
Business Intelligence

App Store Pivot Viewer – ZoomAppy

Good ideas are hard to stop from happening. After dorking around with Pivot Viewer, I was thinking of things I could “pivot”, and the iTunes App Store was one of them. No real API though, there are some out there that have created APIs, or you could scrape the web, etc but nothing solid.

Looks like someone took that idea and ran with it, called ZoomAppy (http://zoomappy.com/). RIght now it looks like it is just the app store, but they have more in store.

I agree that Pivot Viewer is “Business Intelligence”, but it is a different way of thinking. Where in traditional BI, you think of looking at metric/measures like sales/inventory, etc. With PivotViewer, you are looking at “objects” and filtering them based on properties. Teams, Bikes, Apps, Cars, People. Also PivotViewer doesn’t give you any type of aggregations besides counts, so it is limited in that regard.

What else would be a good candidate for Pivot Viewer?


Categories
Business Intelligence

Blaming the Waitress

Most people have probably done this at least once in their lives.. You go to a restaurant and order a meal. The waitress is nice, comes over, gets your drinks, takes your order, checks up on you, refills, what not, brings out the meal, and woah, something is wrong, it is cold, bad, just something. But what do you end up doing? Getting mad and blaming the waitress. You might not give her any tip at the end of the meal, because you are mad about the mishap with the food.

But then you start to think about it, and you look back and realize that hey, it wasn’t the waitress who caused the issue, it was probably the cooks! But you are already driving home and don’t really do anything about it.

People like to cast blame, even that might not be the right phrase, but people like to *call out* the group on the front lines (note: in most cases. For instance in the military campaigns, when one side loses, people blame the generals and commanders, etc, not the privates on the front lines, and rightfully so).

In Business Intelligence, you are usually the “front line” to the business when it comes to reporting information and data. One of the roles of BI is to “deliver information to the business”. But then what happens when something is wrong with that information? People immediately blame the Business Intelligence group. It just seems to be human nature, just like blaming the waitress.

Now, don’t get me wrong, sometimes the problem might reside with the waitress, and then you would call her out on things, but you need to realize, your tip just doesn’t go to her. It gets split between her, the cook, the bus boys, the greeters, etc.

I hate playing any part of the “blame game”, but sometimes people should think through what they are actually “blaming” and make sure it just isn’t calling out the front lines because that is what is easiest.

I’m not sure that Business Intelligence will ever fully get around taking most of the up front hits when something goes wrong with data, or with a reporting server, or with anything that might be outside of its control, but what Business Intelligence groups should strive for is to be accountable for things within its control, such as finding problems before the business does, handling data integrity issues with ease, making ETL’s more fault tolerant, getting and handling server alerts for jobs and processes that it is in charge of, etc, etc.

Just try to remember the next time your food is cold at the restaurant, try not to blame the waitress 🙂


Categories
Business Intelligence Geeky/Programming

OData is the New Hotness

Not sure why I didn’t look into this sooner. OData. Open Data Protocol.

Not many providers, but this has huge potential. First off, PowerPivot can consume OData feeds! See the “From Data Feeds” button 🙂

I used it to download Netflix’s whole library. I tried to download StackOverflow’s data (http://odata.stackexchange.com/) but had no luck. Either got errors or it was taking forEVER!

But, you can see how easy it is to create an OData API for your data? Scott Hanselman did it for StackOverlfow in 30 minutes. This opens up HUGE oppourtunities for apps and ISV’s to expose their data out for end user and other developer consumption.

You can see all the “producers” here: http://www.odata.org/producers .. I am sure there are more, just not on the list.

What a cool easy way to expose your data as an “API” out there.. Excited to see how it is used (Pivot Viewer, Power Pivot, etc) and what people do with it.

Categories
Business Intelligence Geeky/Programming Reviews

Microsoft Live Labs Pivot Viewer – Rich Internet Application

So, I previously blogged about using PivotViewer in your Web Applications, but you can also just consume Pivot collections using the “Pivot” tool from Microsoft Live Labs

You can download it here

What does this tool offer? Well first it has a library/homepage of collections you can browse

You can do some slicing and dicing on a collection of Presidents, or athletes, or Sports Illustrated covers. This tool and technology really fasinates me. It is “Business Intelligence” but in a different way – it is based on “objects” (images) instead of “metrics”. I like it.

What are some cool things I think this could be used for? Company Directory? Online Catalog? Beer selection at Eddie’s? the list goes on and on..

Additionally there is now an add in for SQL Server Reporting Services and SharePoint 2010 you can download here

Once I have an environment in which I can test that set up, I will blog about it.

Categories
Business Intelligence Geeky/Programming

Microsoft Silverlight PivotViewer: Getting Started and Business Case

I have been reading about Microsoft’s PivotViewer lately, and decided to try to get it going for myself. What is PivotViewer? Think of it as visual data slicing through a web page.

What you do is take some data, and then tie records to images, and then publish out your “collection” and you can consume it via a webpage using the Silverlight PivotViewer control. One awesome example of this is here http://netflixpivot.cloudapp.net/. But what I have been really trying to wrap my head around is how to use the as a “business” tool. Because, it is easily technically doable, but you have to have a *reason* to do it.

Working with widgets and customers and locations – what do you do? There are two things I could think of quickly. One – peruse your “master data” very fast and visually. The other is looking at some kind of metrics for your widgets, or logos of your customers you might sell too, or ? ..Well, you could..

  • Master Data/Catalog

    • Show pictures of your widgets, and create filters (they call them facets) for things like size, color, weight, model, etc. You have “one” of each and you just want to see what you offer. Almost could be a pretty sweet online catalog browser

  • Sales/Metrics

    • Do the same as a master data catalog but allow filtering by some kind of metric. Shipped items over a given time or something.

  • Something Else I Haven’t Thought Of?

Anyways, the first thing you should do before anything is get some kind of data feed. Run a query, get some data from somewhere. Start small to test. 500-1000 records.

Then the fun begins. Starting from absolute scratch..

Ok, yeah, tons of setup. Biggest thing is in IIS you need to set some MIME types: .cxml, .dzi and .dzc need to be “text/xml”

Once you have all that setup, you can do 2 things.. create your collection, and create your app. Create a blank silverlight project first:

Once you have that, there isn’t a ton you have to do to get things going with PivotViewer.

  1. MainPage.xaml

    2. Add in your MainPage.xaml, a namespace line for Pivot, and add the control

    Your end MainPage.xaml should look like this:
  2. Reference Assemblies

    3. For good measure, just reference them all, located here: C:Program FilesMicrosoft SDKsSilverlightv4.0PivotViewerJun10Bin

  3. Load Collection:
    4. pvWidgets.LoadCollection("http://localhost/SilverlightApplication1.Web/MyWidgets.cxml", null);

Note, you have to make your web part of your project IIS based instead of the build in web browser. Why? Because the .cxml HAS to be hosted on a web server, it just works that way.

Now, you need to create your collection.. you can use a cmd line tool they offer on the PivotViewer site, they also have a c# library for automating things, but it is best to first just do it manually. So I used the tool they have as an add on for excel. It adds a nice little “Pivot Collections” tab

You can use this to put some data in, you probably want to add more columns than what they give you by default. For my test I just used the same image for all records to get started. I have a feeling that the biggest barrier to entry to corporate BI teams and developers is going to be the imagery. You usually don’t have someone on your BI Team that knows how to use photoshop well and do all the high res imagery, so you are handcuffed there.

For testing sake, I Published my collection to the root of my website, with the name “MyWidgets”.

I loaded up my webpage, and I can slice and dice by all my columns I had in my collection, visually.. pretty dang awesome. (Note, I just made some fake data based on attributes I am used to seeing and with an image of a bike to see what it would look like – the goal was figuring out how would this work in conjunction with current BI offerings (cubes/Pivot Tables, SSRS, PowerPivot, etc))

Now, if you think where you could take this. Each “image” is clickable and brings up the image right in front of you. You could have all the specs of that widget there, and a link to “buy”, or deeper analytics for that widget.

Some other things I found out.. using the Excel tool for Pivot Collections is dog slow. Especially with a ton of records. It has to process the images for the “deep zoom” technology and it just takes a while. Like, hours.

There are tons of possibilities here with PivotViewer, both for an external website and also internal corporate business intelligence. It will give people another way to delve into the data and turn it into information.

Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

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!

Categories
Business Intelligence SQLServerPedia Syndication

Where does PowerPivot Fit?

Now that SQL Server 2008 R2 is out, and Excel 2010 is out. You can get PowerPivot (http://powerpivot.com/) and create your own in memory cubes!

…. Or something like that.

I still haven’t figure out where PowerPivot fits in a business scenario. Why?

Well first you have what are now being called the “old school” BI users, that use Excel to connect to an SSAS cube and create fancy pivots and reports, maybe convert to formulas and create some nice reports/dashboards.. analytics.

Then you have people who only consume canned/standardized reports, through SSRS mostly, or maybe Excel Web Services… but they don’t create. Just consume.

You might even have power users, who take Report Builder and create those SSRS reports for other users. Awesome.

But then, you have this tool, PowerPivot. What can you do? Hit databases (mostly… cubes and other sources as well), bring back data, relate it, and create pivot reports/graphs off of it.

But you better be pretty dang advanced as a business user to use PowerPivot. I could count on one hand the users (that I have dealt with over the last 10 years) I would feel comfortable giving it to and not ending up with more of a headache.

What do I think is still missing from the Microsoft BI toolset? Looking at Business Objects, the Web Intelligence. Universes. You create a Universe off of a datasource and expose it out to the user, they can create reports/ad-hoc whatever off of it.

Kind of like the ever elusive “Report Model” in the Microsoft stack that no one ever uses, ever will use, or has no reason to use. But in BO, they make it useful.

I don’t see PowerPivot taking the place of a Report Model/Universe, so where does it fit? IT Analysts making “pre” cubes before you actually make cubes for your users that just want to hit it with Excel and not care about anything else?

Or people who just want to create their own cubes in silos. Tell me how that lends itself to “one version of the truth”?

Either way, we will see how it evolves and hopefully find some good use for it. 🙂

Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SQL Schema Source Control (CodePlex)

Source Control. In my eyes, one of the best inventions of development.

Software Developers have used it for years, and it allows them to easily develop in a team environment, and be less scared they will lose a change or not be able to see things they did historically.

I have blogged a few times about source control before..

SQL Server Schema Automatic Revision History using DDL Triggers and SVN
MSFT BI In a Team Environment
Visual Source Safe Sucks/
Source Control at Home with Subversion

But this was mostly for code. Developers. The SQL Community has kind of been shafted with source control. Yea you can tie in VSS to Management studio, and others. I have never found one that works, and just wanted something to work in the background.

I originally started doing this with DDL triggers as my <a href="“>post in November kind of outlined. It worked, but wasn’t reliable enough for what I wanted and was too much setup.

So I did what developers usually do, I wrote an app. SQL Schema Source Control http://sqlschemasourcectrl.codeplex.com/

At first it worked with one database, one server, everything was hardcoded. And then it progressed, and now it is all configurable for multi server/multi database, etc. I decided to put it up on CodePlex because I think it can be improved and made to work with other source control providers, like TFS.

The code itself isn’t anything crazy, some file operations and SMO operations to get the DDL and then some functions to add/update/delete and commit to source control.

For info on how to get it working, check out the documentation page on CodePlex, I can also answer any questions here, or on CodePlex.

I have been using the app for a few months now and it has saved headaches, accidental deletes, wanting to see changes over time by developers, etc.

The feature I like the most is that it logs the SQL Agent job changes, so if someone changes a job, you can see the history…

Now, there are competitiros out there. Redgate (http://www.red-gate.com/products/solutions_for_sql/database_version_control.htm) and others. But I wanted something free and open, so that is why I am putting this out there. I would be perfectly fine not putting anything out and just using it, but I think (and hope) others could benefit from using this app

So if you are looking for semi-easy way to get revision control on your SQL Schemas and SQL Agent jobs, check out the app. There is minimal setup, but once you have it working, it just runs.

Note at this time it works with SVN and SQL 2008. Also the solutions is VS2010. I originally had it working with 2005, but no need on my end anymore for that, someone could easily make a version for SQL 2005. VS2008 solution could be created pretty easily as well. Have fun!