What does your application/extension do? What business problem does it solve?
Office Products Depot Data Centre was designed to be the central hub for all product and promotional information within our business.
It is intended to be the one and only source of this information provided in a clear, concise, and consistent manner.
There were two major problems within our business that Data Centre was to provide a solution for:
1. We are a co-operative group of 37 independently owned stationery stores (dealerships) located around New Zealand. Because each store
is a separate business in it's own right, they each have their own database of product information within the ERP system that each store uses.
Historically each store has maintained their own database. In recent times we have moved towards holding product information at the
National Support Office (NSO) and then distributing that information to the store's databases. We have been using a copy of our ERP system
to do this which has created it's own set of challenges because it was not designed to do this. The main issues that have arisen from using our ERP system to
store product information are:
- Keeping all relevant information visible to product administrators has not been possible
- Some information has been stored using unconventional methods which has made extraction difficult and slow
- Any custom extraction of data has required custom reports which are time consuming to create and run
- There is little historical information available and what is available is largely meaningless
- Updating cost prices on a specific date in the future has been an awkward process made possible by a filthy hack of our ERP
Data Centre solves these issues by replacing our ERP as the main source of data and thereby displaying data in a clearer and more consistent manner,
using intuitive and easy to use forms, allowing users to create their own reports using familiar methods, recording and displaying
relevant and meaningful historical information, and providing a simple method to add cost price changes occuring in the future to the database.
2. Each year we produce 12 flyers which are up to 18 pages in length, one major catalogue which is around 500 pages in length, many email
promotions that are sent to our customers, and several other catalogues focused on specific categories or customer groups. Up until now we have
handled the process of creating this promotional material through multiple excel spreadsheets which have been emailed between our office and
suppliers for confirmation of data and then collated and checked before being used to populate the layout of the promotions.
As our promotions have grown more and more complex this system of excel spreadsheets has required more and more hours to maintain and distribute.
Only one person can be editing each spreadsheet at once which has meant that the problem could not be solved by throwing more resource at it.
Data Centre has solved this issue by providing a central repository for all promotional information and allowing our suppliers to access that
information and confirm it through a LightSwitch web portal prior to us using it in any publication. Multiple users can access the same information
at the same time. Many mundane data entry tasks have been automated or simplified. Thanks to LightSwitch and the Data Centre we hope to dramatically
reduce the turn-around time for collating and confirming information for promotions, thereby enabling us to put out more promotional material
of a higher quality with the same level of resource.
Data Centre required full integratation with our ERP system to allow information maintained in Data Centre to easily flow through to our ERP
and then down to our store's databases. This has been easily achieved thanks to LightSwitch being backed by SQL server which allows simple data extraction.
It was not a requirement of the system but there was a strong desire to have the Data Centre hosted in the cloud. This desire was fulfilled
thanks to LightSwitch's close relationship with Windows Azure. Data Centre is a desktop application but application services are hosted on Azure
and all data for the application is stored in SQL Azure. I have made Data Centre a desktop application because it requires access to Excel and
Outlook on user's systems. The Supplier Confirmation Centre portal which attaches to the same SQL Azure database is a web application hosted
entirely on Windows Azure. This needed to be a web application as we were not willing ask our suppliers to install an application we created
on their networks.
Data Centre will provide a massive increase in accuracy and efficiency in our business, enabling us to focus more on strategic initiatives and
less on data entry and maintenance.
How many screens and entities does this application have?
Data Centre consists of 28 screens and 25 entities over 2 projects.
Did LightSwitch save your business money? How?
LightSwitch has saved has saved us money because we have been able to develop Data Centre in-house. This has saved the expense of contractors
and/or a third party developer. LightSwitch has also given us the ability to easily extend Data Centre in the future which will save more money.
The functionality that Lightswitch has allowed us to put into the Data Centre will enable us to produce a greater amount of promotional material
which will make our business more money. We will also save money and create less stress around our business because product information is more
up-to-date and more easily maintained. Hosting the application on Windows Azure has also saved us money because we have not had to set up any new
servers and can scale the web application or turn it off completely depending on demand. If we had not used LightSwitch it would have been very
difficult to leverage these advantages of Windows Azure.
I estimate that LightSwitch saved us at least $20,000 in development costs. The functionality that LightSwitch has provided in the Data Centre
will continue to save our business tens of thousands of dollars annually and allows us to produce more promotions which will make money rather than save it.
Would this application still be built if you didn’t have LightSwitch? If yes, with what?
Yes. We were investigating getting Data Centre built by third party contractors as a web application using ASP.net web forms. I had previously
built a primitive version of Data Centre using ASP.net dynamic data but the application took twice as long to develop and had 10% of the functionality
of the LightSwitch Data Centre. LightSwitch has blown both of these options out of the water in terms of cost, speed and quality.
How many users does this application support?
10 users within National Support Office and potentially over 300 suppliers through the web portal, although not all will need to use it. If we
do need to give 300 suppliers access to it though this can be easilly achieved thanks to the scalability of Windows Azure. This, coupled with the
ability to completely turn off the web server and not be charged for using it when it is not required, was the primary reason we decided to use the Azure platform.
How long did this application take to actually build using LightSwitch?
It took approximately 4 weeks to complete Data Centre from start to finish although there was a lot of learning and reading done in that time. If I had to
create it again from scratch now it would take less time.
Does this application use any LightSwitch extensions? If so, which ones? Did you write any of these extensions yourself? If so, is it available to the public? Where?
Data Centre uses the following extensions:
- Microsoft LightSwitch Extensions by Microsoft
- Office integration pack by GridLogic
- Excel importer by Microsoft
- ComponentOne OLAP for LightSwitch by ComponentOne
I did not write any of these extensions. They are all available through the Extension Manager with the exception of ComponentOne OLAP which
can be found at ComponentOne's website. I highly recommend their extension and I think every LightSwitch app should use it!
How did LightSwitch make your developer life better? Was it faster to build compared to other options you considered?
I created an ASP.net dynamic data web application to manage data prior to using LightSwitch. With LightSwitch I have been able to build an application with
90% more functionality in half the time it took to design and build the Dynamic Data solution. Our users are much more satisfied with the final product which makes my life as a Developer
LightSwitch has made me the most efficient Developer that I have ever been. It not only encourages proper design, it makes it fast and simple to execute.
LightSwitch let me concentrate on adding functionality that would greatly benefit our users rather than spending time making the basics work.
I could not have provided the functionality in the available time frame without LightSwitch. I do not believe that a third party or contractor
could have either. We would have compromised our orignal vision and ended up with something that was perhaps slightly more efficient than our
previous system and not able to be futher customised. Thanks to LightSwitch we have laid solid groundwork on which to build on with careful,
contemplated, incremental development over time to solve more of the issues within our business.
Normally I program in C and I have not enjoyed going back to creating things in C after seeing the amazing power of
Visual Studio LightSwitch. It has been such an epiphany and I really feel like I'm running into the wind when I'm not using it now. Thank you
Microsoft for producing such an amazing, powerful, and easy-to-use tool! Thanks must also go to the amazing LightSwitch community that supports
the product and the support that this contest has had. It has been inspiring and very informative to see what everyone else around the world is
doing with LightSwitch. Thanks CodeProject!
Links, Screenshots, Videos
If anyone is interested in reading the full documentation (minus the large appendices) then it is available as a 7 mb word document here
This describes how many of the processes work and has been provided to our users as a training document.
You can also read about my journey and things I've discovered on my blog: therearefourlightswitches.blogspot.com.
The Home screen:
The Product Detail screen with a price history graph thanks to ComponentOne's Chart for Silverlight included in their OLAP extension:
The ComponentOne Olap Screens in action:
The Bulk Edit Screen that you can read about on my blog:
The Promotion Editing screen:
The Supplier Confirmation Portal:
Show us a cool or unique piece of code or technique that you developed as part of this solution.
This code is used on a screen where I have allowed users to upload a spreadsheet containing new pricing information from a supplier.
Once the information has been uploaded to the screen users click on a button which runs the following code:
var TheseProductPriceChanges = this.DataWorkspace.ApplicationData.ProductPriceChanges.OrderBy(x => x.Id);
foreach( ProductPriceChange ThisProductPriceChange in TheseProductPriceChanges)
var MatchingProductPrices = this.DataWorkspace.ApplicationData.ProductPrices.
Where(x => x.ProductPriceProductCode.Id == ThisProductPriceChange.Product.Id &&
x.ProductPriceSupplier.Id == ThisProductPriceChange.Supplier.Id &&
x.ProductPriceUnit.Id == ThisProductPriceChange.Unit.Id &&
x.ProductPriceEndDate == null);
ProductPrice CurrentProductPrice = null;
if (MatchingProductPrices != null)
CurrentProductPrice = MatchingProductPrices.FirstOrDefault();
if (CurrentProductPrice != null)
CurrentProductPrice.ProductPriceEndDate = ThisProductPriceChange.ProductPriceChangeStartDate;
if (CurrentProductPrice.ProductPriceCostExGST == ThisProductPriceChange.ProductPriceChangeCostExGST &&
CurrentProductPrice.ProductPriceRRPExGST == ThisProductPriceChange.ProductPriceChangeRRPExGST)
this.ShowMessageBox("The current pricing for product: " +
"\nmatches the pricing currently in the system.\n\nBecause of this no changes were made.");
if ((((CurrentProductPrice.ProductPriceCostExGST - ThisProductPriceChange.ProductPriceChangeCostExGST)
/ CurrentProductPrice.ProductPriceCostExGST) * 100) > (decimal)50.00)
this.ShowMessageBox("The current pricing for product: " +
"\nis significantly different to the current price in the system:\n\nCurrent Price: " +
CurrentProductPrice.ProductPriceCostExGST.ToString() + "\n\nNew Price: " +
".\n\nBecause of this no changes were made." +
"\nIf this price change is valid you will need to process it manually.");
ProductPrice NewProductPrice = new ProductPrice();
NewProductPrice.ProductPriceProductCode = ThisProductPriceChange.Product;
NewProductPrice.ProductPriceSupplier = ThisProductPriceChange.Supplier;
NewProductPrice.ProductPriceUnit = ThisProductPriceChange.Unit;
NewProductPrice.ProductPriceStartDate = ThisProductPriceChange.ProductPriceChangeStartDate;
NewProductPrice.ProductPriceEndDate = ThisProductPriceChange.ProductPriceChangeEndDate;
NewProductPrice.ProductPriceCostExGST = ThisProductPriceChange.ProductPriceChangeCostExGST;
NewProductPrice.ProductPriceRRPExGST = ThisProductPriceChange.ProductPriceChangeRRPExGST;
NewProductPrice.ProductPriceComment = ThisProductPriceChange.ProductPriceChangeComment;
This code won't let price changes greater than 50% go through because they usually indicate a data issue. It will not make any changes
to the database if the information is the same as what is already there. When a price change has occured it will make the end date
of the current price the same as the start date of the new price so that users don't need to perform that onerous task manually.
This saves our company time and money and saves our users from falling asleep while performing mundane data entry tasks.
Points of Interest
Did you learn anything interesting/fun/annoying while writing the your application?
If you're in New Zealand and your data is being hosted by Azure then the connection seems to be faster if you use the US region rather than
the asia region which is contrary to what I would have expected. Someone told me this before I tested it and I didn't believe them - I should have trusted them!
Did you do anything particularly clever or wild or zany?
I think the promotion confrmation system in the data centre is pretty clever and it will save our product administrators and suppliers a lot of time
(which saves our company and our suppliers money) chasing up on information and re-checking the same data over and over again.
Every time a supplier makes a change to information in the web portal it creates an entry on another table with the old value, the new value
and who made the change. A user at our end then has the opportunity to review the change that was made and alter it if they wish before ticking
a box and clicking a button to bring the change into the Data Centre. This way we are dealing with changes made to our information by outside
sources more effectively whilst still maintaining a degree of control over the integrity of the data. The Full process is explained in the training
documentation available for download at the top to the links and downloads section of this article.
Check out my Blog here And
don’t forget to follow me on
Twitter. Thanks for reading!