Important Calculated members

Date Range- This is a good one

WITH SET [MyRange] AS
'{[Time].[2009].&[5].[22].[26] : [Time].[2009].&[6].[26].[24]}'
MEMBER [Time].[Range] AS 'Aggregate([MyRange],[Measures].CurrentMember)'

select {[Measures].[Sends],[Measures].[Views], [Measures].[Clicks]} on columns,
{[Zone Id].&[142]}
on rows
from [All Communication Analysis] where ([Time].[Range])


This is an OR query. I got this query from HERE

SELECT [Measures].[Internet Sales Amount] ON 0
FROM [Adventure Works]
WHERE(
{([Date].[Calendar Year].&[2003],[Customer].[Country].[All Customers])
,
([Date].[Calendar Year].[All Periods],[Customer].[Country].&[United States])})



WITH SET [Top5Camps] AS ' TopCount({Descendants( [Zone Id].&[14],3)},200,([Measures].[Sends])) '

select {[Measures].[Sends],[Measures].[Views],[Measures].[Clicks],[Measures].[Clicks Text],[Measures].[Clicks Html],[Measures].[Bounces],[Measures].[Soft Bounces],[Measures].[Hard Bounces],[Measures].[Cliks To Buy],[Measures].[Url Count]} on Columns,

[Top5Camps] DIMENSION PROPERTIES [Zone].[uniq views],[Zone].[uniq soft bounces], [Zone].[uniq hard bounces],[Zone].[uniq clicks html], [Zone].[uniq clicks text],[Zone].[uniq clicks],[Zone].[uniq bounces], [Zone].[Campaign Launch Date],[Zone].[Url Count], MEMBER_CAPTION, MEMBER_KEY on rows

from [Communication]

--Some of these below use custom code in reporting services. The queries are formed in visual basic. Maybe someday I'll take the time to make these real queries.

="select {[RunningTotalSubs]} on Columns, CrossJoin(TopCount( { [DMA].children}, 5000, ([RunningTotalSubs])),

{[Time].[2004].&[1].[1].[1], [Time].[2005].&[1].[1].[1],[Time]}) on rows


from [consumers] where ( [Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "])"

WITH

MEMBER [Measures].[Variant Percentage] AS
'(( [Time].[2004].&[1].[1].[1], [RunningTotalSubs] ) -( [Time].[2005].&[1].[1].[1], [RunningTotalSubs] ))/( [Time].[All Time], [RunningTotalSubs] ) '

select {[RunningTotalSubs],[Measures].[Variant Percentage] } on Columns, CrossJoin(TopCount( { [DMA].children}, 5000, ([RunningTotalSubs])),

{[Time].[2004].&[1].[1].[1], [Time].[2005].&[1].[1].[1],[Time]}) on rows


from [consumers] where ( [Zone Id].&[14])

WITH SET [Top5Camps] AS

' Descendants([Zone Id].&[14].&[74],2) '

SELECT {[Measures].[Sends],[Measures].[S Sends],[Measures].[Views],[Measures].[Uniq Views],[Measures].[Clicks],[Measures].[Uniq Clicks],[Measures].[Clicks Text],[Measures].[Uniq Clicks Text],[Measures].[Clicks Html],[Measures].[Uniq Clicks Html],[Measures].[Bounces],[Measures].[Uniq Bounces],[Measures].[Soft Bounces],[Measures].[Hard Bounces],[Measures].[Cliks To Buy],[Measures].[Url Count],[Measures].[F2f Count]}

="select { { ([Age Group].[All Age Group], [Gender].[All Gender]) },{ ([Age Group].[All Age Group], [Gender].[Male]) },{ ([Age Group].[All Age Group], [Gender].[Female]) },{ ([Age Group].[All Age Group], [Gender].[Unknown]) },{ ([Age Group].[13 and 17], [Gender].[All Gender]) },{ ([Age Group].[18 and 24], [Gender].[All Gender]) },{ ([Age Group].[25 and 34], [Gender].[All Gender]) } ,{ ([Age Group].[35 and 44], [Gender].[All Gender]) },{ ([Age Group].[45 and 54], [Gender].[All Gender]) },{ ([Age Group].[over 55], [Gender].[All Gender]) },{ ([Age Group].[Unknown], [Gender].[All Gender]) }

} on Columns, NON EMPTY { TopCount( { [Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "].children}, 10, ([RunningTotalSubs])) } on Rows from [consumers] where ("&

iif(Parameters!dt.Value="d",Code.getCubeDate(Parameters!p1.Value, Parameters!dt.Value),"[Time]") &",[RunningTotalSubs])"

="select { { ([Age Group].[All Age Group], [Gender].[All Gender]) },{ ([Age Group].[All Age Group], [Gender].[Male]) },{ ([Age Group].[All Age Group], [Gender].[Female]) },{ ([Age Group].[All Age Group], [Gender].[Unknown]) },{ ([Age Group].[13 and 17], [Gender].[All Gender]) },{ ([Age Group].[18 and 24], [Gender].[All Gender]) },{ ([Age Group].[25 and 34], [Gender].[All Gender]) } ,{ ([Age Group].[35 and 44], [Gender].[All Gender]) },{ ([Age Group].[45 and 54], [Gender].[All Gender]) },{ ([Age Group].[over 55], [Gender].[All Gender]) },{ ([Age Group].[Unknown], [Gender].[All Gender]) }

} on Columns, NON EMPTY { TopCount( { [Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "].children}, 10, ([RunningTotalSubs])) } DIMENSION PROPERTIES [Zone Id].[Campaign Type Id].[Key], [Zone Id].[Campaign Type Id].[Name] on Rows from [consumers] where ("&

iif(Parameters!dt.Value="d",Code.getCubeDate(Parameters!p1.Value, Parameters!dt.Value),"[Time]") &",[RunningTotalSubs])"

ON COLUMNS,

Order([Top5Camps], [Top5Camps].currentmember.properties("Campaign Launch Date") ,desc) DIMENSION PROPERTIES [Zone].[Campaign Launch Date], [Zone].[Url Count], MEMBER_CAPTION, MEMBER_KEY

ON ROWS
from [All communication analysis]

WITH SET [Top5Camps] AS

' Descendants([Zone Id].&[14].&[74],2) '

SELECT {[Measures].[Sends],[Measures].[S Sends],[Measures].[Views],[Measures].[Uniq Views],[Measures].[Clicks],[Measures].[Uniq Clicks],[Measures].[Clicks Text],[Measures].[Uniq Clicks Text],[Measures].[Clicks Html],[Measures].[Uniq Clicks Html],[Measures].[Bounces],[Measures].[Uniq Bounces],[Measures].[Soft Bounces],[Measures].[Hard Bounces],[Measures].[Cliks To Buy],[Measures].[Url Count],[Measures].[F2f Count]}

ON COLUMNS, [Top5Camps] DIMENSION PROPERTIES [Zone].[Campaign Launch Date], [Zone].[Url Count], MEMBER_CAPTION, MEMBER_KEY

ON ROWS
from [All communication analysis]

="WITH SET [Top5Camps] AS ' STRTOSET(IIF(ISERROR(STRTOVALUE(""[Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "].&[" & Code.EDecrypt(Parameters!PId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "].&[" & Code.EDecrypt(Parameters!CTId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "]"")), ""[Zone Id].&[3].&[45].&[4311].Children"", ""[Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "].&[" & Code.EDecrypt(Parameters!PId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "].&[" & Code.EDecrypt(Parameters!CTId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "].Children"")) '

="select {[Measures].[Sends],[Measures].[S Sends],[Measures].[Views],[Measures].[Uniq Views],[Measures].[Clicks],[Measures].[Uniq Clicks],[Measures].[Clicks Text],[Measures].[Uniq Clicks Text],[Measures].[Clicks Html],[Measures].[Uniq Clicks Html],[Measures].[Bounces],[Measures].[Uniq Bounces],[Measures].[Soft Bounces],[Measures].[Hard Bounces],[Measures].[Cliks To Buy],[Measures].[Url Count],[Measures].[F2f Count]} on Columns,

[Top5Camps] on rows

from [All Communication Analysis]"

="WITH SET [Top5Camps] AS ' STRTOSET(IIF(ISERROR(STRTOVALUE(""[Zone Id].), ""[Zone Id].&[3].&[45].&[4311].Children"", ""[Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "].&[" & Code.EDecrypt(Parameters!PId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "].&[" & Code.EDecrypt(Parameters!CTId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "].Children"")) '

="select {[Measures].[Sends],[Measures].[S Sends],[Measures].[Views],[Measures].[Uniq Views],[Measures].[Clicks],[Measures].[Uniq Clicks],[Measures].[Clicks Text],[Measures].[Uniq Clicks Text],[Measures].[Clicks Html],[Measures].[Uniq Clicks Html],[Measures].[Bounces],[Measures].[Uniq Bounces],[Measures].[Soft Bounces],[Measures].[Hard Bounces],[Measures].[Cliks To Buy],[Measures].[Url Count],[Measures].[F2f Count]} on Columns,

[Top5Camps] on rows

from [All Communication Analysis]"

="WITH SET Top5Camps AS ' Descendants( [Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "], 3)'

SELECT {[Measures].[Sends],[Measures].[S Sends],[Measures].[Views],[Measures].[Uniq Views],[Measures].[Clicks],[Measures].[Uniq Clicks],[Measures].[Clicks Text],[Measures].[Uniq Clicks Text],[Measures].[Clicks Html],[Measures].[Uniq Clicks Html],[Measures].[Bounces],[Measures].[Uniq Bounces],[Measures].[Soft Bounces],[Measures].[Hard Bounces],[Measures].[Cliks To Buy],[Measures].[Url Count],[Measures].[F2f Count]}

ON COLUMNS, [Top5Camps] DIMENSION PROPERTIES [Zone].[Campaign Launch Date], MEMBER_CAPTION, MEMBER_KEY

ON ROWS
from [All communication analysis]"

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

="WITH SET Top5Camps AS ' Descendants( [Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "].&[" & Code.EDecrypt(Parameters!PId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "].&[" & Code.EDecrypt(Parameters!CTId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "], 1)'

SELECT {[Measures].[Sends],[Measures].[S Sends],[Measures].[Views],[Measures].[Uniq Views],[Measures].[Clicks],[Measures].[Uniq Clicks],[Measures].[Clicks Text],[Measures].[Uniq Clicks Text],[Measures].[Clicks Html],[Measures].[Uniq Clicks Html],[Measures].[Bounces],[Measures].[Uniq Bounces],[Measures].[Soft Bounces],[Measures].[Hard Bounces],[Measures].[Cliks To Buy],[Measures].[Url Count],[Measures].[F2f Count]}

ON COLUMNS, [Top5Camps] DIMENSION PROPERTIES [Zone].[Campaign Launch Date], [Zone].[Url Count], MEMBER_CAPTION, MEMBER_KEY

ON ROWS
from [communication ]"



One link I came accross when using deminsion properties. Try not to make queries with properties because they are slow. But if you have to:

http://www.sqlservercentral.com/blogs/mysqllearnings/archive/2009/04/15/key-column-of-a-dimension-in-ssas.aspx

Each product has a weight associated with it. The requirement is to filter all the products whose weight is 5.

To achieve this, weight should be the member property of the product. This time, it cannot be made as a key column. But, again instead of using ".properties" function, functions like "EXISTS" can be used.

A general way of writing this would be

Filter ([Prod Dim].[Prod Hier].[SKU Lvl].members,[Prod Dim].[Prod Hier].[SKU Lvl].currentmember.properties("Weight") = 5)

In this case, it has to filter each member by getting the property of each member and checking whether the weight is less than 5. Instead of using the ".properties" function, it can be written using "EXISTS" function.

Exists ([Prod Dim].[Prod Hier].[SKU Lvl].members,filter([Prod Dim].[Weight].[Weight].members,[Prod Dim].[Weight].currentmember is [Prod Dim].[Weight].[5]))

Also, it is better to use the function "IS" instead of "=" because IS function eliminates the conversion of the given string to a member.

Because of doing this, there was a considerable improvement in the query performance. These were the scenarios where I felt natural key was useful when specified as the key column.

MDX Calculations

WITH
MEMBER [Measures].[SumOfAG] AS
'( Sum({[Time]}, [Measures].[RunningTotalSubs]))'

MEMBER [Measures].[SuperSum] AS
' (Sum({[Age Group].[13 and 17],[Age Group].[18 and 24],[Age Group].[25 and 34],[Age Group].[35 and 44],[Age Group].[45 and 54],[Age Group].[over 55],[Age Group].[less then 13],[Age Group].[UNKNOWN] },[Measures].[SumOfAG] ))'

MEMBER [Measures].[GMale] AS
'( Sum({([Time],[Gender].[Male])}, [Measures].[RunningTotalSubs]))'
MEMBER [Measures].[GMalePer] AS
' ([Measures].[GMale] / [Measures].[SuperSum])'
MEMBER [Measures].[GFemale] AS
'( Sum({([Time],[Gender].[Female])}, [Measures].[RunningTotalSubs]))'

MEMBER [Measures].[GFemalePer] AS
' ([Measures].[GFemale] / [Measures].[SuperSum])'

MEMBER [Measures].[GUnknown] AS
'( Sum({([Time],[Gender].[Unknown])}, [Measures].[RunningTotalSubs]))'

MEMBER [Measures].[GUnknownPer] AS
' ([Measures].[GUnknown] / [Measures].[SuperSum])'

select {[Measures].[SumOfAG], [Measures].[GMale], [Measures].[GMalePer], [Measures].[GFemale], [Measures].[GFemalePer], [Measures].[GUnknown], [Measures].[GUnknownPer] }
on Columns, { ([Age Group].children) } on Rows from [consumers] where ([Zone Id].&[501])

WITH
MEMBER [Measures].[p1] AS '( [Time].[2008].&[7].[31].[30], [Subs Count] ) '
MEMBER [Measures].[p2] AS '( [Time].[2008].&[6].[27].[30], [Net Subs] ) '
MEMBER [Measures].[Variant Percentage] AS '(( [Time].[2008].&[7].[31].[30], [Net Subs] ) -( [Time].[2008].&[6].[27].[30], [Net Subs] ))/( [Time].[2008].&[6].[27].[30], [Net Subs] ) '
MEMBER [Measures].[SubsDiff] AS '(( [Time].[2008].&[7].[31].[30], [Net Subs] ) -( [Time].[2008].&[6].[27].[30], [Net Subs] )) '

select {[Subs Count], [Net Subs],[RunningTotalSubs],[Measures].[p1], [Measures].[p2], [Measures].[SubsDiff], [Measures].[Variant Percentage] } on columns,

TopCount(NonEmptyCrossJoin({Descendants([Source].Currentmember, 3)},{Descendants([Zone Id].&[1178],2)}) ,150, ([Net Subs])) DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_KEY on rows

FROM [consumers]

BIusiness Intelligence over Microsoft Office Excel

Microsoft Office Excel
As one of the most widely used Microsoft Office products, the Excel spreadsheet
program is designed to organize, analyze, and visualize data. Excel is
one of the most powerful desktop applications in the Microsoft BI arsenal.
An analysis tool for everyone
Excel is such a popular data tool that most of the client organizations I visit
use it to run some critical portion of their business. One good reason is that
Excel can be installed on a local computer with no need for administrators
and servers.
The Data Mining Add-in
Microsoft creates Add-ins (new sets of capabilities) as a way to expand what
its products can do; the Data Mining Add-in allows the Excel program running
on your local computer to serve as a data-mining resource for SQL Server
Analysis Services. You can run SSAS Data Mining algorithms using data that
resides in Excel cells to yield important information about your business.
Microsoft Office Visio
The general idea behind Visio is to create flow charts — and to publish these
documents to the Web as interactive diagrams with drill-down capabilities
(users can click their way down to specific data). Microsoft offers a Data
Mining Add-In for Visio that allows users to create interactive documents
with real inlaid data. For example, a decision tree can be published to the
Web with actual business data built in. When users go to the Web site containing
that document, they can click a decision to view its results.

Data mining

Computers can be programmed to sort through enormous amounts of data
looking for patterns. It’s an exciting new frontier that goes by many different
names — in business, the most common ones are data mining, predictive
analytics, and machine learning — but this book sticks to “data mining”.
The Microsoft data-mining algorithms are part of SQL Server Analysis
Services, but you don’t have to be a super computer ninja to access and use
them. Microsoft offers a free Excel Data Mining Add-In that transforms Excel
into a simple, intuitive client program for the SSAS data-mining algorithms

Analyzing data

As you can imagine, the amount of data contained in a modern business is
enormous. If the data were very small, you could simply use Microsoft Excel
and perform all of the ad-hoc analysis you need with a Pivot Table. However,
when the rows of data reach into the billions, Excel is not capable of handling
the analysis on its own. For these massive databases, a concept called OnLine
Analytical Process (OLAP) is required. Microsoft’s implementation of OLAP is
called SQL Server Analysis Services (SSAS), which I cover in detail in Chapter 8.
If you’ve used Excel Pivot Tables before, think of OLAP as essentially a massive
Pivot Table with hundreds of possible pivot points and billions of rows
of data. A Pivot Table allows you to re-order and sum your data based on different
criteria. For example, you may want to see your sales broken down by
region, product, and sales rep one minute and then quickly re-order the groupings
to include product category, state, and store.
In Excel 2010 there is a new featured called PowerPivot that brings OLAP to
your desktop. PowerPivot allows you to pull in millions of rows of data and
work with it just like you would a smaller set of data. After you get your Excel
sheet how you want it, you can upload it to a SharePoint 2010 site and share
it with the rest of your organization.
With PowerPivot you are building your own Cubes right on your desktop using
Excel. If you use PowerPivot, you can brag to your friends and family that you
are an OLAP developer. Just don’t tell them you are simply using Excel and
Microsoft did some magic under the covers.
When you need a predefined and structured Cube that is already built for
you, then you turn to your IT department.

Integrating data from many sources

The many different systems and processes that make up an organization
create data in all shapes and forms. This data usually ends up stored in the
individual systems that generated it — but without any standard format.
Fortunately, SQL Server has a component — SQL Server Integration Services
(SSIS) — that can connect to these many different data sources and pull
the data back into the central data warehouse. As the data moves from the
source systems to the Data Warehouse, SSIS can also transform it into a standard
useful format. The whole process is known as Extract, Transform, and
Load (ETL).

Reporting on data

When you have a Data Warehouse, you likely don’t want to look at rows
and rows of data; instead, you want to visualize the data and give it meaning.
Building reports that answer a particular question (or set of questions)
means taking raw data and turning it into information that can be used to
make intelligent business decisions. SQL Server Reporting Services (SSRS), a component of SQL Server — builds reports by doing that bit of magic.
SSRS has features that can make your reports as fancy as you like — gauges,
charts, graphs, aggregates, and many other snazzy ways to visualize the data.

Data warehousing and data marts

Although computer systems help solve many problems in business, they use
so many different kinds of programs that they can’t always communicate
easily with each other. A tremendous number of systems make up a modern
organization — payroll, accounting, expenses, time, inventory, sales, customer
relations, software licensing, and so on. Many of these systems have
their own databases and ways of storing data. Combining data from the
tangle of systems — let alone doing something useful with the combined
data — becomes extremely difficult.
Business intelligence creates a “big picture” by storing and organizing data
from many disparate systems in one usable format. The idea is to make the
data readily accessible for reporting, analysis, and planning. A data warehouse
is a central database created for just that purpose: making the data
from all those sources useful and accessible for the organization. The idea is
to give decision-makers the information they need for making critical business
decisions.
A data mart is a more specialized tool with a similar purpose; it’s a functional
database that pulls particular information out of the overall Data Warehouse
(or even directly from source systems depending on who you ask) to answer
specific queries. For example, a manufacturing location may need to compile
some specialized data unique to the process used to make a particular product.
The overall data warehouse is too big and complex do that job (or to modify
effectively to handle it), so a smaller version — in BI lingo, a data mart — can be
created for this one manufacturing location.
The Microsoft SQL Server Database Engine manages not only data warehouses,
but also data marts — and both types of data storage can become
massive. Fortunately, SQL Server addresses this problem by storing one
database across a cluster of many different servers. This approach accommodates
the enterprise as it grows in scale.