Tag: Microsoft

Categories
Business Intelligence

Business Intelligence != Reporting

Last week I was in a heated discussion about Business Intelligence, and it came up that “Business Intelligence is just writing reports, what is the difference from just writing some reports?”

I tend to disagree. Writing reports is, well, writing reports. You can write reports off of tons of data, and yeah, probably get some good info, but usually it ends up in disparate information.

I have blogged about this before, probably a few times, but this is usually what happens in organizations:

1) Some app or system is set up where data is created in some kind of data store, usually a database

2) People want to see the data in some type of report

3) Developers start cranking out reports right off the source system, stopping the bleeding for the current need for more reporting.

4) System keeps growing, more data is created/collected.

5) Reports off source system start slowing down, timing out, many different reports end up for the same thing and don’t match, end users complain.

6) Developers get clever, start moving and summarizing data on some kind of interval, all with custom code, custom design. They write some reports off the summary data to once again stop the bleeding. Some reports are still off the source data. Reports don’t match. End users complain

7) More data is collected, more users want reports, and want them with different views, grouped by this, sorted by that, add this column, remove this column, etc.

8) Developers are taking the heat. Their reports aren’t matching, they are running slow, etc. They can’t keep up with all the report requests. They decide to create a custom reporting engine on top of their summary data that allows end users to create their one reports using some quasi query language and drag and drop columns/grouping. This stops the bleeding for a while.

9) Finally someone in the organization realizes that the developers are reinventing the wheel. This is where Business Intelligence comes in. The source data is left untouched, the BI group creates and ETL to have some kind of data warehouse design and structure, using out of the box tools. (SQL Server Management Studio is and out of the box tool – you use that to write queries. SQL Server Integration/Analysis/Reporting services are out of the box tools, you use them to create ETL’s, Cubes, and Reports).

10) End users can now write reports using Excel off the OLAP Cube, or they use the Report Builder with Reporting Services to create reports off a UDM (Universal Data Model), or the BI devs create reports in Reporting Services, all off the same source data. One version of the truth, less custom reporting.

11) Developers are left to develop on the source system turning requirements into code/features, not focus on reports, and the BI group is focused on getting all the data turned into information.

Of course this is a perfect scenario described above. Nothing is ever as simple as that, but we can hope. Also I am biased towards the MSFT toolset, but there are other toolsets out there that would solve the same problem.

All I know is that I have seen everywhere I go pretty much the same thing happen from #1 to #8 above. In some cases you never see #9-#11 and you end up in a world of hurt.

In the end though, Business Intelligence just isn’t “writing reports”, far from it.


Categories
Business Intelligence SQLServerPedia Syndication

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?


Categories
Life

Big Change #3 – New Job

Recently, in May 2008, I took a position with Stratagem. Though the summer I worked for 2 places as a consultant, KHS in Waukesha, and The Dept. Of Regulation and Licensing (DRL) in Madison.

A little history. I was full time for W3i around 2 years, and then went independent for about a year, then as a consultant with Stratagem. All the different types (full, indie, and consultant) have the pros and cons, and it all depends on what you are doing, where you are, and things you are working on.

I have done .NET, Team Lead, Database Stuff, C++, Data Warehouse/BI (Business Intelligence), ERP Stuff, more .NET and everything in between. Over the past 2 years I have come to love the database stuff more and more, especially BI and Data Warehousing. It is funny, because pretty much 99% of people have no clue what “BI” is. I was just a High Tech Happy Hour at Pooleys and everyone I talked to, “What is BI?” – and this is a tech event!! Anyways, I really do like BI and wanted to move my career forward doing BI. Stratagem hired me to do BI and work on their BI stuff, but there just wasn’t work out there, and I really wasn’t going down the path I wanted to. Stratagem is a great place, great people, I just wasn’t enjoying what I have been working on. (Although I did work on a small part time project for a couple weeks at night doing some SSIS stuff, which was exactly what I wanted to be doing full time!)

Now everyone probably knows I have an iPhone and use it extensively. A couple months ago, I installed the “Career Builder” app from the app store to check it out. It uses your location based on GPS, which I thought was cool. Just for kix, I typed in “Data Warehouse” and there was a result near Madison! Sweet, but what about details? Yeah everything that hit my buttons. Microsoft, SQL Server 2005, Analysis Services, Integration Services, some .NET, etc. Awesome!

I sent in my resume, and figured I would either hear back, and get the job, or wouldn’t hear back at all, not knowing how long the position had been out there, etc. I waited and finally heard back! Sweet. A phone interview, in person interview, informal interview, and another in person interview, and I got the job.

But who is this job with you ask? Well I am proud to say that this upcoming Tuesday I will be the newest “BI Architect” at Trek Bicycle Corp. You know, “Trek”. “Trek Bikes”. The awesome bike company. The HUGE bike company. The type of bikes Lance Armstrong rides. The USA Teams ride. The bikes that have won a ton of Tour De France’s. Sweet, sweet bikes.

Trek is located in Waterloo, WI, which is about 15 miles from where I live now in Madison, and about 14 from where I am moving tomorrow in Sun Prairie, WI. It is about 4 miles from where our band practices, in Marshall, WI. It is out in countryside, on the edge of a small town. Weird how the world HQ for this company is located in such a small town.

In any event, it is a new adventure that I am very excited for. I am really looking forward to get back into BI stuff head first and work back in SQL Server!! (I have been working Oracle for the last 2 months – ugh!)

I will even start riding bikes I’m sure, and I’m betting I am the envy of all my bike nerd friends out in PDX!!!

So this is Big Change #3 out of 3. #1 was moving, #2 is the baby, and #3 is the job. Another whirlwind couple of weeks here, and then the next couple of months, but I am excited and don’t worry, you will see my blogging to continue here. Although I am twittering more (http://twitter.com/scaleovenstove) but I still want to blog a couple of times a week. And hopefully I dive into BI/Data Warehousing and have more cool stuff to blog about in that realm.

One thing, this is the first time since I graduated college where my title isn’t some kind of “Software Dev” title. When I was indie, I really didn’t have a title, even though I was doing BI, so that is different. I don’t see myself going back to being a full time dev, even though I can do .NET. I will still use programming and development as a tool with my BI work, and also just for myself or helping friends.

Go buy a Trek! Save Gas! 😉

Categories
Geeky/Programming Ramblings Random

Why isn't there a Web 2.0 Ajax Visual Studio?

Was thinking about this today. You can now write Word docs, Excel spreadsheets, Powerpoint presentations and the like all online (Google Docs, Zoho, etc, etc). You can record video straight to websites through your webcam, you can video conference directly through the web.

Visual Studio in the Cloud:

Why can’t you code directly into the web?

I would like to see an app that lets you create a new .NET project through a web interface, reference dll’s if you need to (upload them to your “space”) and then go about creating code, Intellisense through Ajax, you hit compile, it sends it off to the server, compiles, and gives you a result. You can then browse to your exe or your new website and view the results.

No need for a bulky IDE installed on your computer, no worries about dependencies, etc. You could code C#/VB.NET on linux and a mac with no need for mono (although you couldn’t run the exe’s – it would be the most beneficial for web apps)

You could target different versions of the framework, use new features if you wanted, all that. Heck why not have the same thing for your database. mySQL already has it with phpadmin and all the other tools, you can query and do whatever you need to through the web. Where are the offerings like this for MSFT products? Maybe I just don’t know about them.

There is CodeIDE – http://www.codeide.com/ but it is limited in languages and options. I want to see more of a full fledged Visual Studio IDE in the browser. Why? Because I want to be able to fire up a computer and just go to work, no installing, no waiting, no upgrading.

I can already see it now, Adobe Air IDE’s you can run on your desktop and sync up source code to the online IDE.

One feature built right in to this “online IDE” would be source control, revision history, etc.

I might not be possible right now, but I say give it a few years, and we will see a product like this come out, and I can’t wait.

Categories
Geeky/Programming Product Reviews

Browser Wars 08

Now that Google has released its browser, Chrome, that leaves us 4-5 big players in the browser wars.

1) Microsoft’s Internet Explorer (download)
2) Mozilla’s Firefox (download)
3) Apple’s Safari (download)
4) Opera’s Opera (Weird – their company name is the same as the browser name) (download)
5) Google’s Chrome (download)

Pretty much everyone has used IE, unless you are a main frame unix guy or something, you at least have probably used it to get Windows Updates. I think I started using it around IE3, then IE4, IE5, IE5.5, IE6, now IE7, and actually I am using IE8 Beta2 at work. IE works, but it has been plagued by security vulnerabilities, and stagnated from lack of innovation from the IE4 days till now, where they are finally picking up steam again. Although, you are kind of stuck to Windows if you want to use IE, one of the major factors I don’t use it as my main browser. IE has somewhere between 70% and 80% of the market share, so your site better work in IE. I do say death to IE6 though. MSFT should push IE7 as a mandatory update.

Firefox is multi-platform, a good thing. It also seems to have major releases more often, better auto update support, and of course, extensions, which really extend the browser to something way better than ever imagined. Firefox has security issues sometimes, but they are usually quick to fix, and they are also pushing the competition with every new release.

Safari, oh Safari. I did try to use this as my main browser when I picked up my MacBook Pro, but after about 2 weeks I had to switch to Firefox. It just lacks some key features that make me want to switch back to Firefox, little quirks. It does render fast and nice, and yeah, the iPhone version is much better than PocketIE – its not even a comparison. Safari works now on Windows and Mac, which is also a good thing, cross platform is always nice.

Opera – well, it has 1% or less of the market share, but it just won’t die. I only use it when I need to test a site that has to work on everything, other than that, not much. Seems that stuff renders different in Opera. They have made some strides in features, like mouse gesturing, and other things that other browsers have “stolen” if you will, but I just don’t see Opera being a big contender in the space. It is cross platform which is nice, but it just doesn’t have the steam the other browsers do.

And then the new player, Chrome. Some are saying it already has 1, 2 maybe eve 3% of the market share. I installed it and used it for about a week as my main browser. It uses WebKit, the same rendering engine as Safari, so sites that work in Safari for the most part work in Chrome. (Firefox uses Gecko by the way). Chrome is the fastest out of the bunch, at least from my experience. The new concept of tabs on top is different. The process model is different, where each tab is a process (IE8 Beta2 has this as well), and there are other “new” features in Chrome – most of which are in IE8, FF 3.1, or available as extensions on FF 3.1. It isn’t cross platform yet, but they say it will be in time, fair enough.

So from the list above, you have Firefox and Opera (which is not used by many) being cross platform. Which means, if you want to use Windows flavors 2000, XP, Vista, or Mac 10.4, or 10.5, or many flavors of Linux, Firefox is pretty much the way to go, to get the consistent experience from OS to OS.

Most companies and corporations are STUCK on Internet Explorer 6. This just makes me cringe. At least get to IE7, it has been out for two years, IE8 is coming out next month!!

I see chrome gaining market share, but Google is walking a fine line on privacy it seems, they have already backpeddled a few of their policies since they released Chrome.

Safari is good for Apple users, or someone who wants the “Apple Experience”. I suppose IE gives you the “Microsoft Experience” the best. Chrome will give you the “Google Experience” the best. Firefox just gives you the “Best Experience” 🙂

I would recommend every once in a while switching to a new browser for a week or two, just to keep up with the changes. I guess you should switch not just browsers, but everything if you can (OS, Media Players, etc, etc) – Try It!

Categories
Business Intelligence Geeky/Programming Ramblings

Excel 2003 vs Excel 2007

It is the year 2008, we are half way through. Excel 2003 is 5 years old. Stop using it please.

Why? Excel 2003 has the old “limits” – 65,000 rows, 256 columns, memory limits etc. Excel 2007 on the other hand, 1 million row limit, etc etc. That coupled with the way pivot tables work in Excel 2003 compared to 2007, and the SQL Server Analysis Services features with 2007, it is a no brainer to go to 2007.

Companies will say – “But we can’t move all our users to 2007, we can’t afford it” – well, think about just moving your power users. The users that have huge spreadsheet extracts, etc. It is worth it. They can save files in 2003 format if they need to share a smaller file or something, and the 2003 users can install a 2007 viewer.

Other options for huge spreadsheets and extracts are… Access – which your users need training on, or need to be able to adapt to, or another options is SSAS and Cubes, which you need executive buy in, and the infrastructure, and the training to get your users up to speed, and by that time you will want Excel 2007 to connect to the cubes, so…

just start using Excel 2007 – 2009 will be here soon!

Categories
Geeky/Programming Life

Dell Dies, And In Its Place..

A MacBook Pro. Yes, and it is awesome.

So the story, my Dell laptop was humming along fine, but then recently I heard some clicking, and was like WTF? Asked Emily “Did this fall on the ground or something”… “No… but I dropped a glass on it”… Oh man, and you know, one month after the warranty expires, as usual.

So this weekend I went to the Apple store and spec’d out a sweet MacBook Pro. 4 GB ram, High Res, everything. Picked up .Mac cheaper since I was getting a machine (soon to be MobileMe).

Right now I am running Vista in a virtual machine, giving it 2 GB, and it flys. This is probably the sweetest machine I have ever used 🙂 I am using VMWare Fusion instead of Parallels like by MacBook, figured Fransen said it was good, so I would try it. I like it.

I really like the light up keyboard. What else? Like they say, everything just works. Take for example my Verizon EVDO card. In XP or Vista, you have to install the connection manager, hope it works, hope all the drivers install, and then hope it launches. You run into admin issues, and just a ton of other stuff. In the MacBook? I plugged in the card, 2 seconds later, connected. No install, nothing. Just worked. God send.

So I think I am an Apple Guy now. iPhone, MacBook, MacBook Pro. Mighty Mouse (wired and bluetooth) and a bluetooth Keyboard. And on the 11th, I will get the 3G iPhone. When I think about it, all I need Windows for is Visual Studio 2005, 2008 and SQL Server stuff. Everything else I can do on the Mac, and with Virtualization, it is almost seamless. I like it.

What’s next? AirPort Extreme, AppleTV, Time Capsule…

I don’t think I will go back, unless something totally changes with the machines and the OS…

Categories
Geeky/Programming

Windows Update Crazy Error (on XP)? This Might Fix It

This weekend I decided to put XP on my laptop for a day to see how it ran. I hated it. Yeah it was fast. But it just seems… old..

Anyways, Vista is back on now (I still might go back to Win2k8). But.. while I had XP installed for 4 hours, I tried to do Windows Update and it kept failing and failing. I went and found the WindowsUpdate log file, and at the bottom,

AUClnt FATAL: Error: 0x80004002. wuauclt handler: failed to spawn COM server
Handler FATAL: 0x80004002: ERROR: Remote update handler container process created (PID: 2912), but exited before signaling event
Agent * WARNING: Exit code = 0x80004002

Well, after some digging, found you need to re-register the Windows Update dll’s (you can put these cmd’s in a batch file and run it)

regsvr32.exe c:windowssystem32wuweb.dll
regsvr32.exe c:windowssystem32wups2.dll
regsvr32.exe c:windowssystem32wups.dll
regsvr32.exe c:windowssystem32wucltui.dll
regsvr32.exe c:windowssystem32wuaueng1.dll
regsvr32.exe c:windowssystem32wuaueng.dll
regsvr32.exe c:windowssystem32wuapi.dll

I also restarted the Automatic Updates service. Tried it again, and it worked. Weird, but it works 🙂

Categories
Geeky/Programming Ramblings

Hacking Microsoft Pro Photo Tools – Using Reflector to use MapPoint Lat Long Lookup (for free!) in C#

The other day, Microsoft came out with “Microsoft Pro Photo Tools” which allows you to geocode your photos. It is a pretty cool app, but there are some things that I wonder, like why didn’t they just build this functionality into Windows Live Photo Gallery?

Anyway’s, with any new thing I download and play around with, I started digging into stuff. I looked in the install directory, C:Program FilesMicrosoft Pro Photo Tools and noticed that there are some Interop assemblies and other assemblies, etc. I fired up Reflector and started disassembling the assemblies and exe. Pretty cool stuff, you can see what they are doing. Using xaml forms, etc. The cool stuff is the Location based stuff.

Microsoft has MapPoint web services which you can use/sign up for, but they cost a pretty penny. I have used some of these web services in the past and they have a ton of functionality.

Like I said, digging through the disassembled stuff in Reflector, I saw a method “GetLatitudeLongitude()” which takes in country, state, city, address, zip and returns a lat long object. But, you need a “MapPointWrapper” object to use it.

I fired up Visual Studio 2008, and then referenced the assemblies in the Pro Photo Tools directory so I could use them in code. I created a test WinForms app, and started hacking away.

Looking at the MapPointWrapper class constructor in Reflector, I noticed that it needs a username, password, URL, and timeout, the first three I don’t have – but I bet I could find!!

Here you can see the constructor as it looks in Reflector. The thing I noticed right away is that they have the username and password embedded in the function, although its all “encoded”, then blend the strings together to create default credentials. Their blend method is using some bitwise operators, etc, if you are interested, you can just click on the Blend method and it browses to that (did I mention Reflector is cool??) – anyway’s, I still need a URL…

image

Reflector lets you click on a class and “analyze” it, which gives you what classed depend on it, which classes use it etc. Just going through the list for MapPointWrapper, I found one that showed how they call the constructor.

image

That’s the ticket! You can see they are passing in empty strings for user/pass (which then gets converted to the correct user/pass by the constructor) and then the URL is right there!!! nice! We can use this!!

Now, on to using this functionality in our own app!!

image

Now, this will give you the lat/long back from MapPoint! Sweet. Now we can start digging into everything else – what else do these assemblies expose?? Can I get routes? directions? Maps? etc, etc, etc. There is a plethora of things to dig into. It looks like they are just using Virtual Earth though to get maps, not MapPoint (from what I can tell anyways).

I know there are a ton of other ways to get this info, but this was basically a test to reverse engineer their assemblies and use the functionality. I don’t recommend or condone hacking/reverse engineering assemblies like this for profit, more for fun , in other words – don’t use this in a production app as Microsoft would probably find out and come hunt you down.

This post is also just an example of how .NET code can be disassembled easily and re-used, for good, or evil 🙂

There are some basic things that every developer should do with .NET desktop apps – use Dotfuscator (which just obfuscates your code, making it harder/not feasible to reverse engineer, and also encrypt any strings/values you don’t want anyone else using or reading. That being said, Reflector is a great way to see how other applications are coded, and learn how they work. Happy Coding Hacking!

Categories
Geeky/Programming

Auto Submit Form in ASP.NET With Javascript

Not sure if this is the best way to do this, but this is what I do. Say you want to auto submit a form on an aspx page. You can call document.formname.submit(); from the body onLoad event and it will submit, but ASP.net will automatically post it back to itself.

I tried added buttons with different postbackUrl’s, clicking the submit button in javascript, etc to no avail.

First, what I had to do was put on my ASP Classic hat. Look at your <form> and remove the runat=”server”

Then, you can say document.formname.submit(); and it will submit your form.

How do you pass data though?

Well, you have to created input fields, probably hidden like

<input type=”hidden” name=”blah” id=”blah” value=”<%= Request.QueryString[“myvalue”] %>” />

Then you can pass data from another page or whatever and auto submit your form.

It would be nice if you could say in your Page_Load() something like

 

btnSubmit.Click(); and it would automatically click it and submit your form, but that doesn’t seem to be available at all.

Like I said, there is probably a better way to do this, and it shouldn’t be this complicated, but a few minutes googling for answers left me up in the air. Funny how something that is so easy in ASP Classic turns out to be harder in ASP.NET. ASP.NET wants you to really post back to the same page by default. It hijacks the “action” attribute on the form no matter what with runat=”server on there.