Swedish SQL Server User Group

Just wanted to let you know that the Swedish SQL Server User Group (SQLUG.se) is making a comeback after hibernating for some time. Can't say I was too active the last time around but I'm planning to change that now. They're kicking it off with a meeting featuring Kalen Delaney as a speaker so it should be prove interesting enough.

Office 2010 Beta

Microsoft Office 2010 is now out as a public beta. Available at: http://www.microsoft.com/office/2010/en/download-office-professional-plus/default.aspx

Enjoy

SSIS, Oracle and x64

I hope I can spare someone the time I just spent to get this working...

The scenario is as follows:
We have SSIS 2008 installed on a windows 2008 x64 server and want to connect to a Oracle database and the standard drivers doesn't work. Something with version 7.3.3 requiring another set of drivers. Anyway, here is what I did. (You don't have to download the complete 1,87 GB package)

I chose to go through ODBC.

1. Download Instant Client Package - Basic (~40Mb) from Oracle (11g) for both 32 and 64-bit.

x64 found here:
http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winx64soft.html
and 32-bits found here:
http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html

2. At the same location, download Instant Client Package - ODBC (1Mb) for both 32 and 64-bits.

3. I started with the 64-bits install. Create a folder somewhere, i.e. C:\Oracle\InstantClient_64 and extract the files from the basic package (64-bit) to that folder. Extract the ODBC package (64-bit) to the same catalog. Open a commandprompt and run odbc_install. Be sure to start the prompt with administrator privileges.

4. Now we need som .ora files, sqlnet.ora and tnsnames.ora was enough for me. I copied them from another server. You will find lots of examples on the internet if you try searching google. I created a folder C:\Oracle\InstantClient_64\network\admin and placed them there.

5. Now we need to create some enviroment variables. Theese are shared between 32 and 64 bits enviroment. First add to the Path variable the directory you just created (C:\Oracle\InstantClient_64). Second create a new variable called TNS_ADMIN pointing to the location of you .ora files (C:\Oracle\instantclient_64\network\admin)

6. Run the ODBC-administrator with administrator privileges and try creating a system-dsn, it should work. The problem is that BIDS is a 32-bit application, so you can't see the 64-bits odbc sources from BIDS.

7. Create a folder somewhere, i.e. C:\Oracle\InstantClient_32 and extract the files from the basic package (32-bits) to that folder. Extract the ODBC package (32-bit) to the same catalog. Now here is the thing: open a commandprompt from C:\Windows\SysWOW64\cmd.exe and run odbc_install.exe. This installs this as a 32-bits driver.

8. Add the directory containing 32-bits package to the path variable (C:\Oracle\InstantClient_32)

9. Run 32-bits ODBC-administrator from: C:\Windows\SysWOW64\odbcad32.exe and verify that it works.

10. When you create the DSN, make sure you call them the same name in both 32 and 64 bits. Also, clear the username and make sure it's a system dsn.

11. Done.

Hope this will help someone, or me when this is long forgotten.

Cisco VPN 64 bit

Cisco VPN client for 64-bit does not yet exist as far as I know. Try the Shrew client, http://www.shrew.net instead. Also, it doesn't cut your internet access.

2009 Pass Summit Day 1

So today was the day the conference started for real. The day started with opening remarks from PASS's President Wayne Snyder and was followed by a keynote by Bob Muglia and Ted Kummert of Microsoft. They talked about SQL Server in general and the upcoming 2008 R2 release in particylar.

The session then were hit and miss. Highlights include Grant Fritchey's     spotlight Best Practices for Working with Execution Plans. While not exactly a BI-related session it was packed of useful information that anyone working with SQL Server will find useful.

The other highlight was hearing Italians Marco Russo and Alberto Ferrari of SQLBI discussing their SQLBI Methodology. In short is a mix between the Inmon and Kimball approaches to data warehousing (see here or google Kimball vs Inmon for a summary). Not only was it thought provoking to hear someone not subscribing 100 % to the Kimball approach, they also managed to combine outlining the differences between Kimbal vs Inmon and describing their methodology with being absolutely hilarious at the same time.

I might write a separate post just about that.

@@Identity with ASP

T-SQL note
@@Identity should be safe to use, unless you are using triggers, if you want to retrieve the latest ídentity value in a table.

Insert into myTable (Col1) Values('hello')
Select @@Identity

I know the @@identity is global within the connection, but it should be safe to use unless you are using triggers. When running from a ASP web application, there seem to be some connection pooling that makes this fail, or rather return the wrong ID. So if two concurrent users are calling the same procedure through an ASP application they might get each others id's.

Solution is as simple as the example: just use scope_identity() function instead or the output variable available in SQL2005 and later.

2009 Pass Summit Day 0 - Pre conference seminars

The pre-conference seminars were titled Delivering BI to the Masses. Maybe not too much of the material was that new to me the great speaker Peter Myers still made it worthwhile.

In the evening we were treated to a tour of the Microsoft Campus by Swedish developer Tobias Ternström and a very nice dinner at Elliot's. Courtesy of Microsoft Sweden. Thanks guys!

(BTW I just realised that you could publish blog post from Word 2007. This is really going to improve my English spelling and grammar)

2009 Pass Summit

So I and two of my esteemed colleagues are in Seattle for the PASS Summit this year. None of us had been before so we were really excited.

First I must say that Seattle is a really great city. We came here on Sunday before the conference, to attend a pre-conference session on Monday. Weather was lovely with the sun shining most of the time with hardly any rain at all. Great food too.

I will try to do a write up of each day. But there will be latency – I won't promise live blogging.

Windows mobile 6.5 for HTC Touch Diamond

Just now upgrading my HTC Touch Diamond 2 to Windows Mobile 6.5 downloaded from HTC homepage, released 2009-10-27.

Make sure you have a backup or your files on the memory stick.

I am running Vista on 64 bit, all you had to do was start the Windows Mobile Device Center and make sure the phone was connected. Download the setup from HTC and execute. The whole process took about 5 minutes (except downloading the setup).

First startup took some time, more or less just about the time as the upgrade... And then some wizards and done!

No problems what so ever, amazing...

Trying out new stuff: Yammer

As a part of the dev team at Business Vision you get to be a guinea pig for new services and software. I've been thinking a while about using twitter for internal communications instead of email.
However, some colleagues might not like the thought of everything they write being open for anyone to read, so I've entertained the thought of using some kind of internal twitter-service. I even considered writing a sharepoint web part.
Luckily I shared my plans with a co worker and he suggested using Yammer. That turned out to be really fortunate as I think Yammer is great. It has a great lightweight web UI and an optional desktop client. It keeps the simplicity of twitter while adding a few functions catering to more enterprisey users. For example the ability to create groups that you can restrict your messages (yams?) to.
The dev team is testing it right now. We will probably invite more people to join over the next few days so it's gonna be exciting to see how it is received.
(Pictured to the right is the Yammer desktop client)

Presenting the dev team: Dan Sydner

Before we begin, please let me introduce myself. My name is Dan Sydner and I've been at Business Vision for the last 5 years. Before that I studied computer science at KTH.

I spend my time at work doing consulting on the various BI tools Microsoft provides. Everything from handcoding (hardcoding?) SQL to SSRS SSAS and SSIS. I also do in house programming in C#, mostly maintaining our report delivery platform, Vision Reporter Web.


And last but not least I'm the editor of this blog! Trying to keep the other guys, Kalle and Richard in line.

Excel 2010 - What is new?

I have now been using Office 2010 CTP since the beginning of August and it's now time to give my view on Excel 2010 from a Business Intelligence point of view.

So, what's new? Not much actually, it feels more or less like Excel 2007 except the blue color. It feels more like a service pack or something. They have fixed many things that felt strange in Excel 2007. So the overall judgement is that Excel 2010 is a little bit better in general.

The two major changes that I think is of importance to Business Intelligence users is: Pivot Slicers and Sparklines.

Pivot Slicers
This is nothing but a flat pivot filter but with some intelligence. Instead of drilling down in a tree view, you can now filter from a more intuitive user interface. The main thing however, is that the slicer knows the choices in the filter that contains information.

The typical scenario is when using the time dimension on the current year. The month or quarters that hasn't happened yet, are greyed out. In the example below, there is no male with 5 children that has 0-1 cars in the underlying data set. This works with cubes, tables and PowerPivot (Gemini).

Sparklines

Sparklines is a nice feature, a small diagram in a cell. You can quickly get a feeling for historical data. I can't say it's a revolution but a nice feature. This will also be available in reporting services 2008 R2.

That's it... The big revolution was Excel 2007, now it's just a little bit better in general.

Gemini - PowerPivot

Right now listening to Donald Farmer about PowerPivot.

He just showed.how to import a 4 000 000 rows from sql server. Really no magic going on, if you are on a slow network 1 000 000 000 rows will take a loooong time.

Also, since powerpivot is a column based store, datamining wont be available at this point. That ruins the data compression.

You will be able to add additional meta data such as calculated nembers and measures. Many functions, mdx-like, like a lot of time functions. So you can more or less skip olap...

The coolest thing though, is that you can import data from multiple sources, ie sap and the web and define relationships between theese sources.

Last post from sharepoint conference 2009

Final note from the airport in las vegas. The grand finale was at LAX at Luxor, real good clubbing.

In total, a very good conference, but not that many news. Last years bi conference in seattle contained most of the stuff here, but this time it seemed to work...

Over and out

Skickat från min HTC Touch Diamond2

Connecting Silverlight Web Parts

In Bob German session about integrating Silverlight in SharePoint, did he demonstrate a sound solution of interconnect Silverlight application hosted in Web Parts.

As SharePoint's built-in support for Web Part communication is server side based, it require postbacks when transmitting data between web parts. This is bad, particularly for Silverlight apps as they (usually) loses there states after an postback, and it gives bad user experiences.

However in Bobs solution, the built-in Web Part communication are only used to mediate the DOM object ID between the Silverlight Applications. That enables a Silverlight Web Part to pass data using a HTML bridge. This technique allows instant communication and interaction between Silverlight web parts and Web part configurations on the SharePoint site as usual. Sweet!

Summary of Sharepoint Conference 2009

Ok, so there is one more day left at the conference, but we will be going home in the morning so I think it's time to sumarize the conference from a BI point of view.

I will divide this in Reporting, Monitoring, Analytics and a general sharepoint category. I havent heard one word about planning, so that is completly out of the picture from microsoft.

Reporting> Reporting services 2008 R2 is a great release! It feels like it is a stable and powerful product and together with the new features I think it will be a pleasure after upgrading. Except from maps, nicer looking charts and inline graphics and charts I think connecting multiple datasets to an object is one of the enhancements made.

Monitoring> Not so much has happened on the surface of performance point except the proclarity decomposition diagram. But a lot of work have been made integrating pps in sharepoint, and it's now more or less fully integrated. No more web sites "on the side". Excel services and Office web applications is also really cool. You can both view and edit a word, excel, powerpoint etc online without the client installed. They made an example of two people playing battleship on BI Power Hour. Cool!

Visio Services is a cool new addition to this area. Another way of visualizing information.

Analytics> The main topic here is Gemini a.k.a. Power Pivot. Really cool in memory database that can handle massive amount of data. However, I wonder how many users really are interested in this. I would say it's one or two per big company... Well, we will have to think and analyze this a bit further to see the true business value in this.

General Sharepoint> A lot of things is happening with collaboration and social computing. Everything is getting integrated and starting to feel like facebook. My site is developed, personal blogs and wikis, team sites. Things are starting to feel more mature.

Another thing is offline access to information. A lot of things is happening here. This is a part of the strategy, BI for everyone.

Cloud computing, Sharepoint Online is a strong focus from microsoft, that was made totally clear...

Finally, everything withing the office family is moving into Sharepoint. Integration is the key, so expect more on that area.

That's it for now.

Ssrs 2008 r2

Some great news from the new release of reporting services 2008 R2!

Maps, sparklines, inline charts etc and multiple datasets connected to one tablix!

Starting to look really great comparing to other vendors. Also the possibility to make an offline exe of a report, you could send a demo to a customer without the database.

Skickat från min HTC Touch Diamond2

Business Connectivity Services (BDC)

First session of today was about the next generation of the Business Data Catalog, now known as the Business Connectivity Services.

They have now added write functionality, which is a great improvement. Also, you can now connect enitities to office. The main example was hooking up a customers table to a sharepoint list, and to outlook contacts and beeing able to edit the back-end system from outlook contacts.

This raises some questions regarding security which wasn't adressed at this point.

You could probably build som neat applications with the new functionality...

First day at conference

There were not many news during todays sessions and key notes, including Steve Balmer. To sumarize the message I would say cloud computing and collaboration is the main theme of day one.

Sharepoint online will be one of the main focus areas from microsoft, and you will be able to run a whole or partial sharepoint enviroment seamlesly. In other words, you can run your critical LOB systems in house and other not so central parts of you sharepoint farm within the cloud.

The other focus area is collaboration including Office Web applications - Share and edit office documents online without installed clients, Gemini (Now officially called PowerPivot) - 100 000 000+ rows for self analytics in excel and social networks - facebook-like functionality built-in in sharepoint.

Sharepoint 2010 and Office 2010 public beta will be released in november 2009 and first version 1:st halv 2010.

Now we are off to meet with the other swedish attendees.

Profiler Woes

If I ever get my hands on the person responsible for reversing the meaning of "Yes" and "No" on the SQL Server Profiler exit confirmation box...



It's always the same, you're in a hurry to log out for some reason or another, you alt-f4 your way out of all open applications, clicking "No" in all the alerts about saving you work, until you try to do the same with the Profiler.

Well, well maybe I get the chance to have my say at this year's PASS Summit in Seattle next week!

Sharepoint conference 2009 in las vegas

We are attending the sharepoint conference in Las Vegas!

We will try to post information relevant to business intelligence continously.

Presenting the dev team: Richard Lautmann

The thing I like the best with working in the Business Intelligence segment is the possibility to make a great impact on a company performance.

The previous years I have primarily been working on shifting focus from our in-house developed products towards building solutions within the Microsoft BI stack. To achieve the same level of functionality within the platform we have developed a number of add-ins or packaged solutions by using the standard components provided by Microsoft.

I have also worked as a BI consultant in many projects and many roles the past 4-5 years. I am currently responsible for our development team at Business Vision and has been since april 2007.

My academic base lies within my Master of Science from the Royal Institute of Technology studies in echonomics at Uppsala University.

Blog creation

This blog came to life an hour ago. It was not an entirely unpleasant experience, by the pool at hotel excelsior, Malta. Want proof?

Blog manifest

Hello

we are Business Vision's development team. Here we will write about

• New cool stuff we find and like
• Things we do
• Tips and tricks

We work in the Microsoft BI sphere so expect a focus on that.