# Wednesday, December 16, 2009

Far and away the number one Business Intelligence client in the world is Microsoft Excel. While there are tons of data visualization tools out there, Excel is hands down the leader since it is both familiar to users and very powerful. Developers have built tons of business intelligence (BI) apps in Excel using connections to data warehouses (or cubes) and letting the users go crazy with pivot tables and charts.

Things are about to get even better with project Gemini or now  Microsoft SQL Server PowerPivot (we rebel and just call it PowerPivot).  PowerPivot is an add-in for Excel 2010 or Sharepoint. PowerPivot for Excel is a data analyses tool that allows you to connect to a database, download data and store it in a local data engine (VertiPaq) so you can slice and dice it to your heart’s content using familiar Excel tools such as pivot tables and charts. (You can then send it up to SharePoint if you like, but let’s just focus on Excel for now.) PowerPivot works in-memory using your local PC’s processing power and is optimized to handle millions of rows in memory on cheap commodity PCs. The VertiPaq OLAP Engine that is part of PowerPivot  compresses and manages millions of rows of data in memory for you.

There are many awesome features of PowerPivot, but something I learned reading the team’s blog on data importing was that PowerPivot supports SQL Azure natively. This scenario is great since you can download your SQL Azure data, store it locally and slice and dice offline.

Let’s imagine you have this set up in SQL Azure:

  • SQL Azure cloud based online-transaction processing database (OLTP)
  • SQL Azure cloud based online analytical processing data warehouse database  (OLAP)

You don’t have a local server to set up some Analysis services cubes and SQL Azure doesn’t provide that capability. You decide on a hybrid solution and provide a “self-service” distributed OLAP system. Your users, using Excel and PowerPivot, download from the cloud some of the OLAP data from SQL Azure and use their own hardware to do the number crunching. You maybe thinking, “I heard that this VertiPaq engine is great, but how will a laptop handle all of this data/processing?”  Remember if you architect your OLAP database properly, there will be a lot of “pre-crunching” already done, avoiding many segmentations and rollups. I tested about 100 million rows (of old Corzen data) on the “PDC laptop” with 2 GB of RAM and had near instantaneous results.While the cloud is real sexy right now, you can’t perform these type of operations without major latency.

Let’s walk through the basics, just getting some data from SQL Azure into Excel and PowerPivot. First you need Excel 2010 and PowerPivot. You can grab Excel via Microsoft’s web site for free (for now!) since it is in beta and PowerPivot from here. Make sure you download the proper version: x32 or x64.

Once installed, you will see a “PowerPivot” tab in Excel. You can click on on the PowerPivot window icon to get started.

image

The PowerPivot tool is mostly for managing data and connections. You can import data from a variety of data sources, including databases, files,  and services (including RESTful ones.) To connect and download from SQL Azure you have to choose From Database|From Other Sources from the Home tab on the ribbon.

image

This will bring you to a list of available data sources, choose Microsoft SQL Azure.

image

Of course, you will need to log into SQL Azure.

image

Once logged in, you can download data either from a TSQL query or just the tables and views raw. I choose to download just the tables from my Northwind database.

image

There are come cool features like the ability to select one table and then automatically select all related tables. You can also specify some filters to your imports (good if you are segmenting the data by user.) Once you are finished, PowerPivot will now import all of your data for you.

image

Now that your data is in PowerPivot you can play with it very easily. Remember that the data is located on the client in-memory as part of your workbook. (It is compressed.) PowerPivot gives you a tab for each data table you imported and you can go in and in typical Excel fashion, sort and filter, like I did for Barcelona (oh the fond memories of 5 TechEds in Barcelona…..) All of this sorting and filtering is happening in-memory on your machine, you can unplug your network cable if you like, however, you can also refresh your data as you need.  PowerPivot gives the developer several ways to refresh data. Some techniques and guidelines are here.

image

Next you can create some PivotTables, charts, or combination. Just choose PivotTable from the Home menu and then choose a PivotTable type. I will just make a dirt simple Pivot table to give you an idea of some of the interactivity you can build pretty rapidly. (I am also using the OLTP version of Northwind for this walk through, so I did not create any cool queries to work with yet. I’ll do another blog post with some more sophisticated demos after the holidays.)

image

Here is a very simple, yet super powerful PivotTable that I built in 30 seconds using the PowerPivot tools. Of course I just used the raw Order table and my lookups (ShipVia and Employee) are showing their integer value, but work with me. I have a pivot table where I can view the total amount each customer spent on freight in all of our orders,by country/city broken out by the employee who took the order. I can also dynamically filter the data set by the Country and also further filter it by the ShipVia data set.

This creates a very interactive data analysis “report” for your users. (Or they can create it themselves if they are Excel savvy.) In a few seconds the users can see multiple versions of the data, changing the filters (and sorts) on the fly. All shippers in all cities for all employees. No problem. Shipper ID 2 in USA only. Sure. You get the drift. (What is cool is that the users can share this via PowerPivot for SharePoint if they like!)

image

Here is what I did:

I Dragged the ShipCity and CustomerID to the “Row Labels” box to use ShipCity and CustomerID as the main data elements in our rows. I dragged Freight into the Values box to sum on freight for each CustomerID. (Customers have many orders with different freight costs, so freight is a good item to sum up.) I choose Country as a dynamic filter and will break out the Freight totals by EmployeeID (in the Column Labels box). Lastly, I added a Vertical Slicer where I see a slice of all shippers (ShipVia) and can do additional filters on all current data selections.

Pretty easy. Now combine this with some pre-built views on the server and your users can really go to town. All without a persistent connection back up to SQL Azure.

Enjoy!

Technorati Tags: ,
posted on Wednesday, December 16, 2009 5:14:02 AM (Eastern Standard Time, UTC-05:00)  #    Comments [0] Trackback
# Tuesday, December 15, 2009

Last week Telerik released the December CTP of the Data Services Wizard. I posted on my blog a video that shows how to get started, however, for those of you that like walkthroughs better, here is one using a WCF Data Services (Astoria) service.

Getting Started: Mapping Data With OpenAccess

To get started, first download and install the Data Services Wizard. After that, fire up Visual Studio and create a new Class library application named Telerik.DSW.Demo.Astoria.DAL. Run the OpenAccess “Enable Project to use ORM” wizard and then run the Reverse Mapping Wizard and map to your database. For this demo I mapped the Northwind database.

Map as many tables as you like, you can also manually remove the complex properties (Customer Collection from the Order entity for example) via the mapping wizard if you want to use that entity in your service. (Don’t worry we will have a solution for this when we have our January beta!)

Note: your wizard may not have created the ObjectScopeProvider in your DAL project. If you don’t have one in your DAL project via the main menu choose Telerik|Open Access|Configuration|Connection Strings. Then select the ObjectScopeProvider check box showed in the dialog below and click on ok.

image

Next Up: Using the Wizard

The WCF Data Service that the wizard will create has to reside in another (Web) project. So let’s create a Web project named Telerik.DSW.Demo.Astoria.Web.

image

Now it is time to start the wizard. Just select from the main menu Telerik|Data Services Wizard.

image

This will bring up the first page of the wizard, Select DAL Project. Here you select the name of the project that has your OpenAccess entities. Select the DAL project and click Next.

image

The Select Data Service screen is where you have to enter in some important information.

First put in the namespace, this is the namespace of your web project. (Future versions of the wizard will default to this namespace), and the name of your service, I choose Northwind as my creative service name. Also select which entities to generate as part of your service. I choose Customer, Order, and OrderDetail. Lastly, select which type of Service to create, in this case a WCF Data Service (our wizard did not catch up with the name, so you have to select ADO.NET Data Service (Astoria).)

image 

After you click next, you can preview the generated code on the View Output screen.

image

Click next and then you will be asked to choose which project to add the service to on the Finish screen. Select the web project, or Telerik.DSW.Demo.Astoria.Web and click next.

image

Now the wizard does a lot of work for you.

First it sets a reference to all of the WCF Data Services libraries (System.Data.Services and System.Data.Services.Client.) Next it sets a reference to the DAL project for you (in our case Telerik.DSW.Demo.Astoria.DAL) and also sets a reference to the Telerik OpenAccess DLLs for you (Telerik.OpenAccess and Telerik.OpenAccess.Query.) Lastly, the wizard created the Northwind.cs OpenAccess reference file as well as the actual data service (svc and cs) files.

image

The last step is to run the service. Just right click on the SVC file and choose View in Browser from the context menu. You will see your RESTful service come up in the browser. From here you can set up a client to consume the service in ASP.net, Silverlight, or any other Microsoft and non-Microsoft technology.

image

Enjoy!

Technorati Tags:
posted on Tuesday, December 15, 2009 5:06:18 AM (Eastern Standard Time, UTC-05:00)  #    Comments [0] Trackback
# Monday, December 14, 2009

Thursday, December 17, 2009
Application Development for the Windows Azure Platform

Subject: 
You must register at https://www.clicktoattend.com/invitation.aspx?code=143228 in order to be admitted to the building and attend.
At PDC 09 Microsoft did announce commercial availability of the Windows Azure Platform. This presentation will cover the key features of the Windows Azure distributed fabric based operating system that make it possible to build applications leveraging distributed computation and storage capabilities in the cloud. It will also cover higher-layer .NET Services such as SQL Azure, Microsoft’s distributed SQL Server database in the cloud and the .NET Services that provide extended connectivity and security across cloud and on-premise applications.
The focus will be on developing applications using the various platform APIs for development, deployment and management of Windows Azure Platform services.

Speaker: 
Bill Zack, Microsoft
Bill Zack is an Architect Evangelist with Microsoft. He comes to this role after serving as a Solutions Architect in the Financial Services Group of Microsoft Consulting Services. His experience includes developing, supporting and evangelizing .NET/SOA based frameworks used to jump-start development projects for financial services companies. Prior to joining Microsoft he acted as a Consultant, Architect, Administrator, Developer, and System Integrator. He has also authored several computer books and white papers. He is the Founder and President of the New York chapter of the International Association of Software Architects (IASA) and a member of the IASA Board of Directors He is also Co-Moderator of the New York City .NET Developers Group, founder and past president of the New York Enterprise Windows User Group, and the founder and past president of several other computer user groups.

Date: 
Thursday, December 17, 2009

Time: 
Reception 6:00 PM , Program 6:15 PM

Location:  
Microsoft , 1290 Avenue of the Americas (the AXA building - bet. 51st/52nd Sts.) , 6th floor

Directions:
B/D/F/V to 47th-50th Sts./Rockefeller Ctr
1 to 50th St./Bway
N/R/W to 49th St./7th Ave.

Technorati Tags:
posted on Monday, December 14, 2009 3:38:48 AM (Eastern Standard Time, UTC-05:00)  #    Comments [0] Trackback
# Friday, December 11, 2009

As a person who gives a lot of presentations, I was looking forward to reading Confessions of a Public Speaker by Scott Berkun. I figured that I could gain some insight from another speaker and pick up a few tips. The book was far better than that! I learned a tremendous amount about how to prepare and deliver professional presentations in a very relaxed and enjoyable way. The book was very readable, funny, and quite informative. Scott shares with us his secrets and emphasis on preparation, and how being prepared can get you out of trouble if things start to go wrong in your presentation.

Scott tells a great story and gives great advice both to people new to speaking and folks like me who have been doing it a long time. Scott's style and good humor will make the book interesting for people who don't have to give presentations as well. His stories are honest as well as funny and make reading the book quite enjoyable. Lastly, I read this book in the Kindle format and was quite impressed with the layout and graphics (these days if it is not on the Kindle it doesn’t exist to me.)  I highly recommend it to everyone needing to communicate effectively.

image

posted on Friday, December 11, 2009 1:27:17 AM (Eastern Standard Time, UTC-05:00)  #    Comments [1] Trackback
# Thursday, December 10, 2009

This week we released our third “alpha” CTP build of our Telerik OpenAccess Data Services Wizard on Telerik Labs. We have received tons of feedback on the tool and look forward to more feedback. The wizard’s development team and the entire OpenAccess team have come up with a roadmap and would like some feedback from the community on it. In the spirit of a transparent design, I am going to publish the entire roadmap here for community review. Of course all of this can change blah blah blah. (The lawyers made me say that.)

Beta 1: January 2010

  • Using T4 Code generation instead of text templates
  • VB.NET code generation (we had a ton of requests for this one!) 
  • Add WCF Data Services 1.5 full integration using the new Data Service Provider (of course we need to make changes to OpenAccess core to implement the DSP, however, the DSP is currently not fully documented by Microsoft, so we don’t know how much work this is just yet.)
  • Generate only the primitive types and prevent generation of complex entities for all services. (WCF barfs on complex types and entities.)

Beta 2: February 2010

  • Full support for WCF RIA Services
  • Full support for Azure Services
  • If you use the wizard to build a service, give you the option to automatically create a Silverlight client along with some code generation to consume the service to get you started
  • ASP.NET Dynamic Data support

Release: Telerik Q1 2010 Release

  • Full integration with OpenAccess’ installer. No longer making the wizard a separate install, it will just install as part of OpenAccess
  • Use of OpenAccess internal APIs
  • Visual Studio 2010 support
  • WCF 4.0 Support. We currently require the WCF 3.5 REST Starter Kit for two of our output modes: REST Collection and AtomPub. .NET 4.0 mode will eliminate this dependency and give you the option to produce WCF 4.0 REST services, etc.

After the Q1 release the roadmap is not super clear. I will assume that core development will slow down a little and that we will then focus mostly on adding support for new service types. I am sure that by next spring, there will already be some new service types to support like the release build of WCF RIA Services, maybe a new CTP of Astoria, etc.

Drop me a line and let me know what you think! Also, the tech support team is adding a separate Data Service Wizard forum in the Telerik support forums, so even though the wizard is technically a “lab” project and not supported, drop by and ping the team with your questions there. We’ll answer all your support questions there as well.

Enjoy and thanks for any feedback you send.

Technorati Tags: ,
posted on Thursday, December 10, 2009 8:10:33 AM (Eastern Standard Time, UTC-05:00)  #    Comments [1] Trackback
# Wednesday, December 9, 2009

Telerik released the December CTP of the Data Services Wizard earlier this week. Here is a video by .NET Ninja in training Peter Bahaa on using the wizard:

Telerik OpenAccess Data Services Wizard December CTP from Stephen Forte on Vimeo.

Technorati Tags: ,
posted on Wednesday, December 9, 2009 10:33:31 AM (Eastern Standard Time, UTC-05:00)  #    Comments [0] Trackback
# Tuesday, December 8, 2009

When: Friday, December 18, 2009, 6:30-10pm
Where: Microsoft NYC Headquarters, 1290 Avenue of the Americas, MPR room, 6th Fl
Sponsored by: Microsoft
Limited Advance Sale Tickets: $15 (per person)
http://www.ggdnyc.com/?page_id=14

You’re invited to an evening of conversation, inspiration and dinner! Join us to mix and mingle with other women working in technology. Dinner, dessert, wine and beverages will be served. We’ll culminate the evening with a raffle to win great prizes. The first 50 people to purchase tickets online will receive a gift bag from Microsoft.
Subject & Speakers:
Bonnie Halper, Founder of StartupOneStop.com
Peter Laudati, Developer Evangelist, Microsoft
Melek Pulatkonak, President & COO of Hakia.com

*Please Note* Tickets for this event can be purchased online only. Tickets are non-refundable. If you want to bring a guest, you must purchase a ticket for them. Attendees must bring photo identification and order number to event. There will be no ticket sales at the door.

flyer

posted on Tuesday, December 8, 2009 7:42:06 AM (Eastern Standard Time, UTC-05:00)  #    Comments [0] Trackback
# Monday, December 7, 2009

We are proud to announce our December CTP of the Telerik OpenAccess Data Services Wizard (formerly known as the WCF Services Wizard). The Data Services Wizard (DSW) allows you to easily create a CRUD data service layer for your application. The DSW does this by using a data access layer already built by OpenAccess and automatically generating the C# code for your endpoints. The types of endpoints you can create are:

  • WCF Data Services (FKA Astoria and also FKA ADO. NET Data Services)
  • Raw WCF endpoints
  • WCF REST Collection endpoints
  • WCF ATOMPub endpoints

This version of the Data Service Wizard is very robust; we have made lots of changes based on your feedback. Our #1 piece of feedback from customers has been to integrate the DSW with OpenAccess and Visual Studio. I am proud to announce that in this version the DSW is fully integrated with Visual Studio!

We have also started to integrate the DSW into the OpenAccess product itself. No longer is the DSW a standalone product; the wizard is now  located under the “Telerik” menu in Visual Studio and looks like the other OpenAccess wizards. Integration will get tighter in the near future and soon just be part of OpenAccess proper, not a separate download.

The basics of the wizard are the same. The DSW will ask you which project your OpenAccess entities are located in and then which entities you will expose in your endpoint.

ScreenShot1

After you choose the entities you want to expose and what type of service you want to build (WCF, Astoria, etc), you can preview the code that is generated.

ScreenShot2

A major improvement with the December CTP is that you can now automatically insert these service endpoint files into a project, eliminating the manual step of copying them on over. This will make building the services so much easier!

image011

Go grab the wizard here. It is still considered a Telerik Labs project, but we will move it to a fully supported beta in January with our next build. On the dock for the next build (early January) are:

  1. Using T4 Code generation instead of text templates
  2. VB.NET code generation
  3. Add WCF Data Services 1.5 full integration (using the new Data Service Provider)
  4. Prevent the generation of complex entities for all services

Future versions of the product will be fully part of OpenAccess and will also support RIA Services, Azure Services, and Visual Studio 2010 and WCF 4.0. Download today and send us feedback!

posted on Monday, December 7, 2009 9:39:31 AM (Eastern Standard Time, UTC-05:00)  #    Comments [0] Trackback