To Act is to Lead

“The smallest of actions is always better than the noblest in intentions “ Leadership is a lot more than just dreaming up big. It’s about acting to them.
What separates the ordinary ones from the great ones is a simple fact: the best of best execute brilliantly around their most vital priorities. In a leadership presentation, you can use concept BRBO: best resources on biggest opportunities.
Leadership is, in so many ways, about getting good stuff done, not started, not in process, done.
So Today makes decision that will revolutionize your career/Life/Self: never leave the site of an opportunity without doing something to seize it. And be less about talk – and more about DO.

multidimensional-expression-part-4

How do I group dimension members dynamically in MDX?

You can create calculated members for dimension and then use them in the query. Example below will create 3 calculated members based on filter condition:


With MEMBER [Product].[Category].[Result1] AS 'Aggregate(Filter([Product].[Category].[All].Children, [Product].[Category].currentmember.Properties("Key") > "3"))'
MEMBER [Product].[Category].[Result2] AS 'Aggregate(Filter([Product].[Category].[All].Children, [Product].[Category].currentmember.Properties("Key") = "3"))'
MEMBER [Product].[Category].[Result3] AS 'Aggregate(Filter([Product].[Category].[All].Children, [Product].[Category].currentmember.Properties("Key") > "3"))'
SELECT NON EMPTY {[MEasures].[Order Count]} ON COLUMNS
,{[Product].[Category].[Result1],[Product].[Category].[Result2],[Product].[Category].[Result3]} ON ROWS
FROM [Adventure Works]


Result will be:
Order Count
Result1 19,523
Result2 9,871
Result3 19,523

How you can add another dimension as a row on your sample code?
Just add new members for new dimension and add same members in you row details.

How do you write MDX query that returns measure ratio to parent value?
Below is example on how is ratio calculated for measure [Order Count] using Date dimension. Using parent function, your MDX is in dependent on level that you are querying data on. In example below, if you query data at year level, ratio will be calculated to level [All]:


WITH MEMBER [Measures].[Order Count Ratio To Parent] AS
IIF( ([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent) = 0
, NULL
, [Measures].[Order Count]
/
([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent)
)
, FORMAT_STRING = "Percent"

SELECT {[Measures].[Order Count], [Measures].[Order Count Ratio To Parent]} ON 0
, {DESCENDANTS([Date].[Calendar].[All Periods], 1), [Date].[Calendar].[All Periods]
} ON 1
FROM [Adventure Works]

Result will be like:

Order Count Order Count Ratio To Parent
CY 2001 1,379 4.38%
CY 2002 3,692 11.74%
CY 2003 12,440 39.55%
CY 2004 13,944 44.33%
CY 2006 (null) (null)
All Periods 31,455 (null)

If you query data at month level, ratio will be calculated comparing to level quarter:

WITH MEMBER [Measures].[Order Count Ratio To Parent] AS
IIF( ([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent) = 0
, NULL
, [Measures].[Order Count]
/
([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent)
)
, FORMAT_STRING = "Percent"

SELECT {[Measures].[Order Count]
, [Measures].[Order Count Ratio To Parent]} ON 0
, {DESCENDANTS([Date].[Calendar].[Calendar Quarter].&[2003]&[4], 1)
, [Date].[Calendar].[Calendar Quarter].&[2003]&[4]
} ON 1
FROM [Adventure Works]


Result will be like:

Order Count Order Count Ratio To Parent
October 2003 1,779 29.95%
November 2003 1,888 31.79%
December 2003 2,272 38.26%
Q4 CY 2003 5,939 58.87%

Multidimensional Expression Part 3

MDX query to get sales by product line for specific period plus number of months with nonempty sales?

Solution:
You can use COUNT () function with Exclude Empty option. For count function you specify set that is cross join of Date members at the month level and measure that you are interested in.

WITH Member [Measures].[Months With Above Zero Sales] AS
COUNT(
DESCENDANTS({[Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]}
, [Date].[Calendar].[Month]) * [Measures].[Sales Amount]
, ExcludeEmpty
)
SELECT {[Measures].[Sales Amount], [Measures].[Months With Above Zero Sales]} ON 0
, [Product].[Product Model Lines].[Product Line].Members on 1
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004])

Result will be:

Sales Amount Months With Above Zero Sales
Accessory $1,987,396.37 19
Components $454,644.34 18
Mountain $24,430,307.51 19
Road $24,919,506.74 19
Touring $16,010,837.10 13

Same you can apply for Years:

WITH Member [Measures].[Years With Above Zero Sales] AS
COUNT(
DESCENDANTS({[Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]}
, [Date].[Calendar].[Years]) * [Measures].[Sales Amount]
, ExcludeEmpty
)
SELECT {[Measures].[Sales Amount], [Measures].[Years With Above Zero Sales]} ON 0
, [Product].[Product Model Lines].[Product Line].Members on 1
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004])


Result will be:
Sales Amount Years With Above Zero Sales
Accessory $1,987,396.37 2
Components $454,644.34 2
Mountain $24,430,307.51 2
Road $24,919,506.74 2
Touring $16,010,837.10 2

You can change Measures:

WITH Member [Measures].[Months With Above Zero Sales] AS
COUNT(
DESCENDANTS({[Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]}
, [Date].[Calendar].[Month]) * [Measures].[Internet Sales Amount]
, ExcludeEmpty
)
SELECT {[Measures].[Internet Sales Amount], [Measures].[Months With Above Zero Sales]} ON 0
, [Product].[Product Model Lines].[Product Line].Members on 1
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004])

Multidimensional Expression (MDX) – part 2

Many developer start MDX query learning with MDX tutorial, learning tuples, namedsets, axis, select query syntax etc….. But still after that, many queries remain to resolve and need hands-on experience of some real time project when one really starts applying functions, tweaking or nesting queries and parameters.
In this session will start with some complex query with adventure works database.
Hope this helps all to resolve last movement MDX requirements when developers don’t have time to learn all the syntax, structure and theory:

How to find bottom 10 customers with lowest sales in 2003 that were not null?
Simple using BOTTOMCOUNT will return customers with null sales and we can use NONEMPTY or FIlTER to remove null data.

SYNTAX:

SELECT {[Measures].[Internet Sales Amount] } ON COLUMNS,
BOTTOMCOUNT(NONEMPTY(DESCENDANTS([Customer].[Customer Geography].[All Customers]
, [Customer].[Customer Geography].[Customer])
, ([Measures].[Internet Sales Amount]))
, 10
,( [Measures].[Internet Sales Amount])
) ON ROWS
FROM [Adventure Works]
WHERE ([Date].[Calendar].Calendar Year].&[2003])

Result will be:
Internet Sales Amount
Ariana Peterson $2.29
Olivia Brown $2.29
Abigail L. Bennett $2.29
Natalie L. Bryant $2.29
Madison D. Lee $2.29
Lauren Miller $2.29
Stephanie B. Murphy $2.29
Cameron L. Rodriguez $2.29
Melanie Peterson $2.29
Alfredo Romero $2.29

What pain points will the right business intelligence application solve for any organization?

It might be easy for McDinald’s to create a hamburger that tastes the same no matter what country ir is served in, but it is not easy for enterprise to apply the same business rules, accounting principles and financial reporting practices to operations in different countries. To paint an accurate financial picture, global enterprise have to put in place systems that compile financial date from geographically dispersed divisions using a myriad of technological applications and a variety of accounting practices, business rules and cultural expectations.

The right BI application should provide organization with:

Integration and Financial Accuracy: It is a key to find a single, integrated application that will provide your organization with one central repository for enterprise-wide financial and non-financial data. This single application and information repository should result in enterprise-wide financial integrity, elimination of redundancy, consistent application of business rules and f firm knowledge that business decisions are being made from a basis of financial truth.

Fast and Accurate Reporting: Fast and accurate report will give you and your executives more time to analyze data. A more thoughtful analysis of the bottom line allows you monitor results against set objectives and develop more meaningful, long term strategic plans that are actionable. It also enables you to peer into the depth of your complex organization and gain an insightful view of its financial reality.

BI is Growing UP

Organizations that use Bi show increased level of maturity- up 5.7 percent from 2009 but there’s still far to go, according to the this year’s BI maturity survey from Forrester Research.
For “BI Maturity in The Enterprise: 2010 Update” Forrester surveyed only 173(of course out of thousand) technology professionals familiar with the respective organization’s BI efforts.
Compared with the previous year, 2010 findings show fewer organizations centralizing BI, with 43 percent employing agile BI and BI self –service, and 38 percent using centralization and consolidation. Forrester also found only 35 percent of organizations are employing pervasive Bi, which is defined as enterprise BI applications being available to most strategic and operation decision makers.
BI talent and skill acquisition also leave much room for improvement, with 46 of respondents in this Forrester study expressing dissatisfaction with procedures to hire, train and retain skilled BI staff.
“Traditional BI approaches, processes, technologies and architectures are no longer enough to continue to drive higher level of BI adoption and increase BI maturity in enterprise,” says Forrester Research Analysts. “New approaches such as agile BI and BI self-service by the business users are needed to supplement traditional BI.”