Tag: mdx

Microsoft Analysis Service (OLAP) Front-End

Visual Business Intelligence - Tables and Graphs 1

Option for MSSQL Analysis Service (OLAP) Front-End

1.Microsoft SQL Reporting Service

SQL Server Reporting Services (SSRS) is a server-based report generation software system from Microsoft. It can be used to prepare and deliver a variety of interactive and printed reports. It is administered via a web interface

2.Microsoft PerformancePoint Service

PerformancePoint Services in Microsoft SharePoint Server 2010 is a performance management service that you can use to monitor and analyze your business. By providing flexible, easy-to-use tools for building dashboards, scorecards, reports, and key performance indicators (KPIs), PerformancePoint Services can help everyone across an organization make informed business decisions that align with companywide objectives and strategy. Scorecards, dashboards, and KPIs help drive accountability. Integrated analytics help employees move quickly from monitoring information to analyzing it and, when appropriate, sharing it throughout the organization.

3.Tantalum

4.Analyzer

Analyzer allows even non-technical users to become productive and self-sufficient quickly, performing powerful analysis and building visually compelling dashboards and flexible reports. A wide variety of components, including pivot tables, charts, Key Performance Indicator (KPI) scorecards, dashboards, web pages, Reporting Services® reports, geographic maps, heat maps, and more — lets users create custom reports and dashboards just by dragging and dropping.

5.Visifire

Visifire is a set of data visualization controls – powered by Microsoft®Silverlight™ & WPF. Visifire is a multi-targeting control which can be used in both WPF & Silverlight applications. Use the same API to create charts & gauges in Mobile, Web & Desktop environments. Visifire Silverlight Controls can also be embedded in any webpage as a standalone Silverlight App. Don’t take our word for it! Visit Visifire Gallery or design your own chart or gauge using Chart/Gauge Designer.

6.XlCubed

XLCubed’s product set empowers the business user. Bringing the power and flexibility of OLAP reporting to Excel, XLCubed leverages Excel’s strengths but mitigates the risks. Users have a rich Reporting, Analytics and Dashboard environment in Excel, and the ability to deploy the reports to XLCubed Web Edition with ease

7.Tableau

Flat files, databases, warehouses, or cubes: Access your data wherever it lives. Analyze it. Fast.The reality of business today is that no organization has data in a single place. Heterogeneous environments are the rule; exceptions to it are hard to find. You need the ability to analyze data wherever it lives to get the answers you need. Make sure your business intelligence solution can handle your data, everywhere.

8.Report Portal

Report Portal is an advanced web client reporting solution that lets you build, publish and view the following types of reports

9.ranet-uilibrary-olap

It provides parsing, generating and visualization of MDX language and MDX query results. Moreover it provides data input and Writeback to OLAP cube. It works under .NET and Silverlight.

10. DevExpress  –  Business Intelligence Suite

DevExpress Analytics controls allow you to deliver highly interactive data-centric applications to your end-users with minimal effort. From OLAP data mining with integrated charting to data export and analysis via our award winning gauge and reporting options, DevExpress allows you to deliver your very best and offer your customers a way in which to intuitively analyze data.

11. Viblend

VIBlend DataGridView for WinForms is a top of the line data grid control loaded with hundreds of powerful features. It provides a solid and highly extensive data visualization foundation ideal for a broad spectrum of data driven applications. You can build in-house Business Intelligence applications as easily as loading a set of records from a database.

12.Syncfusion – ASP.NET – For BI Edition

We offer more than 60 AJAX-enabled controls/frameworks with complete documentation and samples for the ASP.NET platform. Our unique wrappers for jQuery UI controls allow you to easily add advanced jQuery UI features to your Web Forms applications.

anything else..??

SSAS: Mastering MDX on SSAS

The Next step on using SQL Server Analysis Service (SSAS) as Business Intelligence (BI) / Warehouse (BW) platform is to master Multi Dimensional Expression (MDX), it’s like Query on T-SQL.

Question, how to master MDX script language on SSAS (SQL Server Analysis Service) ? or where to start ?

Firts thing first, you have to understand MDX Syntax Conventions (MDX),  MDX Statement Reference (MDX) , MDX Operator Reference (MDX) and especially theese MDX Function Reference

  1. Array Functions
  2. Hierarchy Functions
  3. Level Functions
  4. Logical Functions
  5. Member Functions
  6. Numeric Functions
  7. Set Functions
  8. String Functions
  9. Subcube Functions
  10. Tuple Functions

source : msdn

MDX Statement Reference (MDX)

SSAS: MDX Average Script On SSAS 2008

The MDX script on Microsoft Analysis Service (SSAS) 2008 and 2005 are different from SQL 2000. Here’s an example how to calculate average using MDX script on Analysis Service 2008.

SSAS 2008, How to calculate Net Value By Date (NetValue / Date) 

AVG(
Descendants(
[Time].[Calendar].CurrentMember,
[Time].[Calendar].[Date]
),
[Measures].[NetValue]
)

Old one, SSAS 2000, How to calculate Net Value By Date (NetValue / Date) 

AVG(
Descendants(
[Time].CurrentMember, [Date]
),
[Measures].[NetValue]
)

Other source from MSDN
http://msdn.microsoft.com/en-us/library/ms146067.aspx

Multiple IIf Logic Block

IIf function often used in MultiDimensionalExpression (MDX), It’s quiet simple to implement single block of IIf block.

This an example of Multiple IIf logic block.

Example1:

IIf(Measures.[Store Sales] > 50000, "Good",
IIf(Measures.[Store Sales] > 30000, "Average",
IIf(Measures.[Store Sales] > 10000, "Poor",
"No")))

If…Then..Else… in MultiDimensionalExpression

A logical script in MDX to perform an “If..Then.. Else..” block.

IIf(«Logical Expression», «Numeric/String Expression1», «Numeric/String Expression2»)

Example1:The following example returns 0 if Measures.CurrentMember is an empty cell, 1 otherwise:

IIf(IsEmpty(Measures.CurrentMember), 0, 1)

Example2:The following string returns the string “Yes” if Measures.CurrentMember is an empty cell, the string, “No” otherwise:

IIf(IsEmpty(Measures.CurrentMember), "Yes", "No")

Example3:

IIf(
[Scenario].CurrentMember IS [Scenario].[Budget],
     [Measures].[Budget Unit Sold],
     [Measures].[ActualUnit Sold]
)

Example4:Average Sales (Measure) By Time Day (Dimension)

IIf (
([Time].currentmember.level.name = "(All)"   OR
[Time].currentmember.level.name = "Year"     OR
[Time].currentmember.level.name = "Quarter"  OR
[Time].currentmember.level.name = "Month"    OR
[Time].currentmember.level.name = "Day"
),AVG(Descendants(Time.CurrentMember,[Time].[Day]),Measures.[Sales]),0)

Source:

SQL Server 2000 Book Online (F1)

MDX Solution With Microsoft SQL Server Analysis Service (Wiley) (George Spofford)

Relative Contribution to Total Value (Percentage)

There’s a few scenario to calculate a percentage of a measure in OLAP CUBE (Analysis Service 2000). A Percentage is common calculation in MDX , and it can be solved by divide a measure to  total value (Grand Total).

Sample 1:Measures.[Value Contribution to Total]:

(Measures.[Value] / (Measures.[Value], Time.[All Time], Products.[All Products],…)

Sample 2:Measures.[Value Contribution to Total]:

(Measures.[Value] / (Measures.[Value], Products.[All Products],Warehouses.[All Warehouses])

Sample 3:Measures.[Product Contribution to Total Products]:

(Measures.[Value] , Products.CurrentMember) / (Measures.[Value], Products.[All Products])

Source:

MSDN

Multi Dimensional Expression Basic (MDX)

This post is copy from http://gumper.com, a unique website coz it’s only have one item….

The basic script of Multi Dimensional Expression (MDX). It’s very usefull for developer to build some scenarios in a CUBE.

thank you,

hope it’s ok i copied all the page.

MDX Expressions
Function Requires Returns
CurrentMember Dimension Member
PrevMember Member Member
NextMember Member Member
Lag Numeric Expression Member
Lead Numeric Expression Member
ParallelPeriod Level, numeric expression, member Member
PeriodsToDate Level, member Set
YTD Member Set
MTD Member Set
Sum Set, numeric expression Number
 
Navigating the Hierarchy
Function Requires Returns
Parent Member Member
Children Member Set
FirstChild Member Member
LastChild Member Member
Descendants Member, level or member, distance Set
Ancestor Member, level or member, distance Member
Siblings Member Set
FirstSibling Member Member
LastSibling Member Member
Cousin Member, ancestor member Member
 
Data Analysis
Function Requires Returns
Sum Set, numeric expression Number
Count Set Integer
Avg Set, numeric expression Number
Tail Set, count Set
Item Set, index number Member
OpeningPeriod Level, member Member
ClosingPeriod Level, member Member
Max Set, numeric expression Number
Min Set, numeric expression Number
 
Moving Averages
Function Requires Returns
Level Member Level
Members Level or dimension Setr
Iif Condition, what to do if true, what to do if false Number or string
 
Filters
Function Requires Returns
Filter Set, condition Set
IsEmpty Expression Boolean
 
Member Properties
Function Requires Returns
Properties Member name String
Val (VBA Function) String Numeric value
 
Aggregates
Aggregate function Returned value
Sum The sum of the input values
Min The lowest of the input values
Max The highest of the input values
Count The number of input values
Distinct Count The number of unique input values
 
Example MDX Calculations
Sales Growth 
([Time].CurrentMember, [Measures].[Sales])
- ([Time].CurrentMember.PrevMember, [Measures].[Sales])
PP Growth 
([Time].CurrentMember, [Measures].[Sales])
- (ParellelPeriod(Year, 1, [Time].CurrentMember), [Measures].[Sales])
YTD Sales 
sum(ytd([Time].CurrentMember), [Measures].[Sales])
YTD Sales Using PeriodsToDate 
sum(PeriodsToDate([Time].[Year], [Time].CurrentMember), [Measures].[Sales])
Product Percentage 
([Product].CurrentMember, [Measures].[Unit Sales])
/ ([Product].CurrentMember.Parent, [Measures].[Unit Sales]) * 100
Product Total Percentage 
([Product].CurrentMember, [Measures].[Unit Sales])
/ ([Product].[All Products], [Measures].[Unit Sales]) * 100
Average Stock – Brute Force 
Sum(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])
/ Count(Descendants([Time].CurrentMember, [Month]))
Average Stock – Elegant 
Avg(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])
Closing Period – Brute Force 
(Tail(Descendants([Time].CurrentMember, [Month]), 1).Item(0), [Measures].[Quantity])
Closing Period – Elegant 
(ClosingPeriod([Month], [Time].CurrentMember), [Measures].[Quantity])
Max for Period 
Max(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])
Min for Period 
Min(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])
Moving Average 
Avg([Time].CurrentMember.Lag(2):[Time].CurrentMember, [Measures].[Unit Sales])
Products Down 
Count(Filter(Descendants([Product].CurrentMember, [Product Name])
            , ([Time].CurrentMember, [Measures].[Unit Sales])
              < ([Time].CurrentMember.PrevMember, [Measures].[Unit Sales])))
/ 
Count(Descendants([Product].CurrentMember, [Product Name]))
Dynamic Custom Default Member 
Tail(Filter([Time].[Month].Members
     , Not IsEmpty([Time].CurrentMember)), 1).Item(0)
Properties Query 
Filter([Store].[Store Name].Members
       , Val([Store].CurrentMember.Properties("Store Sqft")) < 21000)
Products Down Query 
select   
    {{{[Measures].[Products Down]}   
      * {[Customers].DefaultMember}   
      * {[Product].DefaultMember}}} on columns  
    , {[Time].&[1998].&[Q1].&[1]  
       , [Time].&[1998].&[Q1].&[2]  
       , [Time].&[1998].&[Q1].&[3]  
       , [Time].&[1998].&[Q2].&[4]  
       , [Time].&[1998].&[Q2].&[5]  
       , [Time].&[1998].&[Q2].&[6]  
       , [Time].&[1998].&[Q3].&[7]  
       , [Time].&[1998].&[Q3].&[8]  
       , [Time].&[1998].&[Q3].&[9]  
       , [Time].&[1998].&[Q4].&[10]  
       , [Time].&[1998].&[Q4].&[11]} on rows  
from  
    [Sales_MDX2]
MDX Query 
select
    {[Measures].[Unit Sales]
     , [Measures].[MA]} on columns
    , {Descendants([Time].[Year].&[1997], [Month])} on rows
from
    [Sales_MDX2]