Share via

Time Series Reporting Stored Procedure - part 3 of 3

  • Article

This post continues part 2 of the series.  In this part of the series I will demonstrate how to create a report using the stored procedure and also how to call the stored procedure from Excel using the Data Mining Addins for Office 2007.

In creating a report, the first thing I did was to use BI Dev Studio to create a new Report Server Wizard Project.  I then, of course, gave the wizard a connection string to my Analysis Services database that contains my Time Series model.

After all that housekeeping, the wizard puts you on the rather ominous "Design the Query" page - which has a big giant text box for a query which is disabled so you can't type there, and a button labeled Query Builder...   Given the lack of options, I guess I'll just click that big button instead of futilely trying to type into the disabled text box!

Clicking on the Query Builder button launches the (what else?) Query Designer for Analysis Services.  First potential gotcha happens here.  If you have cubes and mining models in your Analysis Services database, the Query Designer will default to the MDX query builder which returns MDX Cellsets.  We don't want that - we want DMX Rowsets.  So if that's the case, you need to click the little "pickaxe" button to switch the designer to DMX mode.

image

Which makes the designer look like this:

image

And you say "Great!  Now all I have to do is type in my stored procedure call and I'm done!"  So you go and you click on the "Edit as Text" button right in the upper right-hand corner.  And you click on it, and click and click and click and click - clickity click click click.....clik....clk.....  But alas, nothing happens, because it's disabled.

However, as you mumble frikkafrakkafrikkafrakka under your breath and move that mouse pointer over to the cancel button to give up on the whole endeavor - WAIT!   All is not lost!  There's another way!  This little button image  switches from "Design Mode" to some unnamed mode I guess you might want to call "Text Edit Mode"  yay!  Hooray for small victories!  Now you can type in your stored procedure call something like this

 CALL TSSprocs.TimeSeriesReport("Reds Interleaved", "Sales", "Red", 25, 10)

And even preview the results if you want before clicking OK and sending that string to the uneditable text box on the main wizard.

On the next couple of pages, it doesn't matter what you really do - just make some selections to get the wizard to finish.  For example, choose a tabular report, add the sales to the details, pick a style - that kind of stuff.

Once you get to edit the actual report, the first order of business is to simply delete the table that was created!  OK, you could probably do this without the wizard, but hey, that's how people usually get started, no?

Next you go to the toolbox and drag out a chart to the report surface.  Make it a line chart and, since it's going to be the only thing on the report, MAKE IT BIG.

When you click on the chart you will have drop areas for series, data, and category.  The "Ordinal" column from the dataset will be the category (so go ahead and drop it there), and the two series columns will be the data (yes, the data) so drop them there.

There is still a problem that needs changing, as the data sets are automatically aggregated by Count and we need them to just be the values, or at least something that makes sense.  "Sum" makes sense for this data, so right click on each of the data fields, select "Series Properties" and change the Value field from Count(Sales) to Sum(Sales).  When you're done, your chart setup will look something like this:

image

At this point, you can just run your report and you're done!  The result (depending on your data, of course) will look like this

image

and you can make it as pretty as you want.

What's cool is that you can easily parameterize the stored procedure to select the models, columns, and series that you want to predict.  For example, in this case, my series are in the "Series" column in my model, so I can populate a drop down using the query

 SELECT DISTINCT [Series] FROM [Reds Interleaved]

and parameterize my query with the result to get an interactive report like this:

image

All done!

Now lets do the same in Excel (using those Data Mining Addins)

First, you have to have the Addins installed - it's not going to work otherwise - when you install the addins, make sure you check the "Data Mining Client for Excel" option.

Next you will need to use this part of the data mining ribbon

image

Particularly the "Data Mining" tab (to get here) and the connection and query buttons.  Before executing the query, of course, you have to click the connection button to connect to your Analysis Services database.

Next you click on the Query button to bring up the query wizard.  Actually nothing in the "wizard" part is going to help you out here, so as soon as you see it, click on this button

image

The advanced query interface has an incredible interactive query designer that would be just awesome to use anywhere, but again, we're doing something "special" so it's not going to help us out either, so just click on

image

And then select and delete all the text (Now that's what I'm talkin' bout - an editable text box!)

You can then type/copy/otherwise enter your stored procedure call - you remember, this one:

 CALL TSSprocs.TimeSeriesReport("Reds Interleaved", "Sales", "Red", 25, 10)

Click finish, specify where you want the data to be dropped and viola!  You have your time series prediction results all set up for you in Excel and it's even formatted nicely for you as a table!

image

Then you just Insert a line chart and.....almost!  Inserting a line chart actually adds the "Ordinal" column as a series so you have to fix that up by manually removing the series and setting the category labels to the Ordinal column, but then - a chart!

image

And now I'm finished with this ridiculously long post.  Of course there are many other things you can do - you can change the stored procedure to return deviations and plot those, you can change it to return all the series in a model, etc, etc, but this series of posts should lay the foundation for doing more.

Let me know if you like this series, if the level of detail is way too much or needs to fill in some more gaps, if the snark level is too high or too low, or even if the content is useful - always appreciate the feedback.

Comments