MDX to get last member of the dimension attribute

You may use ClosingPeriod function to do that:

ClosingPeriod
(
[Dim Date].[Date Hierarchy].[Month]
,[Dim Date].[Date Hierarchy].CurrentMember
)

will give you the closing month based on the CurrentMember

Let’s say we have data up to May 2011 date, the above function will return MAY for 2011 and DECEMBER for the prior years.

If you are looking for last non empty member, you may use the following:

Tail
(
NonEmpty
(
[Dim Date].[Date Hierarchy].[Date].MEMBERS
,{[Measures].[measureX]}
)
,1
).Item(0)

Posted in MDX, SSAS | Leave a comment

How to convert json string to an object (C#)?

 

Make sure you reference:

using System.Web.Script.Serialization;

Then it is very simple to convert a json string to an object:

string value = “some json string”; // comes from the client side

JavaScriptSerializer jsSerializer = new JavaScriptSerializer();
jsSerializer.MaxJsonLength = 2147483647;
SomeObject obj = jsSerializer.Deserialize<SomeObject>(value);

Usually I have the same object on the client side, I make sure I have all necessary properties on this object based on my requirements. Then I fill the object on the client side use the json2.js library to convert the object to json string on the client side and pass it to the server. I then use this object back and forth for my purpose, it can have commands, data, whatever you meaning you give to the string. One gotcha is it does not work with complex data types easily so be careful.

 

 

Posted in C#, Javascript, JSON | Leave a comment

Javascript regular expression to validate an email address

Using Regular Expressions is probably the best way. Here’s an example:

function validateEmail(email)
{
var re = /^(([^<>()[\]\\.,;:\s@\"]+(\.[^<>()[\]\\.,;:\s@\"]+)*)|(\
".+\"))@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\])|(([a-zA
-Z\-0-9]+\.)+[a-zA-Z]{2,}))$/
return email.match(re)
}

But keep in mind that one should not rely only upon JavaScript validation. JavaScript can easily be disabled. This should be validated on the server side as well.

 

Posted in Javascript | 1 Comment

Given a string of JSON data, how can you safely turn that string into a JavaScript object?

You may use do this unsafely:

var obj = eval("(" + json + ')');

however that leaves us vulnerable to the json string containing other code, which it seems very dangerous to simply eval.

One of the best ways to do that is using JQuery:

jQuery.parseJSON( jsonString );

 

Posted in Javascript, JQuery, JSON | Tagged , , | Leave a comment

Speed up MDX query preparation in Reporting Services by specifying default parameter value

Speed up MDX query preparation in Reporting Services by specifying default parameter value. Read more…

Posted in MDX, SSAS | Leave a comment

How to get comma separated list of members for Dimension Attribute (MDX)?

WITH SET [ABC] AS [CUSTOMER].[CUSTOMER GEOGRAPHY].[COUNTRY].MEMBERS
MEMBER [MEASURES].[TEST] AS GENERATE([ABC], [ABC].CURRENTMEMBER.NAME, “,”)
SELECT [MEASURES].[TEST]
ON 0
FROM [ADVENTURE WORKS]

Posted in MDX, SSAS | 1 Comment

Calculated Member to get Last 12 Month’s Revenue (MDX) ?

Assuming you have a time dimension with hieararchy Year->Quarter->Month [Time H], the following will give you last 12 month’s revenue:

Sum
(
ClosingPeriod
(
[Time H].[Month]
,[Time H].[All]
).Lag(12)
:
ClosingPeriod
(
[Time H].[Month]
,[Time H].[All]
)
,[Measures].[Revenue]
)

Posted in MDX, SSAS | 1 Comment

How do I find bottom 10 customers with lowest revenue (MDX)?

It is very common to write MDX queries to get top or bottom items based on revenue or some other metric. Here is an example of BOTTOMCOUNT function:

SELECT { [Measures].[Revenue] } ON COLUMNS ,
BOTTOMCOUNT(NONEMPTY([Customer].[All Customers] ) , ( [Measures].[Revenue] ) ), 10, ( [Measures].[Internet Sales Amount] ) ) ON ROWS
FROM [MY CUBE];

Posted in MDX, SSAS | Leave a comment

Web 2.0 Colors for your web site

Here are my favorite Web 2.0 colors that I use with web projects…

Neutrals

 

Shiny silver [#EEEEEE]

 

Reddit white [#FFFFFF]

 

Magnolia Mag.nolia [#F9F7ED]

 

Interactive action yellow [#FFFF88]

 

Qoop Mint [#CDEB8B]

 

Gmail blue [#C3D9FF]

 

Shadows Grey [#36393D]

Bold

 

Mozilla Red [#FF1A00]

 

Rollyo Red [#CC0000]

 

RSS Orange [#FF7400]

 

Techcrunch green [#008C00]

 

Newsvine Green [#006E2E]

 

Flickr Pink [#FF0084]

Muted

 

Ruby on Rails Red [#B02B2C]

 

Etsy Vermillion [#D15600]

 

43 Things Gold [#C79810]

 

Writely Olive [#73880A]

 

Basecamp Green [#6BBA70]

 

Mozilla Blue [#3F4C6B]

 

Digg Blue [#356AA0]

 

Last.fm Crimson [#D01F3C]

Posted in Web Design | Leave a comment

How to clear Analysis Services (SSAS) database cache?

It is a common task to clear the cache to see real performance of your MDX queries.

To clear a cache you should use XMLA command ClearCache. You can choose to clear cache for database, cube or measure group. Note: you should specify object IDs and not names. In many cases this might not be the same. To see ID of the object you can open database in the BIDS, then select object (database, cube or measure group) and check value for property ID.

Example of command to clear cache for database:

<ClearCache xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”>
<Object>
<DatabaseID>BI Cubes</DatabaseID>
</Object>
</ClearCache>

Example of command to clear cache for cube:

<ClearCache xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”>
<Object>
<DatabaseID>BI Cubes</DatabaseID>
<CubeID>Super</CubeID>
</Object>
</ClearCache>

 

Posted in MDX, SSAS, XMLA | Leave a comment