Data Centre: A Product Information and Promotion Management System

Introduction

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 its 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 its 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:
   1. Keeping all relevant information visible to product administrators has not been possible
   2. Some information has been stored using unconventional methods which has made extraction difficult and slow
   3. Any custom extraction of data has required custom reports which are time consuming to create and run
   4. There is little historical information available and what is available is largely meaningless
   5. 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 occurring 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 integration 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 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 easily 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:

1. Microsoft LightSwitch Extensions by Microsoft
2. Office integration pack by GridLogic
3. Excel importer by Microsoft
4. 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 1,000,000% better!

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 original vision and ended up with something that was perhaps slightly more efficient than our previous system and not able to be further customized. 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.

Screenshots

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:

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:

void AddThisPricingToProducts()
{
    /* this function adds the pricing to the product prices table 
     * I've done it this way so that I can change the end date on the price that currently
     * has a blank end date so that only one price ever has a blank end date
    */
    //Get all the price changes uploaded
    var TheseProductPriceChanges = 
        this.DataWorkspace.ApplicationData.ProductPriceChanges.OrderBy(x => x.Id);

    foreach( ProductPriceChange ThisProductPriceChange in TheseProductPriceChanges)
    {
        //if there is a price record which matches the candidate key on the product
        //prices table then check the information on that record
        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)
        {
            //Change the end date on the current price so that the system knows 
            //when to use the new price
            CurrentProductPrice.ProductPriceEndDate = 
                          ThisProductPriceChange.ProductPriceChangeStartDate;

            //if the Cost price and the RRP on the product price in question 
            //are the same as what we
            //are loading then don't bother loading anything.
            if (CurrentProductPrice.ProductPriceCostExGST == 
                    ThisProductPriceChange.ProductPriceChangeCostExGST && 
                CurrentProductPrice.ProductPriceRRPExGST == 
                    ThisProductPriceChange.ProductPriceChangeRRPExGST)
            {
                this.ShowMessageBox("The current pricing for product: " + 
                    CurrentProductPrice.ProductPriceProductCode.ToString() + 
                    "\nmatches the pricing currently in the system.
                     \n\nBecause of this no changes were made.");
                ThisProductPriceChange.Delete();
                continue;
            }

            //if the price that we are loading is more than 50% different 
            //to the price that is in
            //the database then the user will need to confirm that the price change is valid
            //and the price will not be loaded
            if ((((CurrentProductPrice.ProductPriceCostExGST - 
                   ThisProductPriceChange.ProductPriceChangeCostExGST) 
                / CurrentProductPrice.ProductPriceCostExGST) * 100) > (decimal)50.00)
            {
                this.ShowMessageBox("The current pricing for product: " + 
                    CurrentProductPrice.ProductPriceProductCode.ToString() + 
                    "\nis significantly different to the current price 
                    in the system:\n\nCurrent Price: " + 
                    CurrentProductPrice.ProductPriceCostExGST.ToString() + "\n\nNew Price: " + 
                    ThisProductPriceChange.ProductPriceChangeCostExGST.ToString() + 
                    ".\n\nBecause of this no changes were made." + 
                    "\nIf this price change is valid you will need to process it manually.");
                ThisProductPriceChange.Delete();
                continue;
            }
        }

        //Load the new product price into the database
        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;
        ThisProductPriceChange.Delete();
    }

    return;
}

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 occurred, 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 confirmation 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.

Thanks for reading!

History

• 23^rd January, 2012: Initial version