THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
 in Alberto Ferrari (Entire Site) Search

## Alberto Ferrari

• #### PowerPivot, Stocks Exchange and the Moving Average

 In this post I want to analyze a data model that perform some basic computations over stocks and uses one of the most commonly needed formula there (and in many other business scenarios): a moving average over a specified period in time. Moving averages can be very easily modeled in PowerPivot, but there is the need for some care and, in this post, I try to provide a solid background on how to compute moving averages. In general, a moving average over period T1..T2 is the average of all the values between T1 and T2. Sounds easy, so why do I feel the need to write a post about it? Because in stock fixings, as in sales and many many other scenarios, the moving average cannot be computed that easily. If the period is – say – 10 days, then it can contain one or more holidays inside. during holidays, there is no data and this does not mean that the value during holidays does not exists, it means that holidays should not be used to compute the average. Moreover, different periods contain a different number of vacation days, making things a little bit more complicated. The business scenario is clear, let us describe the data model. We basically have one table, called Fixing, which contains the value of stocks for the last 10 years: There are a lot of columns here, since this is the data structure provided by my trading system but we are interested in only three columns, for the sake of this example: Stock: the name of the stock, i.e. Microsoft. Date: the date of the values Close: the final price of the stock As a very gross rule of thumb, if the price of the stock is higher than the moving average over a period of time, then the price of the stock will further increase, if it is below the moving average, it will decrease further. Normally two moving averages are used in conjunction and the points where the stock is changing direction are detected by the intersection of the two moving averages. Thus, we want to use PowerPivot to produce a chart like the following one, which represents the last three years of the Microsoft stock. When the red line crosses the green one going down, it is time to sell, when it crosses going up it is time to buy. If you use this rule to trade… don’t blame me for any loss, I only tried to explain why moving averages are pretty useful in the stocks market. Now, let us get back to business: how do we compute the moving average? In a previous post, I spoke about how to compute the difference in working days between two dates. This scenario is very similar since the moving average over 50 days is, in fact, the average of the last 50 “working” days of the trading market, i.e. the dates where the value of the stock exists in our table. Thus, the problem is now shifted to “how do I compute the date that is 50 working days before the current one?”. The answer is pretty easy: first of all we need to assign to each date in the tale an incremental number, counting the number of dates before the current one. Clearly, since we have many different stocks in the table, we must count only the dates where the price of the current stock exists. Let us add a calculated column to the Fixing table with this formula: ```DayNumber=COUNTROWS ( FILTER ( Fixing, Fixing[Date] <= EARLIER (Fixing[Date]) && Fixing[Stock] = EARLIER (Fixing[Stock]) ) )``` Now each row in the Fixing table has a number that uniquely identifies each date for a stock. Let us add a new calculated column to hold the date that represents “50 working days before now”: ```FirstDateOfRange50=CALCULATE ( VALUES (Fixing[Date]), FILTER ( Fixing, Fixing[DayNumber] = EARLIER (Fixing[DayNumber]) - 50 && Fixing[Stock] = EARLIER (Fixing[Stock]) ) )``` Basically, we use CALCULATE to get VALUES of the Date column in a filter context that shows the only one row that has a DayNumber equals to the current day number less 50. Now that we have the value of the first date of the range, knowing that the last date of the range is always the current date, we can compute the moving average over 50 days using this formula: `=CALCULATE ( AVERAGE (Fixing[Close]), FILTER( Fixing, Fixing[Date] >= EARLIER(Fixing[FirstDateOfRange50]) && Fixing[Date] <= EARLIER (Fixing[Date]) && Fixing[Stock] = EARLIER (Fixing[Stock]) ))` The key here is the FILTER expression, that creates a filter context that shows 50 rows from the Fixing table, exactly the 50 rows that are needed to compute our moving average. Repeating the same procedure for the 200 days average, you end up with this table inside PowerPivot: Et voilà, the hard part of the work is done. The remaining work is just to add a PivotChart as in the following figure. where we have selected Apple instead of Microsoft: As you can see from the figure, I added the Calendar table to the data model with the necessary relationship to be able to add the year to the chart but, this time, I did not use the Calendar table to perform working days computation since the addition of the stock name makes the model a bit more complex (a date might be “working” for a stock but not for another one) and the DAX formulas need to be more complex too. This is just another example of what PowerPivot can do for your reporting needs. If you want to touch the real Karma of PowerPivot, don’t miss the opportunity to follow one of the workshops I and Marco Russo are bringing all over Europe in the next few months. You can find more info on www.powerpivotworkshop.com. Hope to see you  there! Posted Wednesday, January 26, 2011 9:00 AM by AlbertoFerrari | 14 Comments Filed under: PowerPivot

• #### Working days computation in PowerPivot

 A very frequent question, when performing business analysis, is to count the number of working days in a certain period of time. Creating a data model with PowerPivot that gracefully handles working days is pretty easy, nevertheless it is worth a post. As you might already know, PowerPivot needs a calendar table to compute most of its time intelligence functions and this table needs to satisfy some important conditions: There should be no hole in the whole calendar, i.e. all dates of a year should be present. The key of the table should be of DATETIME type. Building this table is really straightforward using Excel: start filling a column with dates (use alt-dragging to fill it very quickly) and then add some very simple formulas to the worksheet, to obtain a table like the following one. Clearly, the number of dates that should be included in the table really depends on your data, always remember that the table should always start at the 1st of January and end at the 31 of December, with all the necessary years included. Leaving a hole means getting wrong data later. Before loading the table inside PowerPivot, it is necessary to add some info about working days and vacation, due to the title of this post . To do this, let us create a new Excel table containing the holidays, with a description of the non working reason, like the following one: You can add as many vacation days as you need to this table (and, if you wonder whether I consider holding a PowerPivot Workshop vacation… well, I surely enjoy it: it is very close to vacation!). Then, using Excel VLOOKUP function, we can bring the information about vacation directly inside the Calendar table, which now will contain some new columns: The interesting formulas are: WorkingDays =IF (OR ([@WeekDay] = 6, [@WeekDay] = 7), 0, IF (ISNA (VLOOKUP ([@Date], HolidaysTable[#Data], 2, FALSE)), 1, 0)) Holiday =IF([@WorkingDays]=0,"Holiday","Working Day") HolidayReason =IF (ISNA (VLOOKUP ([@Date], HolidaysTable[#Data], 2, FALSE)), [@Holiday], VLOOKUP ([@Date], HolidaysTable[#Data], 2, FALSE)) Here we state that a day is a working day if it is not Saturday or Sunday and it does not appear in the holidays table. Other formulas are very simple ones and define a string representation of holiday and holiday reason, as you can see from the figure. Please note that the WorkingDays column is an integer, holding 0 or 1. Moreover, I decided to name it plural instead of singular… this is not a mistake, it will make sense in the next figure. If you now create a PivotTable using only the new Calendar table and put Year on columns, MonthName on rows and the WorkingDays column as value, you get this first nice result, which shows the number of working days for each month. Due to the fact that the value is computed by simply summing values, it works even with different period selections, with or without holes. I have already put inside PowerPivot some tables coming from the AdventureWorks database, in particular iI have the SalesOrderHeader table, which contains the OrderDate. Thus, I can create a relationship based on the order date, as you can see in the following figure: Having done this, you can use the WorkingDays column to compute, for example, the average amount sold per working day, using this formula: AmountPerWorkingDay=SUM (SalesOrderDetail[LineTotal]) / SUM (Calendar[WorkingDays]) Which provides an interesting report: Well… this report looks interesting, yet it is not working as we would expect, since the average of 2001 is not 44,092,64. The issue here is that the sum of working days in 2001 is 257 but not all the months contain values for sales, yet their working days are counted in the denominator. If we really strive for perfection (which we do) then it is necessary to leverage some basic DAX knowledge and change the formula of the AmountPerWorkingDay to filter out the months where there are no sales. A more correct formula is the following: ```= SUM (SalesOrderDetail[LineTotal]) / SUMX (VALUES (Calendar[MonthNumber]), IF ( CALCULATE (SUM (SalesOrderDetail[LineTotal])) = 0, 0, CALCULATE (SUM (Calendar[WorkingDays])) ) )``` The complete explanation is a bit long, here we basically iterate over months and, if a month does not contain values for the sales, we remove it from the count of working days. Take some time to study it, then move on. The result is: Which now shows the correct result and a good course in the value, which grows year over year. AdventureWorks business is going in the right direction! Nevertheless, as it has been correctly noted by @Reuvain in the comments, the Grand Total column is still incorrect, since we have solved the issue with months but not with years. The value of zero over January, 2001 is counted to perform the average of January Grand Total. To solve this issue we need to iterate over years too, as in this (please note the outer and inner loop, over years and months). ```= SUM (SalesOrderDetail[LineTotal]) / SUMX (VALUES (Calendar[Year]), SUMX (VALUES (Calendar[MonthNumber]), IF ( CALCULATE (SUM (SalesOrderDetail[LineTotal])) = 0, 0, CALCULATE (SUM (Calendar[WorkingDays])) ) ) )``` Using this new formula the values will be correct at the grand total column too: Now that we can decide whether a day is working or not, there is another very interesting computation that I would like to show: computing the difference, in working days, between two dates. Computing the delta between two dates is a matter of a subtraction in PowerPivot, since dates are stored internally as floating numbers. But, how many working days do exist between two dates is a different problem, which requires some DAX acrobatics. Before moving on, we understand that we need at least two dates to perform a delta. Luckily, in the SalesOrderHeader table there are the OrderDate and the ShipDate, it might be interesting to calculate the difference, in working days, between these two dates. Now, time to think at the algorithm. The main idea is to assign an incremental number, to each date, that increases by 1 only on working days and remains the same over holidays. It is much easier to understand it looking at the next figure, the value of WorkingDayNumber in the box. You can see that the WorkingDayNumber column contains the value 3 from January 4 to January 6, since the last two days are vacation. If we had such a value, then to compute the delta in working days between two dates, it would be enough to perform a simple subtraction between the two WorkingDayNumbers. Now, the issue is how to compute such a value. It is not easy if you are not familiar with the way PowerPivot formulas need to be expressed but, believe me, after some time with PowerPivot the formula will come to your mind in a snap. The idea is to define a calculated column that counts the number of dates before the current one filtering only working days. Or, said in other words, sum 1 for working days, 0 for holidays, for all the dates that exist before the current one. Guess what? We have a column that contains 0 for vacation and 1 for working days: it is the WorkingDays column, so we need to sum it up for all the dates before the current one. Here is the formula: WorkingDayNumber = CALCULATE (SUM(Calendar[WorkingDays]), Calendar[Date] <= EARLIER(Calendar[Date])) Take your time to study and understand it… it is something that requires you to have already touched the Karma of PowerPivot… Once you have digested it, it is time to bring this information inside the SalesOrderHeader table. In fact, the SalesOrderHeader table contains two dates (ShipDate and OrderDate) but we need to compute the difference between the related WorkingDayNumber, which are not part of the SalesOrderHeader table. Since there exists a relationship between SalesOrderHeader and the calendar table, the value of the WorkingDayNumber can be easily computed using RELATED. The value for ShipDate, on the other hand, needs a slightly more complex DAX formula, since we need to search, inside the Calendar table, the value of WorkingDayNumber for the date that equals ShipDate. Here are the formulas: WorkingDayOrder = RELATED (Calendar[WorkingDayNumber]) WorkingDayShip = CALCULATE (VALUES (Calendar[WorkingDayNumber]), Calendar[Date] = EARLIER (SalesOrderHeader[ShipDate])) And now, the final result is simply computed by subtracting the WorkingDayShip from the WorkingDayOrder, which returns the number of working days that passed from the date of the order to the date of the shipment. Sounds easy? No, it is not very easy… yet it can be done and, after having studied it, you will find it easy. PowerPivot requires you to change your mind and think in the DAX way, roll up your sleeves and start studying! This is just another example of what PowerPivot can do for your reporting needs. If you want to touch the real Karma of PowerPivot, don’t miss the opportunity to follow one of the workshops I and Marco Russo are bringing all over Europe in the next few months. You can find more info on www.powerpivotworkshop.com. Hope to see you  there!
• #### PowerPivot Workshop: new announcement and early bird expiring soon #ppws #PowerPivot

 As always, I am a bit later than Marco in producing news. Nevertheless, I am very excited to tell you  the new date for the Frankfurt workshop on PowerPivot: February 21-22, 2011. Save the date and find all the relevant information on www.powerpivotworkshop.com, where you can also register a seat for the workshop with the early bird rate. Moreover, the early bird for the London date is quickly approaching: it will expire on January, 17., Thus, hurry up and don’t miss the opportunity to save some money and learn all the insides of PowerPivot directly from us. Posted Friday, January 14, 2011 10:30 AM by AlbertoFerrari | 0 Comments Filed under: PowerPivot
• #### End of Early Bird for the London PowerPivot Workshop is approaching

 Hurry up! On January, 17 the early bird rate for the London PowerPivot Workshop will finish. You have a few days to save £150 from the regular price. Don’t miss the opportunity to attend the best PowerPivot workshop in Europe saving some money too. Posted Friday, January 14, 2011 9:00 AM by AlbertoFerrari | 0 Comments Filed under: PowerPivot

• #### PowerPivot Workshop in London #ppws

 As you might have read in Marco’s post, the PowerPivot Workshop I and Marco Russo have prepared is now starting its European tour. We will be in London on February 7,8 2011 and then touch most of the European countries during the next few months. We have provided the workshop first in Amsterdam and it has been a huge success, it is now time to start the roadshow and meet people who want to learn PowerPivot all over Europe: I am really excited about this! You will find updated information and workshop dates on the www.powerpivotworkshop.com site, where you can also suggest us new cities where you would like us to be in the future. I really hope to see all of you soon and start together the new era of Self Service BI in the best way.
• #### PowerPivot: editing measures when you reach 45

 I have always been used to small fonts but now, as I am getting older, I’d better admit that a greater font is much more relaxing. Editing PowerPivot measures has always been a pain, since all you have available is a small text box and I hate to admit that I got used to leverage ZoomIt for a long time to edit measures. Today I ran into a great Windows feature that I did not know about: ctrl-wheel on the mouse inside a textbox increases the font size of the text box. It seems to work with most textboxes in Windows, even if I use it mostly inside PowerPivot to be able to read at a reasonable size. This is the final effect… great, as years go by!

• #### PowerPivot and Many to Many Relationships

 If you have already read this post from Marco Russo and have understood everything, then this new post of mine will be useless. But if you are like me, i.e. you have read it and have broken your head trying to understand the final (really smart!) formula, then this new post might help you to understand it better. Thus, this post does not contain anything new, it only shows graphically how to make many to many relationships work fast with PowerPivot. I am going to use as an example a database structure that I am currently working with, i.e. a database used to perform analysis of audience for a TV broadcasting company. The database structure is shown in the next figure: The fact table says that an individual is watching a network in specific point in time of a single date. The red box contains the many to many relationship (the evil, from the performance point of view). An individual belongs to many categories (she is a woman, she has less than 30 years and so on, each characteristic of the individual makes it belong to a category) and the user wants to browse the data using targets. A target is nothing but a boolean expression of categories (a target might be “women with less than 30 years”, thus being an AND of categories). So, the bridge table links individuals to targets through a many to many relationship. Now, let us see the same figure with an indication of the number of rows expected for a (very small, indeed) period of time: The fact table contains a lot of rows but it is in the range that PowerPivot can handle without any problem. After all, aggregating several millions of rows is not a problem for a great engine like PowerPivot is. The problem is that the user will make a filter on the Targets table and we need to propagate that filter to the Audience one, which is something that does not happen magically. In fact, the relationship between the bridge and the targets table indicates that, when a filter is imposed on the targets table, the bridge is filtered to show only the values that are in relationship with the targets. The other relationships are not affected by the filter, so the execution context sees all the individuals and all the audience rows. We need to write a DAX formula to make PowerPivot follow the relationships. My first trial, with this structure, was the naïf one: using SUMX I iterate over the bridge table and then, for each row, I use CALCULATE to consolidate the row context in the bridge to a filter context that propagates over relationships. Thus, the first formula looks like this: `SUMX ( TargetForIndividuals, CALCULATE (…))` Now, this formula will work but it is not fast. Indeed, it is incredibly slow. The reason is that if we filter only one target, SUMX will iterate over (more or less) 1/140 of the bridge table, thus it will perform an iteration over 10,000 rows. For each row it will open a new row context that CALCULATE will consolidate in a filter one. Since each row in the bridge corresponds to only one row in the Individuals table, the relationship, along with the filter context, will make only one individual visible. Now, if we compute a formula over the Audience table it will be computed for that individual only. By simple math, we know that, on average, each individual has +/- 900 rows in the Audience table. Thus, the computation for each individual is very fast, but there will be 10,000 of those computation, making the full process very slow. The following figure is a graphical representation of the algorithm:   In order to make this formula work fast we need to reduce the number of iterations over the bridge table. The idea is that if we are able to filter all the individuals belonging to a target in a single step, then we can use that filter to reduce the size of the individuals table which, in turn, will filter the audience table. Said in other words, we want to do a single step where we filter the Individuals table using the TargetForIndividuals as a sort of bitmap mask. With this algorithm in mind, it is now easy to read this formula (yep, I understand that writing it is harder, but reading it is at least affordable):   `CALCULATE ( …, FILTER ( VALUES (Individuals), CALCULATE ( COUNTROWS ( TargetsForIndividuals ) > 0 … ))` Let us read it. We use CALCULATE, the ellipsis contain the formula, which is not interesting for this post, the only thing to note is that the formula will work on the Audience table, performing some kind of calculation. CALCULATE will create a new filter context where we impose a filter over Individuals, using FILTER. The individuals table is unconstrained, before the filter context created by CALCULATE so VALUES(Individuals) inside FILTER will return all the individuals. But then, the FILTER call returns only the individuals where COUNTROWS (TargetForIndividuals) is greater than 0. At this point, we need to remember that the bridge table (TargetsForIndividuals) shows only the rows (10,000) for individuals who belong to a specific target, due to the filter present on the Targets table. Thus, the FILTER call will return only the individuals who belong to a target, again 10,000 rows. CALCULATE will then create a filter context where the Individuals table shows only those 10,000 rows and, due to the presence of the relationship, the Audience table gets filtered too, showing only the rows who belong to individuals who, in turn, belong to the specified target. Let us see this in the next figure: Now the situation is completely different. The formula does not perform any iteration, it will do a one-step filter of the Individuals table and then a single pass of the Audience table, looking for nine millions rows at once. Needless to say, this formula runs at a blazing speed. If you are interested in this kind of games, this is one of the topics of my incoming PASS Summit session and, needless to say, of the great PowerPivot workshop that SQLBI will bring to the Netherlands in December 1 and 2. I really hope to see you at both events!
• #### Banding with PowerPivot

 Banding is one of the most common requirements when you have the need to analyze continuous values, like the retail price of a product which may change over time, due to discounts and price variations. I have already spoken about banding with regards to SSAS here, now I would like to spend some words on banding with PowerPivot. This topic is covered in much more details in the upcoming book I have written with Marco Russo: “Microsoft PowerPivot for Excel 2010: Give Your Data Meaning”. To show the examples, I am using the fact reseller sales table in AdventureWorks and this banding table: Now, let us take a look at the various options that we have available in PowerPivot to perform banding. The first one is the naïf one. You can add a calculated column with a long formula that computes the price band: ```= IF ( FactResellerSales[DiscountedPrice] <= 5, "01 LOW", IF ( FactResellerSales[DiscountedPrice] <=30, "02 MEDIUM", IF ( FactResellerSales[DiscountedPrice] <=100, "03 MEDIUM", IF ( FactResellerSales[DiscountedPrice] <= 500, "04 HIGH", "05 VERY HIGH"))))``` It is quick, dirty, but it works. Clearly, even if I expect that this approach will be used by many Excel users, I don’t think that this formula is worth a post . Moreover, using this approach requires you to update the formula whenever you want to change the banding table (which is hardcoded inside the formula) thus, in the search for a better data driven approach, I am going to show some better solutions. Nevertheless, even if I don’t like this formula, it is worth noting that with PowerPivot you have a quick and dirty option. If you (as I do) extensively use PowerPivot to perform fast prototyping of complex BI solutions, then you will be able to show banding to end users with a real minimal effort. Then, when the demo is finished, it is time to search for something better. A second approach would be that of relying on the PowerPivot engine to create a relationship between the FactResellerSales table and the band one. Nevertheless, you cannot create such a relationship since the banding table, in its current format, does not have a valid key. It contains ranges, not values which can be the target of a relationship. Nevertheless, performing a simple change in the data model, you can follow the same approach used with SSAS and expand the banding table so that it gains a structure which can be used to create the relationship. The new band table format might be like this: Now, Price is a valid key and you can create a relationship between the FactResellerSales table and this table using the price. Clearly, since this table is too long to be filled by hand, you will need to create a simple VBA script that starts from the previous table and creates the expanded one, where each band is repeated for each single price. The code is not complex and might look like this: ```For Each Row In ActiveSheet.ListObjects("PriceBands").ListRows Dim MinValue As Integer Dim MaxValue As Integer Dim Value As Integer Dim newRow As ListRow MinValue = Row.Range(1, 2).Value MaxValue = Row.Range(1, 3).Value - 1 For Value = MinValue To MaxValue Set newRow = PriceBandsExpanded.ListRows.Add newRow.Range(1, 1) = Row.Range(1, 1) newRow.Range(1, 2) = Value Next Next ``` This solution works in a data driven way, is very simple to implement and solves the banding problem. Nevertheless, it requires one step (the creation of the expanded table) every time you update the configuration sheet. Moreover, it requires a bit of knowledge of VBA programming, which might not be in the hands on the average Excel user. Thus, it is better to find a pure PowerPivot solution to banding. The key point here is that we want to create a relationship based on BETWEEN, while in PowerPivot we can only leverage relationships based on identity of values. Thus, to override the limitation of PowerPivot, it will be necessary to avoid using its engine and leverage the full power of DAX to create a calculated column that mimics the BETWEEN relationship. Let us revert back to our original table, let us suppose that we create a linked table inside PowerPivot and call it PriceBands. We do not create any relationship between FactResellerSales and PriceBands, instead we can leverage the FILTER function to find, for each row of the sales, the correct price band. My first trial has been this: ```BandName=MAXX ( FILTER ( PriceBands, FactResellerSales[DiscountedPrice] >= PriceBands[MinPrice] && FactResellerSales[DiscountedPrice] < PriceBands[MaxPrice] ), [PriceBand] )``` In this formula we leverage FILTER to fine the PriceBands table and find the right row. Now, since FILTER returns a table (which, we know, contains only one row), we use MAXX to transform this table into a scalar value, which should contain the PriceBand name. As simple as this solution seems, it will not work because the MAXX function in PowerPivot does not work with strings, it only computes MAX of numbers or dates. If you wonder why, as I do, you’d better know that this is for “Excel compatibility”, thus it is by (wrong) design. The very creative reader might now think at a new solution, that is add an identity column to the PriceBand table, compute the MAX of that identity value (which is now a number, thus MAXX will work) and then use it to setup a relationship with the PriceBand table. Believe me, it will work fine (I tested it) but, clearly, it is not very elegant. The elegant solution is to leverage the CALCULATE function. By using CALCULATE you will end up with this formula: ```BandName = CALCULATE( VALUES (PriceBands[PriceBand]), FILTER ( PriceBands, FactResellerSales[DiscountedPrice] >= PriceBands[MinPrice] && FactResellerSales[DiscountedPrice] < PriceBands[MaxPrice] ) )``` The key is to use CALCULATE to create a new execution context where the PriceBand table is filtered so that it will contain only one row, using the same filter expression used before. Then, when PowerPivot calculates the VALUES (PriceBands[PriceBand]) expression to compute the distinct values of the PriceBand column, it performs the calculation in a context where there exists only one row, so the returning value will be the right band name. If, for some error in the configuration, the VALUES call returns more than one row, PowerPivot will raise an error. Otherwise, it the resulting table contains one row only, then the value is automatically converted into a scalar value and the formula correctly computes the band name. As it often happens with PowerPivot, the correct solution requires you to use and understand CALCULATE, and to have a creative mind to search for non trivial solutions to the problem. Nevertheless, the final result is a very compact, elegant and fast formula that works in a complete data drive way and let the user change the configuration sheet, refresh data and test several bandings at a glance. .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }
• #### Final Update of the Rough Cuts of our PowerPivot book available!

 The upcoming book Microsoft PowerPivot for Excel 2010: Give Your Data Meaning that I wrote with Marco is its final stage. You can see the final update through the “Rough Cuts” initiative by O’Reilly. The book will be available in bookstores in the first days of october.

• #### Remote Execution of SSIS Packages

 Having the need to execute a package on a remote server, I discovered  that DtExec has no option to launch the package remotely (something that everybody apart me already knew…). So… time to write some code. I wrote a WCF Service that executes a package, hosted it into a Windows Service (it can be hosted in IIS too, I just like services more than I like the web interfaces) and developed a test program that uses WCF to communicate with the server to execute the package. All this stuff has been indeed really easy to write (and this induces me to argue why Microsoft did not provide such a tool with standard SQL Server installation). Using the WCF callback methods I succeeded in returning to the caller full details of log and events produced by the package, so that I can run a package remotely and still monitor what happens on the server side from the client. Moreover, since I needed to call the package from inside an application, I implemented a nice progressbar that shows to the user the progress of the package, by inspecting the package events, so that he knows if he can have a coffee during processing or not. At the end, the solution is a very nice example of what can be done using WCF as a communication media and, since I don’t think that I’m the only one having such a need, I am sharing the sources with the web. If you are interested in getting the code, with some basic documentation, follow the link to the source code and have fun. BTW: the test application can be used without the need to understand all the code and is basically a DTEXEC replacement that runs packages remotely… something that can be useful to anybody developing SSIS code.