SSAS has quite a few functions that have different purposes and can make a lot of potential calcuatlions a lot easier.  A recent issue I was faced with is I had a new measure group to build based off a fact table.  This measue group had quite a few Calculated Members that needed to be built in order to complete the all the measures that would be needed.

I came across an interesting problem where I needed to use a measure from a seperate measure group, and apply a dimension to the measure to get a total. To get down to details, I needed the Gross USD (United States Dollars), only when applied by dimenions of food and beverages...not when involving merchandise.   Now, the identifier for Food, Beverages, and Merchandise were all part of one dimension named "Family Grouping".  So I knew in the end I needed the sum of Gross USD, and I needed the value of it only against Food and Beverage. 

So, at first I thought, maybe I would use the Scope Function, but that seemed like it wasn't really answering my issue, as the SCOPE function seemed to apply values across a particular range of a given measure. 

No, what I wanted was to be sure that whenever I got my GROSS USD total, it always gave me the amount when directly related to my particular dimensions...and the answer to that was to use the SUM function.

So my first stab at the sum function was to use code similar to this in my calculated memeber, minus all the other "who-ha".

SUM({null:[Dim Type Grouping].[Type Grouping].&[2]}, [Measures].[Gross USD])

What this did is it gave me the sum of the Measure Gross USD across my Dimension of type grouping 2...but I was surprised to see it included the type Grouping 1 as well.  This wasn't what I wanted as the the type grouping of 1 had no bearing on my calculated member that I was trying to achieve.  The Null statment previous my Dimension is what caused it to pull in my first dimension as well.  Admittedly I still need to figure out why that is the case.

So to fix my calculation I made a slight modificaiton, and got this.

SUM({[Dim Type Grouping].[Type Grouping].&[2]}, [Measures].[Gross USD])

The query above in my calculated member, properly returns only the Gross USD when related to the Dimension of typeGrouping 2.

In the end, the Sum function is quite powerful, and can be used in a variety of ways, and should not be confused with the AGGREGATE function, which works a bit differently.

Sql Server Analysis Services, a powerful tool, large in scope, deep in subject.  You could literally write books on SSAS, and they have, but what is it's use?  Well, Business Intelligence is the first obvious answer, but BI can be delivered from just SSRS (SQL Server Reporting Services).   What is a report though? A report is generally speaking a predefined view of data that allows a user to get the values they want to see from a set of data.

SSAS delivers the same concept, plus other data that the user hasn't even requested yet, but, as we all know, they'll ask for it tomorrow.  Today the user is happy with seeing total sales for a particular week, itemized by stores.  However tomorrow they want to see sales for the week, itemized by category instead of stores.   Can you deliver this in SSRS? Sure you can...lets work up a new query and a new report.... but how would SSAS potentially make this easier/faster?

SSAS can bind to SSRS, or other third party reporting tools, but where it really shines, IMO, is it's "auto-magic" functionality in Excel.  As can be seen in the picture below, in 2007/2010, if you open a sheet, you can click on the Data tab, then choose "From Other Sources" and then use the option "From Analysis Services".

Naturally you'll need access to an SSAS cube that has been created already, but excel will let you work with this cube in a drag and drop style environment.  A pivot table is setup for you and calculations generated based off the columns, rows, and numbers(measures) you drag into the fields to see.  Automatically allowing on the fly filtering from drag-drop menus, filters, or if you have 2010, you can use Slicers.  Slicers being basically windows with filter options letting you quickly select multiple values to aggregate data by.

So think about it like this, with SSRS only, you could get a request for a report that you can deliver in a few hours.  With SSAS, and just Excel, that request could be avoided completely, the end-user could just drag-drop the values they want to see on a pivot table and see the numbers immediately.   Allowing them to play around and see some numbers that aren't really critical enough to actual get someone to make a report for.

Also, another handy feature of excel, is the ability to select a pivot table you already created and just spawn a pivot chart from it with a few clicks.  So essentially your spawning sales dashboards in moments, instead of hours.  And again your trashing sales dashboards you don't like because the overhead cost to make them was so tiny why not create a few and throw them away? It's not like someone slaved for hours to make a pretty pie chart or bar chart, you did it in seconds with a couple mouse clicks.

All of this of course depends on someone setting up SSAS, getting the cube built, dimensions, proper measures, and applicable permissions.  Again a very broad subject, and this blog post still probably doesn't hit the general concept well enough, but the key is to understand that SSAS is very powerful in Business Intelligence, and if leveraged correctly can save lots of time in end-user data analysis.

I'm working on my own application for Windows Phone 7, like many people are.  Part of the requirements I've set for my application is to let the users send the context of what they are working on to other people via SMS or Email.  The reason being, that I'm not making my application work on Android or Iphone, but if the user can at least do their work in my app, and send the results to other users, it may be beneficial.

To send either an SMS or Email were going to use the same Namespace.

using Microsoft.Phone.Tasks;

Now, lets look at how easy it is to send an email from your Silverlight WP7 application.

private void btnSend_Click(object sender, RoutedEventArgs e)
        {
            EmailComposeTask ect = new EmailComposeTask();

            ect.Body = "A really great source that provides the body for your email would go here";
            ect.To = "somebody@email.com";
            ect.Subject = "WP7 Email";

            ect.Show();
        }

The code above is obviously linked directly to a button click for simplicity of example.  We've instantiated an EmailComposeTask, preset some values, and then used the Show method to the launch the email application.  Provided the user has set up at least one or more email accounts, they will go through email task screens they at this point are most likely very familiar with.  Prompted to choose an email account, and potentially just hit send based on the values you provided before launching the email application.  

Sending an SMS is part of the same namespace and follows the same basic steps.

private void btnSendText_Click(object sender, RoutedEventArgs e)
        {
            SmsComposeTask sct = new SmsComposeTask();

            sct.Body = "A great message that takes into account 160 character concepts goes here";
            sct.To = "1235554567";
            sct.Show();
        }

 Again, same general flow, task is instantiated, we can set some basic values, and then launch the corresponding task by using the "Show" method.  This, like the email task, requires user interaction to be completed.  This means they can also change the values preset by your application prior to sending, something to keep in mind when deciding to use these tasks.

Considering how simple these tasks where, I encourage you to review the many other tasks in the Microsoft.Phone.Tasks namespace.  You'll see a lot of features have been simplified for you via these tasks, so please familiarize yourself with them before potentially reinventing the wheel.

XML isn't new, it's been around for quite awhile, and will most likely be around for awhile longer.  The basic concept of nodes and attributes has been used in a lot of different areas of design in an effort to bring uniformity.  Admittedly in the past, I wasn't to thrilled about dealing with some of the nastier XML documents, but with the arrival of LINQ to XML, me and XML became friends again.

Looking at an XAML file, we can see that essentially it is just namespaced XML.  It can be loaded an parsed like any other XML document.  Here I have an example of a exported canvas from Expression Design.

<?xml version="1.0" encoding="utf-8"?>
<Canvas xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" x:Name="LinqToXaml" Width="3600" Height="3600" Clip="F1 M 0,0L 3600,0L 3600,3600L 0,3600L 0,0">
	<Canvas x:Name="Layer_1" Width="3600" Height="3600" Canvas.Left="0" Canvas.Top="0">
		<Path x:Name="Path" Width="1361" Height="1371" Canvas.Left="989.5" Canvas.Top="899.5" Stretch="Fill" StrokeLineJoin="Round" Stroke="#FF000000" Fill="#FF451616" Data="F1 M 1670,900C 1795.22,900 1912.53,934.093 2013.28,993.567C 2214.72,1112.47 2350,1332.82 2350,1585C 2350,1828.51 2223.87,2042.34 2033.82,2163.81C 1928.62,2231.06 1803.82,2270 1670,2270C 1294.45,2270 990,1963.32 990,1585C 990,1490.42 1009.03,1400.32 1043.44,1318.37C 1146.67,1072.51 1388.33,900 1670,900 Z "/>
		<Path x:Name="Path_0" Width="968.593" Height="1018.48" Canvas.Left="2567.52" Canvas.Top="408.874" Stretch="Fill" StrokeLineJoin="Round" Stroke="#FF000000" Fill="#FF9F0E0E" Data="F1 M 3050,410C 2570,600 2560,990 2570,1010C 2580,1030 2840,1130 2940,1300C 3040,1470 3300,1420 3300,1420L 3400,1050C 3400,1050 3660,950 3460,730C 3260,510 3050,400 3050,410 Z "/>
	</Canvas>
</Canvas>

So in the above file we have a Canvas object as a container, and it contains a Canvas with some basic size attributes. That canvas then contains two path objects that  I drew while in Expression Design.  So lets drop a lot of the fluff and just get down to how we actually get to the values of this XAML file.  Use whatever platform your comfortable with, console application, web site, or a LINQPad type program.  First thing to do is make sure you have access the the xml objects were going to use.

using System.Xml.Linq;

 Now we need to load our xaml file. The XDocument object handles this for us nicely in one handy line of code.

XDocument xDoc = XDocument.Load("C:\\LinqToXAML.xaml");

So, the XDocument.Load method brings in for us our entire document, and if you take the xDoc object and output it as a string, you'd have your entire Xaml file at this point.  But, accessing individual values in the XAML file requires accessing it via the namespaces attached to it.  If you look at the root canvas, you'll see the namespaces listed as attributes.

<Canvas xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" x:Name="LinqToXaml" Width="3600" Height="3600" Clip="F1 M 0,0L 3600,0L 3600,3600L 0,3600L 0,0">

The first name space starts with xmlns and this is the document namespace, the second namespace is has the "x" prefix to identify it, and anything that lives in this namespace will use that "x" prefix to identify that it belongs to that namespace.

Lets add get our XNamespace object declared and initialize it's value.

XDocument xDoc = XDocument.Load("C:\\LinqToXAML.xaml");
XNamespace xspc = xDoc.Root.Name.Namespace;
XNamespace otherSpc = "http://schemas.microsoft.com/winfx/2006/xaml";

This is a handy way to make the root name space available to us in code as Is shown above by accessing the namespace property of the root, and for example you can also see how to hard-code the namespace if needed.  With these namespaces setup, were now ready to write our LINQ statements to access our XAML.

XDocument xDoc = XDocument.Load("C:\\LinqToXAML.xaml");
XNamespace xspc = xDoc.Root.Name.Namespace;
XNamespace otherSpc = "http://schemas.microsoft.com/winfx/2006/xaml";

var theVals = from x in xDoc.Descendants(xspc + "Canvas")
				select x.Elements();

This query isn't very good, because it'll give us duplicate data, but the main point to see is that when specifying the descendants we want, we specify the root namespace and join it to the Canvas object we wanted to look for.  This is the basic concept of how we access namespaces with LINQ to XML.

Lets start to refine our query a bit and return only our Path Objects.

XDocument xDoc = XDocument.Load("C:\\LinqToXAML.xaml");
XNamespace xspc = xDoc.Root.Name.Namespace;
XNamespace otherSpc = "http://schemas.microsoft.com/winfx/2006/xaml";

var theVals = from x in xDoc.Descendants(xspc + "Canvas")
		select x.Elements(xspc + "Path");

 Now were filtering our query to say only give me the path elements, again when accessing the path element, we include our root name space declaration.  But what if we want to access the Name attribute in this xaml file which happens to be in a different namespace.  We extend our query and include the other namespace.

XDocument xDoc = XDocument.Load("C:\\LinqToXAML.xaml");
XNamespace xspc = xDoc.Root.Name.Namespace;
XNamespace otherSpc = "http://schemas.microsoft.com/winfx/2006/xaml";

var theVals = from x in xDoc.Descendants(xspc + "Canvas")
		select x.Elements(xspc + "Path").Attributes(otherSpc + "Name");

 The other name space is used because that attribute is prefixed by the "x" namespace.  Do we have to use the root namespace though when we access the other attributes that are not in the "x" namespace? No.

XDocument xDoc = XDocument.Load("C:\\LinqToXAML.xaml");
XNamespace xspc = xDoc.Root.Name.Namespace;
XNamespace otherSpc = "http://schemas.microsoft.com/winfx/2006/xaml";

var theVals = from x in xDoc.Descendants(xspc + "Canvas")
		select x.Elements(xspc + "Path").Attributes("Data");

As you can see, when we access the Data attribute, of our Path object, we didn't need to specify the namespace like we did when accessing the object itself. Obviously I haven't provided the output results of these queries, but I encourage you to use whatever your tool of choice is and try this out for yourself.

In our last query, we accessed the Data property of a path object.  If we wanted to find a particular path object, we could of put in a where clause to our query and specified the name of the path object, and then taken it's "Data" values and manipulated it as we see fit. 

When developing for Silverlight or WPF based applications, like anything else, there is a lot of tools and information out there to assist you in the development process.   Microsoft has made quite a few tools to help us in these endevours, among those tools, that' I'd like to discuss, is Expression Design, and Expression Blend.

Some people would say "well duh", obviously you can use those, and talk about them in great detail.  For some people it's not quite so obvious where to start at, and when.  Perhaps they stumbled upon a tutorial that shows them how to perform one function in Blend, and then another tutorial showing the same thing in Visual Studios.   None of these even mention bothering to use Expression Design....   so when and why would we use one tool over another.

Expression Blend is a powerful tool, and a lot of tutorials will lead you toward it, but what is the tool essentially? To me, it's a layout manager, control designer, and animation studio.  That's my own personal definition of it.  Can you do more then that in it? Yes, but is it ideal for more then that? In my opinion, not really.  When I'm writing complicated code, I'd prefer to use visual studios. I'm not going to define a C# double animation in Expression blend, I'm going to code it in VS.  But will I write a storyboard or state in Expression Blend? yes, preferably every time if I can pull it off.

So, where does design come in? Many of you may know, that you can export design as a silverlight canvas or WPF canvas.  What does this accomplish for you?  A lot if you really take an open-minded look to it.  The drawing tools in design are obviously superior to that of Blend, and essentially whatever you can draw in design can be exported to a canvas.

So lets actually think about leveraging the design abilities of Expression design and draw UI that throw out the normal squares, rectangles and circles....Lets make some crazy shapes that bring us interfaces that break the norm.   And when designing these interfaces think of the animations you can use against them.  You can draw a bunch of wonky shapes in expression design..apply gradients.....whatever you can think of, and then export these objects as a canvas...open it in expression blend, and have an animation field day with them.

So great, were using design to make really unique objects that were exporting, and then animating in blend.  What else can we do with Design and the canvas export?  Well what is XAML in it's basic form? It's namespaced XML.  You can take Linq to Xml and iterate through any XAML so long as you load your namespaces correclty....Linq to XML and XAML will be saved for another blog post in the future.

Another thing to let your mind rumble on.... If I'm using Design to make objects, which in the end have paths and coordinates, and I"m parsing it with Linq to XML, what can I do with those path objects?  Think Sql 2008 Geomertry types...and equating images to a database....yeah more on that in the future. 

This site is run on blogengine.net, and it has quite a bit of web.config entries required to support it.  In a shared hosting environment that is common when purchasing web-hosting from companies such as Godaddy, or discountasp.net, your likely to put in some virtual directories under your domain.  For example http://www.somecooldomain.com/someVirtualDirectory/

A virtual directory will automatically inherit the config values of the parent. unless cleared or overridden in the child directory.  In our example here, we want to host a WCF service in the sub-domain of our site, while BlogEngine.net is setup in our root directory.  To do this, lets first clear out the entries from blogengine.net that will conflict with our virtual directory.  To do that add the following entries to the web.config of our WCF service.

In the root node, preferably near our authentication statement.

<roleManager enabled="false">
      <providers>
        <clear/>
      </providers>
    </roleManager>

In the pages node add this entry to clear namespaces

<namespaces>
        <clear/>
      </namespaces>

Next in the http modules add these remove statements

<remove name="WwwSubDomainModule"/>
      <remove name="UrlRewrite"/>
      <remove name="CompressionModule"/>
      <remove name="ReferrerModule"/>

Next, to just be sure in IIS7 add this in system.webserver node under modules.

<remove name="ScriptModule" />
      <remove name="WwwSubDomainModule"/>
      <remove name="UrlRewrite"/>
      <remove name="CompressionModule"/>
      <remove name="ReferrerModule"/>

Now this takes care of what you may be able to google and hunt for in regards to blogengine.net, however you still may have some additional problems related to your WCF service.

For instance, the first one being that after all these fixes your getting a web error stating "Failed to execute URL".  Long story short, your web-host may be running your app pool in classic mode, and if you change it to integrated mode, this error should go away.  Potentially to be replaced with another error.

Another common problem is naturally trust levels, if you just used the standard Visual Studio WCF service template, then your WCF service is most likely configured for WSHttpBinding.  Which can be an issue based on your trust level.  If you have to keep WSHttpBinding, then your going to want to do additional research on how to keep that.   In my case it was easier just to remove my WSHttpBinding and replace it with a basichttpbinding since this WCF service was going to be consumed by a Silverlight app anyways.

Another issue that can arise is the web-host or parent config may cause issues with the actual .svc type itself. In which case you probably just need to add the build provider to the web.config

Add this in the compilation node.

<buildProviders>
        <remove extension=".svc" />
        <add extension=".svc" type="System.ServiceModel.Activation.ServiceBuildProvider,System.ServiceModel, Version=3.0.0.0, Culture=neutral,PublicKeyToken=b77a5c561934e089" />
      </buildProviders>

Finally, another potential error related to virtual directories and WCF services is the actual allowing of prefixes accurately. And that of aspnetcompatiblity issue. This can all be resolved in the same part of the web.config with the following addition.

<serviceHostingEnvironment aspNetCompatibilityEnabled="false">
      <baseAddressPrefixFilters>
        <add prefix="http://www.yourdomainname.com/yourdirectory/"/>
      </baseAddressPrefixFilters>
    </serviceHostingEnvironment>

Hopefully this covers all the potential configuration errors with a WCF service in a Virtual Directory under blogengine.net

The term simple should probably be avoided when speaking of SSAS. For those of you wondering what SSAS is, it's short for SQL Server Analysis Services.  Now if your wondering what's that, this blog post is probably not for you.  However if you are involved in database reporting in any shape or form, I encourage you to take some time to familiarize yourself with it, it's very powerful when applied.

Recently, when trying to work on a relatively new cube for my company, I had some difficulty finding a clear explanation on how to accomplish the task of generating an Average value based off my fact table.  In my efforts to find the answer I saw that, like many other things, there are many ways to skin the cat.   While searching I came across a lot of references to calculated measures, but no great tutorials on how to implement it.   Hopefully I can remedy that by providing a somewhat decent tutorial on creating a basic calculated measure.

In my scenario, my fact table has a "Seconds Elapsed" column that represents the amount of time spent on each line item.  My goal is to allow all my dimensions, including time, sum and average the "Seconds Elapsed".  In some cases you may want to avoid including a time dimension in your measure, which is a semi-additive, and not covered in this post.  So conveniently, were going to let every dimension have it's dirty little way with our measures.

So, we have our measure for "Seconds Elapsed", and it's set to sum.  This measure includes zero values, and values greater then zero, all of an Integer type. You can see an example of the data in question from this picture taken from an "Explore Data" view of our fact table.

 So ignoring SSAS in general, what's the basic formula to find the average value of something?  Sum / Count.   Essentially an aggregate divded by an aggregate.  To find the average time elapsed we need to take the Sum of  "Seconds Elapsed" in scope and divide it by the in scope Count.  Scope is easy in our example since it has no limitations no matter the dimension.  Scope could potentailly be a bit more difficult to deal with if we were going to use our average as a Semi-additive.

So, lets build what we need for our calculated measure,  we already have our Sum measure in "Seconds Elapsed", but next we need a count measure, as a generic count of our fact table would not be suitable. The reason for this is some of our "Seconds Elapsed" fields have 0 values.  If we include these zero values in our Count, it'll skew our average. So we need to create a measure that gives us a count of only the "Seconds Elapsed" with a value greater then zero.  To do this we create a named calculation by right clicking our fact table in the data source view and choose new named calculation. This brings up a form allowing us to enter our calculation.

As you can see we use basic T-SQL here to evaluate our "Seconds Elapsed" column and result in a 0 or 1.  This will give us a new measure that we can Sum as our divsor.  Now no matter what dimension we apply, we'll have an accurate count of how many rows to include as our divisor in our average calculation.  

As you can see in the picture above, where we have a "Seconds Elapsed" value greater then zero, the "TimeAverageDivisor" column has a 1, and all other values are 0, thereby giving us our accurate count.  We will now include "TimeAverageDivisor" as a measure, by going to our cube structure, and adding a new measure, then choosing our named calculation, setting the aggregate to "Sum".

Finally, we've got all our building blocks, so now we need to create our calculated Measure.  To do this, we go to our cube and click on the calculations tab.

The small green arrow above I've so amazingly crafted with MSPaint, is pointing out the "New Calculated Measure" button.  Cllicking on this will bring the handy dandy window that lets you input your basic information about this new measure your creating, and the associated formula.  Best advice at this point is, to use drag-and-drop techniques to write your formula for you.  On the left we see our dimensions and measures.  We will drag out our "Seconds Elapsed" sum measure, enter the standard division symbol "/" and then drag out our "AverageTimeDivisor" we built earlier.

The query is a very basic MDX Expression, named "AverageTimeSpent", and will be automatically made available as a new measure in any of our analysis done.  That's the end, build and deploy your solution, and you should now have an "AverageTimeSpent" measure.  Feel free to hit me up with any questions to fill in any holes I may of missed.

OK, here it is, a blog. I've finally decided to give it a whirl and start putting my concepts to the virtual paper we call the web.  I'm .NET programmer at heart, but of course, like all programmers, I dabble in a lot of different things.   My guess is that eventually I'll post a few helpful tidbits on this blog that might help another programmer through a tuff time.  Or even better, I'll post how to do something in some horrendous manner, and it'll bring all the village programmers out with their torch and pitchfork. 

I'd like to say I'll do my best not to delete posts or comments, but who knows.  I'm guessing that it'll take awhile for anyone to even see this site. A bit more info about myself, I've been programming for a questionable amount of years.  Some of the things I've done, do and will post on are:

• SSRS, SSAS, SSMS, SSIS, and any other things that make you sound like a snake.
• Sharepoint 2007...a more recent venture.  Wish I could say it was 2010
• Silverlight since the 2.0 days.. 1.0 didn't win my attention.
• Windows Phone 7, nothing on the market yet, but soonish.
• XNA, fun stuff.
• C# and VB.Net I prefer C# though and most of my code will be in C#.
• WCF... get your bindings warmed up.
• WPF
• Expression Blend, Sketchflow, Web, Design, Encoder...and bears oh my.
• Office 2007,2010
• Ancient technologies like 1.1, VB6.. I'll avoid these like the plague but they are none the less a part of the arsenal.

I'm sure I'll chat about other things I do, eventually.  I consider myself to be long winded at times.  Fun Fun.  So if your reading this, welcome to the site, I probably sent you the link myself.