Wednesday, November 24, 2010

Axes in MDX Query

An Axis is a group, or collection of members from one or more dimension, which is constructed as Tuples. The main purpose of axis is used extract or filters the specific part of the cube values.
Characteristics of Axis:
  • Axis number is zero based.
  • zero (0) for the x-axis, 1 for the y-axis, 2 for the z-axis
  • MDX supports up to 128 axes.
  • First 5 axes have names like COLUMNS, ROWS, PAGES, SECTIONS, and CHAPTERS, remaining are accessed thru axis number only like 5, 6, 7, etc.,
  • We access specify the access in MDX Query in following different ways:
  • on Axis(0), on Axis(1) , etc.,
  • on 0, on 1, etc.,
  • on columns, on rows, on pages, on sections, on chapters, on 5, on 6 etc.,
  • we can’t skip any axis in MDX Query, we no need maintain the sequential axis, but sequence should exist in MDX Query
For Ex:
This Query contains one columns and Rows

SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS,
{[Date].[Calendar].MEMBERS} ON ROWS
FROM [Adventure Works]

The same query will work, even if the different sequence,

SELECT
{[Date].[Calendar].MEMBERS} ON ROWS,
{[Measures].[Internet Sales Amount]} ON COLUMNS
FROM [Adventure Works]

But it won’t work, if we miss or skip any sequence like, here axis 1 is not used, It is Invalid

SELECT
{[Date].[Calendar].MEMBERS} ON 0,
{[Measures].[Internet Sales Amount]} ON 2
FROM [Adventure Works]

But You can write SELECT clause without using any Axes, in this case, all the dimensions available in the cube will act as slicer, MDX fetches the first cell value

MDX: Tuple Vs Set

Tuple:
  • It is a collection of members from different Dimension
  • It Allows only one member from each dimension
  • It is a Basic Unit to forming an axis
  • As a rule, tuples are enclosed in parenthesis
Set:
  • One or More tuples from same dimension,
  • All tuples enclosed in a set with same order of dimensions
Set Can contains tuples and tuple can consist of dimensions.

SSRS: Report Builder

Report Builder is a tool provided by Microsoft, which can help the user the design the report their own. It supports to build a table, matrix and chart reports. User can design the report with the help of predefined data model. User can interactively explore the data within report model with the help of Report Builder Tool

You can download this tool from,

Report Builder 2.0

Report Builder 3.0

SSRS: Role Based Security and Types

Reporting Services uses a role-based security model to control access to reports, folders, and other items that are managed by a report server.
The followings Types of role can be defined by using report manager for a SSRS Items.
Role Name
Description
Browser
May view folders, reports and subscribe to reports.
Content Manager
May publish reports and linked reports; manage folders, reports and resources in a user's My Reports folder.
Publisher
May publish reports and linked reports to the Report Server.
Report Builder
May view report definitions.

SSRS: Permissions granted to user are insufficient

Problem:
I have installed SQL Server 2008 in my laptop, when I tried to browse the Report Server URL, http://localhost/Reportserver/, system throws following Error

The permissions granted to user 'Domain\User' are insufficient for performing this operation. (rsAccessDenied) Get Online Help

Solution:
1. Execute Internet Explore thru “Run as Administrator” Mode
2. Then browse this URL, it will works fine




Note: It works only on Internet Explorer, it won’t work at Google Chrome and Mozilla Firefox, even if you try with “Run as Administrator” Mode

Tuesday, November 23, 2010

Reporting Services Login box appears:

Today one of friend told me that he is facing the problem like "Report Services Login Screen Appears" while trying to deploy the report. Even it is not accepting after enter the proper credential.

I found the error that "TargetServerURL" path is invalid. It contains only http://localhost/

It is working after changed as http://localhost/ReportServer

Now if we deploy the report, it is working fine.

Sometimes problem may be,

2.http://localhost/reportserve$SQL2005 – Different Instance

If the "TargetServerURL" is proper, then it works fine. If you are deploying the report on server from your system, if you enter the proper credential then it will work!

Monday, November 22, 2010

Enable/Disable - SSRS Export Types

SQL Server Reporting Services (SSRS) supports to export different types like XML, CSV, HTML, PDF, etc., when you view the report.  This export option is available at report viewer Export drop-down list.
We can control this lists thru report services server configuration file which is available at "C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\rsreportserver.config".
In This File, In <Render /> tag elements, add attribute Visible="false" and save the file. Now if you check, it won't be available at Export drop-down list
Example:
<Render>
<Extension Name="XML"
Type="Microsoft.ReportingServices.Rendering.XmlDataRenderer.XmlDataReport,Microsoft.ReportingServices.XmlRendering" Visible="false" />
</Render>

Hope it Help!

ReportServer Vs Reports

http://localhost/ReportServer

http://localhost/Reports

It is a Web Service

It is a Web Application

Only you can access the report

Here you can view, manage the report, creating roles, enabling subscription, schedule the report, etc., we can call it as a Report Manager

Pupose of ampersand (&) character in MDX

In MDX Query, A member can be referenced by either its member name or by its member key


The ampersand (&) character is used in MDX to differentiate a member key from a member name, as shown in the following example:


Reference the member by its member Key:
[Time].[2nd half].&[Q4]


Reference the member by its member name:
[Time].[2nd half].[4th quarter]

What are the Components in Reporting Services (SSRS)?

The following Components are available in SSRS:
  1. Report Builder
  2. Report Designer
  3. Report Manager
  4. Model Designer
  5. Report Server

Friday, November 19, 2010

Simple SSRS 2008 Architecture

Microsoft Visual Studio 2010 Express Prerequisites

The followings are necessary, if you want to install Microsoft Visual Studio 2010 Express in your system


System Requirements
1.1 Supported Architectures
  • x86
  • x64 (WOW)
1.2 Supported Operating Systems
  • Windows XP (x86) with Service Pack 3 - all editions except Starter Edition
  • Windows Vista (x86 & x64) with Service Pack 2 - all editions except Starter Edition
  • Windows 7 (x86 and x64)
  • Windows Server 2003 (x86 & x64) with Service Pack 2 - all editions
    • Users must install MSXML6 if it is not already present.
  • Windows Server 2003 R2 (x86 and x64) - all editions
  • Windows Server 2008 (x86 and x64) with Service Pack 2 - all editions
  • Windows Server 2008 R2 (x64) - all editions
1.3 Hardware Requirements
  • 1.6 GHz or faster processor
  • 1024 MB RAM (1.5 GB if running on a virtual machine)
  • 3 GB of available hard-disk space
  • 5400 RPM hard-disk drive
  • DirectX 9-capable video card running at 1024 x 768 or higher display resolution
  • DVD-ROM drive

Thursday, November 18, 2010

Error: Visual Studio setup cannot run in compatibility mode

Today I tried to installing .. Windows Phone Developer Tools RTW in my system. i faced the following Error:

Problem:
---------------------------
setup.exe
---------------------------
Visual Studio setup cannot run in compatibility mode. For more information see the 'Installing' section in the
Visual Studio Readme at "http://go.microsoft.com/fwlink/?LinkId=143397".
--------------------------
OK
---------------------------


Solution:
After Google the solution for this problem, i got the followings steps..


1. Right click on setup.exe and click Properties
2. Switch to the Compatibility tab
3. Untick the "Run this program in compatibility mode for" checkbox
4. If you have a button at the bottom of the dialog entitled "Show settings for all users" click this and Untick the "Run this program in compatibility mode for" checkbox
5. Click OK until you have dismissed all dialog windows
6. Go back to the uninstall entry and uninstall as normal.

Wow... It works fine.. :)
Hope It Helps