Building Loot Tables from Excel using the Content Pipeline

My current game project has a big emphasis on loot collection. I want my loot drop system to have a lot of flexibility for tuning drop rates though, so I’ve been creating a system similar to that found in Diablo II. I’ve settled on a system similar to the one described (in great detail) here, although considerably simplified for my purposes. What it boils down to is a table of values that I can easily add/remove entries from as well as tune any given values. Maintaining this type of data is where a spreadsheet really excels (ha!). But I need this data at runtime…

Enter the content pipeline. While the content pipeline has many built-in importers and processors, Excel spreadsheets are not covered. It was a relatively simple procedure to write my own importer and custom processor though. I could have settled for using handy intermediate XML and edited that by hand, but being able to work directly in Excel is much more convenient (and powerful).

So here’s what I’m going to show you: how to import data from excel, then how I processed it for my needs, and finally how to write it out to binary and read it back in at runtime.

What:

Create a custom importer and processor so that the content pipeline can build data from Excel files.

Why:

  • Data is much easier to create, edit and maintain in Excel compared to intermediate XML.
  • Data can be verified at build time using this method, as compared to reading in the data from un-built .xls or .csv at runtime
  • Spreadsheet source data can be imperfect (e.g. contain blanks or invalid entries) but the performance hit of processing this is taken at build-time, not at load time for the user.
  • The content pipeline can take advantage of the System.Data namespace, which isn’t available on Xbox360.

Background:

First off, here’s what I’m working towards getting at runtime:

public class TreasureClass
{
    public String Name;
    public int NumPicks;
    public int ChanceNoDrop;
    public Dictionary<String, int> Items;
    public int TotalProbability;
}

It’s a pretty simple class that basically represents a list of potential drops and their probabilities. A potential drop could be another TreasureClass (allowing high TreasureClasses to have a chance to drop items from a lower class) or an actual item. To drop from a TreasureClass, you just roll a random number and see which Item it corresponds to. If the item is another TreasureClass, repeat the process using that TreasureClass. Once you hit an actual item, drop it.

Simple, but powerful. It’s also very easy to change behaviour by adding new TreasureClasses or modifying probabilities.

So that’s what I’d like to end up with in game (a collection of TreasureClass objects). However, this is how I want to edit them (simplified a bit):

Name             Picks    NoDrop    TC1         ChanceTC1   TC2            ChanceTC2
Act 1 Equip A    1                  weapon03    50          armor03        50
Act 1 Equip B    1                  weapon06    50          armor06        50
Act 1 Equip C    1                  weapon09    50          armor09        50
Act 1 Melee A    1        100       Gold        30          Act 1 Equip A  30

It may not make a lot of sense if you haven’t read into how Diablo II’s loot tables work, but here’s the important part: data is stored in an Excel worksheet where each row corresponds to a TreasureClass.

Step 1: Content Importer

In my game’s ContentPipelineExtension library, I added a new class called XLSXImporter. This will read in the entire contents of the XSLX file into a DataSet object, which is then passed on to the custom processor. By writing it this way, I can create other processors that consume DataTables and thus use Excel for various types of data without having repeat all the file IO and worksheet parsing. In theory I could also re-use the processor with any importer that outputs a DataSet (such as a plain .csv importer for example).

Thankfully, we can make use of some handy System.Data classes since the content pipeline will be running on Windows. This makes grabbing all the data from an Excel file pretty trivial:

using System;
using System.Data;
using Microsoft.Xna.Framework.Content.Pipeline;
using System.Data.OleDb;

using TImport = System.Data.DataSet;

namespace ZomContentPipeline
{
    [ContentImporter(".xslx", DisplayName = "XSLX DataSet Importer", DefaultProcessor = "LootTableProcessor")]
    public class XLSXImporter : ContentImporter<TImport>
    {
        public override TImport Import(string filename, ContentImporterContext context)
        {
            DataSet dataSet = new DataSet();
            OleDbConnection connection = new OleDbConnection();
            OleDbDataAdapter dataAdapter;

            //For .xslx files, not .xls
            connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
                                            + filename + ";Extended Properties='Excel 8.0'";

            connection.Open();

            //Get the table schema, so we can programmitically grab all the sheets by name
            DataTable schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            foreach (DataRow row in schemaTable.Rows)
            {
                String sheetName = row["TABLE_NAME"].ToString();

                dataAdapter = new OleDbDataAdapter("select * from [" + sheetName + "]", connection);

                //Add the table to the dataset, giving it the appropriate name
                dataAdapter.Fill(dataSet, sheetName);
            }

            return dataSet;
        }
    }
}

That’s all there is to importing. This will consume an .xslx file and output a DataSet containing one DataTable per sheet. I’m grabbing all sheets out of convenience, but you could limit this to a particular sheet if you wanted to. However it doesn’t look like you can have parameters with Content Importers, so you can’t specify the sheet name as a parameter in the VS IDE Properties pane. Oh well.

Step 2: Process the data

Now we have the data from the Excel sheet in object form, but the game won’t be working with a DataSet. The next step is to write a processor that will make use of the DataSet from the importer and create the TreasureClass objects that I want to use in game. Much of this is custom code for my particular needs, but this is what it looks like:

using TInput = System.Data.DataSet;
using TOutput = System.Collections.Generic.List<ZomLib.Items.TreasureClass>;

namespace ZomContentPipeline
{
    [ContentProcessor(DisplayName = "Loot Table Processor")]
    public class LootTableProcessor : ContentProcessor<TInput, TOutput>
    {
        protected String sheetName;

        [DisplayName("Sheet Name")]
        [DefaultValue("Sheet1")]
        [Description("Name of the sheet in the .xslx to process")]
        public String SheetName
        {
            get { return sheetName; }
            set { sheetName = value; }
        }

        public override TOutput Process(TInput input, ContentProcessorContext context)
        {
            DataTable table = input.Tables[sheetName + "$"];

            List<TreasureClass> treasureClasses = new List<TreasureClass>();

            //name picks nodrop tc1 chance1 tc2 chance2 tc3 chance3 tc4 chance4 tc5 chance5
            foreach (DataRow row in table.Rows)
            {
                try
                {
                    string name = (String)row["Name"];
                    TreasureClass tc = new TreasureClass(name);

                    int picks = 0;
                    int.TryParse(row["Picks"].ToString(), out picks);
                    tc.NumPicks = picks;

                    int noDrop = 0;
                    int.TryParse(row["NoDrop"].ToString(), out noDrop);
                    tc.ChanceNoDrop = noDrop;

                    for (int i = 1; i <= 5; i++)
                    {
                        AddTC(i, tc, row, context);
                    }

                    tc.UpdateTotalProbability();

                    treasureClasses.Add(tc);
                }
                catch (Exception) {}
        }

        int rowsWithErrors = nonEmptyRows - treasureClasses.Count;

        context.Logger.LogImportantMessage("Loot table complete");
        context.Logger.LogImportantMessage("TCs added: {0}", treasureClasses.Count);
        context.Logger.LogImportantMessage("Rows with errors: {0}", rowsWithErrors);

        return treasureClasses;
    }

    protected void AddTC(int tcNum, TreasureClass tc, DataRow row, ContentProcessorContext context)
    {
        String itemName = "TC" + tcNum;
        String chanceName = "ChanceTC" + tcNum;

        if ((row[itemName] is String && (!String.IsNullOrEmpty((String)row[itemName]))))
        {
            string item = (String)row[itemName];
            int chance = 0;
            int.TryParse(row[chanceName].ToString(), out chance);
            tc.AddItem(item, chance);
       }
    }
  }
}

I removed a bit of the error checking and some using statements to condense this a bit. The only interesting part here is that I did add a parameter called “Sheet Name” to the process. This allows me to specify the name of the sheet in the Excel file (well, technically the table name of the DataSet) to process. This saves me making an assumption that the data lives in the first table or the table called “Sheet1$”, etc. Other than that, it’s just some basic parsing to create a List of TreasureClass objects.

Step 3: Write out the game object data to binary, then read it from the game itself

At the end of the custom processor, you’ll note that we are left with a List of TreasureClass objects in memory, in the content pipeline. That’s of no use to the game itself. We need to write out that list of objects to an .xnb file. Then we need to read it back in at runtime.

Fortunately, XNA does this automagically for us! See here for more info on what’s happening. The short story is that the content pipeline can do this using reflection when we don’t create a custom writer/reader pair of our own.

Wrap-up:

That’s all there is to it. The content pipeline is a powerful tool that I’m only slowly starting to understand. Being able to maintain my data in Excel is going to be extremely useful and was well worth the effort to get this set up. In the end, it really only took me about an hour to get everything working as desired.

How to Play Music in XNA games on Windows Phone 7 (and still pass Cert)

I’ve seen this come up in the forums a few times now: how do you properly handle music in an XNA game for Windows Phone 7? I got snagged by this in certification too, so I have some experience on how to handle it. Note that this is just my experience and there are always other ways to do things. This applies to XNA games on WP7.

What You Need To Consider

WP7 users can play music while playing your game. If your game plays music, you can’t simply interrupt the user’s current music. The relevant section in the Certification Requirements is “6.5 Applications that Play Media”.

6.5.1 Initial Launch Functionality
When the user is already playing music on the phone when the application is launched, the application must not pause, resume, or stop the active music in the phone MediaQueue by calling the Microsoft.Xna.Framework.Media.MediaPlayer class.
If the application plays its own background music or adjusts background music volume, it must ask the user for consent to stop playing/adjust the background music (e.g. message dialog or settings menu).

6.5.2 Configurable Functionality
If the application needs to play its own background music or adjust background music volume, it must provide the user with the configurable settings for its music.

 

The Basic Plan

Music in an XNA game should be played using the MediaPlayer class. If you use this class, you will automatically avoid the case where your music is playing at the same time as the user’s music. Note that if you try to use the SoundEffect class to play music, both your music and the user’s music can play simultaneously. This will fail certification and is specifically disallowed in the requirements.

Before we play music, however, we need to check if:

  1. The user is currently playing music
  2. We have permission to override the user’s music

 

The first case is simple. All we need to do is check if


MediaPlayer.GameHasControl

is true. If it is, then the user is not playing music and we have free reign to do what we want.
If the game does not have control though, we can’t call MediaPlayer methods (like Play, Stop, or Volume). At least, not unless the user says it’s okay. There are a few ways to approach this. We could prompt the user for permission when the game starts using something like a message box. That would work, but I would personally find that pretty intrusive.

Instead, I opted to have an option called “Allow game to override active music” in my options menu. If the user set this to true, I would re-enable my audio system and start playing music. Since it was part of the saved user settings, the game remembered the user’s choice for later.

Further Exceptions

If your game uses music for a cutscene, you can get away with interrupting the user’s music provided you resume it afterward. See section 6.5.3:

6.5.3 Applications that Play a Video or Audio Segment
An application may interrupt the currently playing music to play a non-interactive full motion video or audio segment (e.g. cut-scene or media clip) without asking for user consent. If music was playing prior to the segment, the application must resume music when the segment has completed.

Things To Remember

  • Don’t use SoundEffect to play music
  • Don’t change anything using MediaPlayer unless GameHasControl is true, or you have asked the user for permission in some way
  • Don’t simply check (MediaPlayer.State != MediaState.Playing) to see if you can play music. Use MediaPlayer.GameHasControl.
  • Perform your checks when resuming from tombstone, not just at launch. A user can tombstone, start music and then return to your game.

HOWTO: Track sales and downloads with XNA and Windows Phone 7 using Dotfuscator and Runtime Intelligence

Phew, that’s quite the headline. If you’re a Windows Phone 7 developer, you’re probably itching to get some real download and sales data, but Microsoft has stated that this won’t be available until the end of January 2011. Without that, you’re stuck checking the Zune marketplace rankings. These are just relative rankings however, and aren’t necessarily based on sales anyway. Some solutions for rolling our own analytics have been posted, notably here (overview of Microsoft Silverlight Analytics Framework) and here (walkthrough for Silverlight with Google Analytics).

You’ll notice that neither of those options work with XNA games (and you cannot mix XNA with Silverlight for WP7 and still pass cert). Fortunately, Microsoft announced that they would be partnering with PreEmptive Solutions to offer Dotfuscator and the Runtime Intelligence service for WP7 developers for free until the end March 2011. Nifty!

Unfortunately, I couldn’t find anywhere that really showed how to do this all the way through. I spent a couple of days on it though and now I can provide a guide based on my experiences, hopefully allowing you to avoid some of the gotchas. This is a long one, but hopefully you’ll get a good sense of what’s actually happening this way.

Overview

The plan here is to use Dotfuscator to instrument our existing game in such a way that it sends messages to the Runtime Intelligence server, where we can later log in and view analytic data. This guide focuses on using the Dotfuscator UI to instrument our project after it has been compiled, though you could theoretically tag your source code with the necessary attributes instead. By using the UI, we can tag methods in our game with attributes that specify what data we should send. The .xap is then rebuilt with the additional functionality, without modifying the source code.

In a nutshell:

  1. Get the required tools.
  2. Instrument our game and rebuild the .xap from the Dotfuscator UI.
  3. Run it.
  4. View analytics data on the Runtime Intelligence portal.

You’ll see that there’s a bit more to it than that, but this is all that is absolutely required.

Getting Started

First off, the partnership between Microsoft and PreEmptive for WP7 seems to be a bit last minute. In particular, there is no version of Dotfuscator specifically for WP7 yet (coming end of November). As such, some of these initial steps may not be valid a month from now.

To get started, you’ll need to get a copy of Dotfuscator with the appropriate license. For now, visit http://www.preemptive.com/windowsphone7.html . On the right is a note about “Can’t wait for the WP7-specific SKU?” with instructions on how to contact PreEmptive and obtain the software. If you want to instrument your app before the end of the month, you’ll need to do this (and to do so, you’ll need to make an account at PreEmptive). Otherwise, give the poor guys a break and don’t bother them if you don’t intend to use it. Grab a copy of the WP7 project template from the same page.

When you hear back from PreEmptive and follow their instructions, you should end up with:

  1. A copy of Dotfuscator Pro installed, with a temporary license.
  2. A Runtime Intelligence account, activated from within Dotfuscator. This is different from the Dotfuscator license.
  3. The project template, download from http://www.preemptive.com/windowsphone7.html (on the right)

Modify your game

Okay, I know I said we’d be instrumenting our game after it has been compiled, but there is one small thing we’ll need to change. I believe this will be handled automatically in the WP7-specific version of Dotfuscator, but I had to do this manually. Go to your WMAppManifest.xml and add ID_CAP_NETWORKING to your capabilities if it’s not there already:

<Capabilities>
<Capability Name="ID_CAP_NETWORKING" />
</Capabilities>

This is required by your instrumented game to send the analytics messages. Go ahead and build your game now too.

Instrument your XAP

Now comes the time to instrument your compiled game using the Dotfuscator UI. Don’t be confused by the name – we don’t need to use any of the obfuscation features (though you can if you want).

There is a handy video made by PreEmptive that you can watch that explains these steps nicely: http://www.youtube.com/watch?v=QvTueq7EJuY . I’ll run through them here as well.

Open up Dotfuscator. If you haven’t already entered your license key, do that. Then enter your Runtime Intelligence portal activation code under Help –> Activate Runtime Intelligence.

When prompted to “Select Project Type”, select “Open Existing Project”, highlight “More…” and press “OK”. Browse to where you saved “wp7app.template.xml”, which you downloaded from http://www.preemptive.com/windowsphone7.html.

Dotfuscator stores its project files in fairly intuitive XML. The “wp7app.template.xml” is a starting template that you will modify and then save a copy of for your particular game.

The first thing to do is go to the “Input” tab. Select the “${configdir}\Add_Your_XAP_here.xap” item and remove it. Then click “Add new input” and browse to your game’s .xap file. If you wanted to include/exclude specific .dlls from being instrumented or obfuscated, you can do that here. By default, everything is included (and nothing is obfuscated anyway), so we can move on.

Next, go to the “Instrumentation” tab. Here we need to add a few things as a little required housekeeping for the Runtime Intelligence service.

First, we need to setup some application-wide settings:

  1. In the tree view, find your main assembly and right-click on it. Select “Add Attribute”.
  2. Add *both* the “Application” and “Business” attributes. Select OK.
  3. Select the Business attribute you just added. In the right-hand pane, add the CompanyKey provided to you by PreEmptive. Add a CompanyName if you want, but it’s optional.
  4. Select the Application attribute. Give it a Name and a Version if you wish and the click the “…” to generate a GUID.

With that done, we need to decorate two methods so that our instrumented app knows when to send session information. The first is the SetupAttribute, which should go on your Game class’s LoadContent method. To do this:

  1. Drill down through the tree into your main assembly and find your Game class.
  2. Right-click on its LoadContent method and select “Add Attribute”.
  3. Choose “PreEmptive.Attributes.SetupAttribute”
  4. In the right-hand pane, set the CustomEndpoint to “Runtime Intellience Services for Windows Phone 7” by clicking the “…” and choosing it from the list.

Next, add the Teardown attribute:

  1. Right-click on your game’s “OnExiting” method and select “Add Attribute”.
  2. Choose “PreEmptive.Attributes.TeardownAttribute”.

Go ahead and save your project with a different name now. I just chose MyAppName.xml for simplicity.

Now hit the green play button to “Build Project”. This will take your .xap file and recompile it with the necessary code injected according to the attributes you placed. The resulting .xap file will live in “\Dotfuscated\YourAppName.xap”, based on where your original .xap lives.

At this point, we’ve done all that is absolutely required, so let’s see what happens.

Checking our data

Start up the WP7 emulator (“C:\Program Files\Microsoft SDKs\Windows Phone\v7.0\Tools\XDE Launcher\XdeLauncher.exe”) as well as the Application Deployment tool (“C:\Program Files\Microsoft SDKs\Windows Phone\v7.0\Tools\XAP Deployment\XapDeploy.exe”), both of which are part of the WP7 tools.

Deploy your instrumented .xap to the emulator (the one in the Dotfuscated subdirectory) and run it. There’s no indication that anything is different, so let’s check if we got our data.

Log into the Runtime Intelligence portal using the credentials you got from PreEmptive. You should see… nothing! Don’t panic.

There are a few caveats to working with the portal:

  1. By default, the filter is set to a day behind. Expand the filter and get it to include today’s date.
  2. It can take around an hour for messages to show up in the portal. They will show up as “Queued Data”. This just lets you know that the service is receiving messages from you.
  3. It can take 12-24 hours for messages to be processed. Once they’re processed, you can actually get information from them.

Yikes! 12-24 hour turnaround before you can see anything useful. Not good for testing and iteration. Worse yet, we’ve only really instrumented our app to send a message on startup and shutdown. That tells us, at best, how many times our app is run. We’re going to need more specific data, and a way of testing it faster.

Using Fiddler to view messages

I ended up using a tool called Fiddler to inspect messages as they were sent by the emulator. This post is long enough already, so I’ll point you to this blog post for instructions on how to get Fiddler to work with the emulator. A few notes: make sure you get Fiddler 2.3.0.7 (it’s a beta), and make sure you restart the emulator after starting Fiddler. If all goes well, you should be able to see traffic when you run Internet Explorer in the emulator.

It helps if you use Fiddler’s “Process Filter” and drag it onto the emulator. This way you avoid seeing packets from all the other programs that are running.

You should have Fiddler running and showing packets from the emulator at this point.

Now, go back to your Dotfuscator project and find the SetupAttribute in the Instrumentation tab again. Select the SetupAttribute and then find the “UseSSL” entry in the right-hand pane. Set it to false for the purposes of testing. Now when we inspect packets in Fiddler, we’ll be able to see the contents.

Save, rebuild the Dotfuscator project and re-deploy it to the emulator.

Now when you run your app, you should see a couple of messages appear in Fiddler:

If you use the XML view under “Inspectors”, you can see the contents of the message nicely. You should see that the event code for the messages are “Session.Start” and “Application.Start”, which seems pretty reasonable.

If you exit, you should see… no new messages! What??!

Start your app a second time and you’ll now see four messages: Session.Stop, Application.Stop and then the expected Session.Start and Application.Start.

This is unintuitive and has some implications. During the teardown method, the Runtime Intelligence code assumes the app is exiting. When WP7 apps are shutting down, network calls will appear to succeed, but nothing is actually sent. To deal with this, the Runtime Intelligence code instead writes the messages to IsolatedStorage and then sends them the next time the app is run. This means you will have a lot of incomplete sessions when viewing data on the portal. There is nothing you can do about this.

Reporting unique users

Right now, our app reports session data. While interesting, we could do a lot better. Let’s start by at identifying unique users.

To do this, we’ll make use of the phone’s unique id. This could lead to some duplicates if one user installs your game on multiple devices, but we’ll ignore that use case for now.

Adding an ID to session data is quite simple with Dotfuscator. To start, we’ll need to modify our game code so that we store the device’s unique id somewhere to access later. It needs to be available before the method tagged with “SetupAttribute” is called in order for Dotfuscator to use it (in our case, before Game.LoadContent is called). I just added the following to my game class:

#if WINDOWS_PHONE
using Microsoft.Phone.Info;
#endif

public class DatGame : Microsoft.Xna.Framework.Game
{

#if WINDOWS_PHONE
String phoneID = "0";
#endif

public DatGame()
{
[...]
var value = (byte[])DeviceExtendedProperties.GetValue("DeviceUniqueId");
phoneID = Convert.ToBase64String(value);
[...]

You’ll notice we’re now using the phone’s identity. That’s a new capability, so update your WMAppManifest.xml accordingly by adding:

<Capability Name="ID_CAP_IDENTITY_DEVICE" />

Now we have a field called “phoneID” that should contain the device’s unique ID. Rebuild your game. In order to hook this up in Dotfuscator:

  1. Go to your SetupAttribute and look at the right-hand pane.
  2. For “InstanceIdSourceElement”, select “Field”
  3. For “InstanceIdSourceName”, type “phoneID”

Now Dotfuscator has enough information to fill in the InstanceID of its messages with the phone’s unique ID. All messages will have this ID automatically, so you can now identify unique users for any message type you report.

Reporting Paid versus Trial

Now that we can identify unique users, let’s report on whether the app was launched with a full license or with a trial license. To make this easier, we’ll add some stub methods into our game code that we can then decorate using Dotfuscator.

There are many ways to do this. I chose to simply hook up to my PhoneLaunching event, which will be called only when the game is launched for the first time and not when tombstoned.

protected void OnPhoneLaunching(object sender, EventArgs args)
{
    if (Guide.IsTrialMode)
    {
        LaunchTrialFeatureTick();
    }
    else
    {
        LaunchPaidFeatureTick();
    }
}

public void LaunchTrialFeatureTick()
{
}

public void LaunchPaidFeatureTick()
{
}

This is a pretty simple example (you should check Guide.IsTrial and cache the result somewhere). Note that every time the game is launched, one of either LaunchTrialFeatureTick or LaunchPaidFeatureTick will be called. Rebuild your game. Let’s hook these up in Dotfuscator:

  1. On the “Instrumentation” tab, find the “LaunchTrialFeatureTick” method, right-click and select “Add Attribute”.
  2. This time, select “FeatureAttribute”.
  3. In the right-hand pane, set the Name field to something reasonable like “LaunchTrial”. This is how the feature will be identified in the portal.
  4. Do the same for “LaunchPaidFeatureTick”, with a name like “LaunchPaid”.

The reason I named these methods with “Tick” is because the method represents the entirety of the feature. If you want, you can identify the beginning and end of a feature separately in order to get a sense of how long a feature is used for. I won’t cover that here but it should be pretty intuitive.

Wrap up and where to go from here

At this point, your app should be reporting session data, along with a “Feature” message each time the game is launched indicating if its the paid or trial version. Each message has a unique id per phone, so you should be able to get a pretty accurate idea of how many users are playing your game. You’ll still need to wait for your data to be processed on the portal but you can check your work with Fiddler.

Don’t forget to enable SSL again!
Don’t forget to submit your instrumented .xap to certification and not your original!

What else can you do? You can easily add any custom data to your messages by filling out the “ExtendedKey” properties in Dotfuscator. For example, when a user finishes a round in my game, it also reports their high score. I also store the user’s last IsTrial setting, allowing me to send a message when they purchase the game or run the full version for the first time (rather than filtering LaunchPaid events for unique users). If there’s interest, I can show you how to do that too.

Questions? Comments?

If you found this useful, follow me on Twitter @benkane and check out my new game Zombie Accountant! Thanks!