# Tuesday, 29 December 2009

With long  travel delays due to snowstorms and terrorism, I got to catch up on some of my reading. One book I read over the break that stood out was an unexpected one: Essential Silverlight 3 by Ashraf Michail. I say it is an unexpected one since the book is not about building business applications with Silverlight so you won’t see a chapter on MVVM or using WCF and asynchronous services. What you will see is a discussion of the internals of Silverlight, the guts of a ZAP file, and how media, layout, text, input, data binding, and the like work.

You maybe thinking, “I am a developer, I don’t care about vector graphics!” I thought the same but read the book anyway and learned a lot about how Silverlight works and how to best debug and performance tune an application. Maybe I am biased since I work at a company that has to know all of the details of the .NET framework to make our products work, however, I think that every developer should take a look “under the hood” and see how Silverlight works.

Ashraf has a very engaging writing style and the book is a quick read, I read it over the course of two or three days (on airplanes and in airports!) He breaks down the chapters really nice and the last part of each chapter is called “under the hood” where you learn something about the internals of Silverlight. I must admit that I did skim some of the advanced material about animations and wrote a note to come back to it if I need to, but the chapters on vector graphics, GPU acceleration, the Silverlight application architecture, and data binding were truly fascinating and will help me with all of my business applications.

If you are new to Silverlight this is the first book you should read, then pick up a book on building applications with Silverlight. If you have been using Silverlight for a while and want to take your development to the next level, read this book as well-epically the “under the hood” sections.

Lastly, if you use Silverlight in a Virtual PC, you will care about vector graphics!

image

Technorati Tags:
posted on Tuesday, 29 December 2009 06:35:08 (Eastern Standard Time, UTC-05:00)  #    Comments [0] Trackback
# Monday, 28 December 2009

About a week ago, I showed a simple demo on my blog of SQL Server PowerPivot for Excel, or just PowerPivot for short. I used SQL Azure as my backend and just did a mapping to a few Northwind tables and then built a pivot table using the Order Details table.

Today I will take that example one small step further and write a custom TSQL query and build a more advanced pivot table against that data source.  In a future post I will build a PowerPivot example against a true OLAP Cube, but that will have to wait for the new year. :)

Here is a TSQL query that shows an Order and joins to get the Product Name and Category as well as the Employee and Customer names. We also do a DATEPART() to get the order year off the Order Date. In addition we also calculate the Total Price. This will allow us to do many different sorts and groupings in a pivot table. The TSQL is here:

Select
od.orderid, o.orderdate,datepart(MONTH,o.orderdate) as OrderMonth,
datepart(YEAR,o.orderdate) As OrderYear,
emp.FirstName + ' ' + emp.LastName as EmployeeName,cust.CompanyName,
cust.City, cust.Country,
cat.CategoryName, p.ProductName,
((od.UnitPrice*od.Quantity) * (1-Discount)) As TotalPrice
from dbo.[Order Details] od
    inner join dbo.Products p
on od.productid=p.productid
    inner join dbo.Categories cat
on cat.CategoryID=p.CategoryID
    inner join orders o
on od.orderid=o.orderid
    inner join dbo.Employees emp
on emp.EmployeeID=o.EmployeeID
    inner join dbo.Customers cust
on cust.customerid=o.customerid

Now we have to connect to our SQL Azure database in PowerPivot. To do this, follow the instructions I placed on my blog last week to connect. After you connect, instead of choosing tables to connect to, choose the query option. Put in the TSQL from above and give your query a friendly name like Steve1 in PowerPivot (or rather NorthwindOrderDetails since we will be using this in our pivot table). Now you can use this query and its data to build a pivot table.

image

Building the PivotTable is pretty easy. Let’s use the TotalSales as our main value and Category Name and Product Name as our row labels. That means that we will see Category and be able to drill down into Product and get a sum for that category or product for each City (the field we choose for our column.) In addition we added a filter for Country that will allow us to see a drop down of each Country and we can filter for as many countries as we want. Lastly, we can add a “vertical slice” of Order Year that will add an additional filter in a graphical style. This filter is applied after your main filter (Country.)

image

I did this part in under a minute. This gives the user an extremely powerful pivot table and the ability to do some pretty sophisticated drill downs and filters. In addition, it is pretty easy to add/remove even more data (Customers and Employees for example) to this pivot table.

image

Enjoy!

posted on Monday, 28 December 2009 05:33:56 (Eastern Standard Time, UTC-05:00)  #    Comments [0] Trackback
# Sunday, 27 December 2009

Here is a special holiday message from Telerik!

PS: I did not make the video cutoff due to travel delays from Hong Kong to the snowstorm on the East Coast.

Enjoy!

posted on Sunday, 27 December 2009 21:26:59 (Eastern Standard Time, UTC-05:00)  #    Comments [1] Trackback
# Monday, 21 December 2009

At the PDC Microsoft released the Sync Framework Power Pack for SQL Azure tool. It is a tool built on top of the Sync Framework 2.0 that provides a wizard to keep an on-premises and SQL Azure database in sync as well as a Visual Studio template where you can take a SQL Azure database offline using SQL Compact (or whatever they call it these days.) Lets take a look at the data sync wizard today and soon look at the Visual Studio template.

I started the wizard via Start|Microsoft Sync Framework|SQL Azure Data Sync Tool for SQL Server. The wizard works by taking a database that is on-premises and creating a new one in SQL Azure and creating the SQL Agent jobs to keep them in sync. I could not find out how to sync two identical databases that already exist. To get started you need to provide your SQL Azure credentials and then the name of the new database that you are going to create in SQL Azure.

image

After you provide your credentials for your local server and select a database, you have to select a database to sync with. The next screen shown here allows you to select the tables you want to keep in sync. You also get to choose who wins in a conflict, the local server or SQL Azure.

image

Next you get to choose the order of your sync. This is a very smart feature, you get to say sync in this order to avoid the classic order detail missing order id problem.

image

After you finish up the wizard, it will go ahead and create the new database in SQL Azure for you as well as the tables and associated tracking tables. It will also create the SQL Server Agent jobs required to keep the data in sync. I noticed that on my local server I did not have SQL Agent running, so no sync was performed and after the wizard ran I had no data. So I had to go into SQL Server Agent and enable it and change the default schedule of my job (which was daily at midnight.)

image

You may want to get more creative with the SQL Server Agent schedule depending on your environment, however, the Data Sync wizard is a handy tool to get you started.

Enjoy!

Technorati Tags:
posted on Monday, 21 December 2009 05:59:41 (Eastern Standard Time, UTC-05:00)  #    Comments [0] Trackback
# Saturday, 19 December 2009

While I love Northwind, would do anything for Northwind, I realize that there really is not enough data in Northwind for meaningful demos. We’ve been using Northwind for 15+ years and the reason why we have not completely abandoned Northwind is because AdventureWorks is such crap. Someone at Microsoft took a relational pill one day and over relationalized AdventureWorks to 100th normal form. It takes about 7 joins to get a list of unique customers and their mailing addresses. Microsoft has admitted their mistake and given us “AdventureWorks Lite” a slimmed down version of AdventureWorks with less joins and less pain in the you know what. (Only 3 joins to get the customers and their mailing addresses.) In addition to ADLite, Microsoft has also given us a data warehouse version of AdventureWorks in star schema for BI testing called AdventureWorksDW.

You can now easily load these databases into SQL Azure. The databases were released on CodePlex along with a loading script that you have to run from the commandline. Be careful, the Datawarehouse database is larger and can cause a timeout on a slow connection, forcing you to do it over again.

image

Enjoy!

posted on Saturday, 19 December 2009 21:45:12 (Eastern Standard Time, UTC-05:00)  #    Comments [0] Trackback
# Friday, 18 December 2009

The Astoria (aka ADO.NET Data Services) team released an updated .NET 3.5 SP1 version of Astoria the other day. There are tons of great new features like projections and my favorite, an inline row count. As I said on my blog yesterday, you have to alert Astoria that you want to use the new V2 features, by default Astoria 2.0 runs in Astoria 1.0 mode. (For backwards compatibility.)

Telerik has enhanced the Data Services Wizard to support Astoria 2.0 (officially the “Data Services update for .NET 3.5 SP1” but I digress….)

Now if you have the updated Data Services DLLs on your machine, you will have the option to create a service using V1 or V2. If you choose V2, we will automatically set the MaxProtocolVersion property to V2 for you.

12-17-2009 5-38-01 PM

Happy Data Servicing!

Technorati Tags: ,
posted on Friday, 18 December 2009 05:41:52 (Eastern Standard Time, UTC-05:00)  #    Comments [0] Trackback
# Thursday, 17 December 2009

The Astoria (aka ADO.NET Data Services) team released an updated .NET 3.5 SP1 version of Astoria last night. This version of Astoria is an inplace update and will overwrite your current version of Astoria (System.Data.Services.*.dll). You can download it from the following locations.

The new version of Astoria has some very useful and powerful features. They include projections, data binding, row count, feed customization, server based paging and better BLOB support. There is one small issue, in order to support these new features you have to tell the framework you are using the new version of Astoria. For backward compatibility reasons, by default Astoria will work in “1.0” mode and none of the new features will work out of the box. To take advantage of the 2.0 features, you have to make two minor changes to the InitializeService method:

   1:  //change the IDataServiceConfiguration to DataServiceConfiguration
   2:  public static void InitializeService(DataServiceConfiguration config)
   3:  {
   4:      config.SetEntitySetAccessRule("*", EntitySetRights.All);
   5:      //take advantage of the "2.0" features
   6:      config.DataServiceBehavior.MaxProtocolVersion =
   7:          System.Data.Services.Common.DataServiceProtocolVersion.V2;
   8:  }

The first thing that you need to change is on line 2, change the interface IDataServiceConfiguration to be just DataServiceConfiguration (I am sure that there is a better way to do this, I have not figured it out yet.). Next, set the MaxProtocolVersion property of DataServiceBehavior to V2. After that you can take advantage of all the new features!

I want to take advantage of my favorite new feature, the inline row count. (I have been asking over and over for this feature, so: Thanks Astoria team!!!) The inline row count will return the number rows in the data feed regardless of paging ($skip, $top etc) as an XML element: <m:count>. This new property makes our RAD Grid and other paging aware controls much easier to perform paging. To make this work, you just add the inlinecount to the querystring:

servicename/entityname?$inlinecount=allpages
 

Let’s implement this with the example I did the other day using Telerik OpenAccess and the Data Service Wizard. If you remembered I built a simple Astoria 1.0 service exposing the Customers entity. We built this using “Astoria 1.0”, however, I upgraded my machine with the release of Astoria 2.0. When I use inlinecount, I get a 404 error. As with a brand new service, we have to tell the framework what version of Astoria we want to use before we can take advantage of inlinecount. I made the same changes as I did above (DataServiceConfiguration and MaxProtocolVersion ) and reran my application. Now when I type this URL: http://localhost:1191/WebDataService.svc/Customers?$inlinecount=allpages&$top=2

I get only the top two records but the total count of records.

image

Awesome.

Enjoy Astoria 2.0!

PS:

I thought that this was called “WCF Data Services”? According to the Astoria team blog:

Since this release is an update to the .NET Framework 3.5 SP1 we kept the name consistent with what was used in the 3.5 SP1 timeframe so that printed documentation, etc is consistent.  Going forward in .NET 4 and onwards you’ll see us use WCF Data Services.

Technorati Tags:
posted on Thursday, 17 December 2009 03:51:54 (Eastern Standard Time, UTC-05:00)  #    Comments [0] Trackback
# Wednesday, 16 December 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, 16 December 2009 05:14:02 (Eastern Standard Time, UTC-05:00)  #    Comments [0] Trackback
# Tuesday, 15 December 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, 15 December 2009 05:06:18 (Eastern Standard Time, UTC-05:00)  #    Comments [0] Trackback