Page 149 - DCAP606_BUSINESS_INTELLIGENCE

Business Intelligence

Notes [Measures].[Tax] } ON 0,
{ [Date].[Fiscal].[Fiscal Year].&[2010],
[Date].[Fiscal].[Fiscal Year].&[2011] } ON 1
FROM [Adventure Works]
WHERE ( [Sales Territory].[North] )

Self Assessment

State whether the following statements are true or false:
3. An MDX query is different from an MDX expression.
4. Learning MDX will permit you to take advantage of some of the more advanced features
of Analysis Services to create precisely the dataset you need.
5. The MDX SELECT statement supports optional syntax.

10.3 Excel with Analysis Services

You can use Analysis Services as a data source for the Office Excel 2007 PivotTable and PivotChart
characteristics. The Excel 2007 PivotTable characteristic permits you to create reports and crosstab
reports that will let you pivot, filter, add and remove dimensions, drill down, drill up, and
drillthrough data. You can use the Excel 2007 PivotChart feature to create powerful data
visualizations. Excel 2007 presents various features you can use to format and analyse data with
PivotTable:
Improve the appearance of your report by hiding field headers and using the expand/
collapse buttons.
View empty rows and columns.
Execute Analysis Services actions.

Sort and filter data.
Display member properties.
Display KPIs.
View and modify data sources.
Analysis Services provides dimensional data that is well-suited for data exploration in PivotTables
and Power View reports. You can get Analysis Services data from:
OLAP cubes on an Analysis Services multidimensional server.

Tabular models on an Analysis Services tabular server.
Excel 2013 workbooks on SharePoint 2013, if the workbook contains a data model.
PowerPivot workbooks on SharePoint 2010.

10.3.1 Connecting Excel Client to Analysis Services Environment

From within Excel, select the Analysis Services drop down from the Data tab -> From Other
Sources drop down, and then walk through the data connection wizard to identify location,
cube, and credentials.

144 LOVELY PROFESSIONAL UNIVERSITY