Asd

Friday, December 30, 2011

Have a happy and prosperous New Year 2012

This has been an exciting year for me I achieved lots of thing in this year and continue to achieve in next year. Thanks every one for your support.

Have a happy and prosperous New Year 2012 from me and my family to all my friends Blog readers.



May the blessings of the Lord enrich your family and friends, not only in wealth but in health, love and happiness.

A New Year starts, with a new calendar.  But my love stays constant, with you always in my heart.  Happy New Year 2012!

Thanks,
Suhas Rajaram Kudekar

Monday, December 19, 2011

Available MDX Time Functions in SSAS.

Today I will discuss more on available MDX Time Functions. I already post the number of available MDX function in my previous post (Types of MDX Functions available in MDX Queries).

For those who doesn’t know more on MDX. Those can get some more idea on my previous post (To get a comma separated list in SQL & MDX).

There are total 9 Time Function available in MDX & MSBI developer people like to use them in their reports.

1-Function Name:  ClosingPeriod
Syntax:  CLOSINGPERIOD( [«Level»[, «Member»] ] )
Description:  The ClosingPeriod function returns the last sibling from the descendants of a supplied member at a supplied level. 
The query above returns the default measure for the closing period at the Calendar Month level for the member CY 2008.

2-Function Name:  LastPeriods
Syntax:  LASTPERIODS( «Index»[, «Member»] )
Description:  The LastPeriods function returns the supplied number of members up to and including the supplied member.
The above query returns the first 15 days of July 2005 in the Calendar hierarchy in the Date dimension.

3-Function Name:  Mtd
Syntax:  MTD( [«Member»] )
Description:  The MTD function returns all siblings of the supplied member starting at the first sibling and ending with the supplied sibling.
The above query returns all siblings up to and including the member July 8, 2005 from the Calendar hierarchy in the Date dimension.

4-Function Name:  OpeningPeriod
Syntax:  OPENINGPERIOD( [«Level»[, «Member»] ] )
Description:  The OpeningPeriod function returns the first sibling from the descendants of a supplied member at a supplied level. 
The query above returns the default measure for the opening period at the Calendar Month level for the member CY 2008.

5-Function Name:  ParallelPeriod
Syntax:  PARALLELPERIOD( [«Level»[, «Numeric Expression»[, «Member»] ] ] )
Description:  The ParallelPeriod function returns a member from a prior period in the same relative position as a supplied member. 
The query above returns the member Q2 CY 2008 which is at the supplied Calendar Quarter level (Q1 CY 2004) and three prior periods (Calendar Year) away from the supplied member of Q1 CY 2004.

6-Function Name:  PeriodsToDate
Syntax:  PERIODSTODATE( [«Level»[, «Member»] ] )
Description:  The PeriodsToDate function returns a set of siblings from the same level as the supplied member. The returned set starts with the first sibling and ends with the supplied member.
The query above returns the first sibling at the Date level up to the supplied member of March 7, 2008. 

7-Function Name:  Qtd
Syntax:  QTD( [«Member»] )
Description:  The QTD function returns a set of siblings from the same level as the supplied level. The set consists of the first sibling up to and including the supplied member.
The query below returns a set of dates starting with the first day of the quarter (July 1, 2005) and ending with supplied date of July 07, 2005.

8-Function Name:  Wtd
Syntax:  WTD( [«Member»] )
Description:  The WTD function returns a set of siblings from the same level as the supplied level. The set consists of the first sibling up to and including the supplied member.
If you run the above statement in SQL Server Management Studio, you would receive the following error: Executing the query ... Query (1, 8) By default, a week level was expected. No such level was found in the cube. Execution complete.

9-Function Name:  Ytd
Syntax:  YTD( [«Member»] )
Description:  The YTD function returns a set of siblings from the same level as the supplied level. The set consists of the first sibling up to and including the supplied member.
The query above returns a set of dates starting with July, 2005 and ending with July 14, 2005.

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)
Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2

Tuesday, October 25, 2011

'SQL Server Denali' No More: SQL Server 2012 Announced.

Wish you Happy Diwali and New Year to all of my friends & there family.


Now some important announced of SQL Server.

Microsoft VP Ted Kummert announced the official name of the next version of the SQL Server – SQL Server 2012. The version of the SQL Server will be 11. The release date is estimated in the first half of the year 2012.

Microsoft also announced that the new BI self-service reporting tool, codename “Crescent”, (Get more details of Crescent in my previous blog post) will be given the official name Power View. Power View (Crescent) is a highly interactive data exploration tool and a preview is already available with the CTP3 of Denali.

SQL Server 2012 Power View (Crescent) is one of the new tools that will be released with the next version of SQL Server. Currently it is available in the SQL Server Denali CTP, the beta version of the next SQL Server, SQL Server 2012.

The business user will start the application by opening an existing Crescent report or choosing a BISM – Business Intelligence Semantic Model (Get more details of BISM in my previous blog post) to start from. This BI Semantic Model is also a new feature in the Denali release. As the name says, it is a semantic model/layer on top of your data warehouse. Using this model abstracts the data warehouse complexity for a business user, and enables him/her to create reports with Crescent.

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)
Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2

Monday, October 3, 2011

Change the Look & Fill of the Report Manager.

In Previous Blog Architecture Diagram of SSRS  post we were getting the completed diagrammatic overview of SSRS Architecture. In that a Web-based report access and management tool is available named as Report Manager.

Definition - Report Manager is a Web-based report access and management tool that we use to administer a single report server instance from a remote location over an HTTP connection. we can also use Report Manager for its report viewer and navigation features.


Different type of Report Manager Properties Available on clicking on diffrent available Tab.
·         Site Settings Properties
On the Site Settings page, we have General Properties, Security and Schedule
Report Manager – Data Source Properties with the exception of Dependent Items. Dependent Items page will list all items (reports, data sets) that use this particular Data Source.

·         Search Properties
We can now search in SSRS Report Manager! Type in our keyword, and a way we go! It will find your report, your data set, your folder, or even your report part.

·         Report Contextual Dropdown Properties
When we hover over a report item and click on the down arrow, we will be able to move, delete, edit in Report Builder 3.0, and explore other properties, to name a few.

·         Report Properties
We have links for : properties, data sources, shared data sets, subscriptions, processing options, cache refresh options, report history, snapshot, and security.

·        Shared Data Set Properties
Share data sets among diffrent reports.

·         Shared Data Set Caching Properties
Cache these shared data sets.

We can use Report Manager to browse the report server folders or search for specific reports. we can view a report, its general properties, and past copies of the report that are captured in report history. Depending on your permissions, we might also be able to subscribe to reports for delivery to an e-mail inbox or a shared folder on the file system.

How we can Start Report Manager

ü  To start Report Manager from a browser Open Microsoft Internet Explore

ü  In the address bar of the Web browser, type the Report Manager URL.
By default, the URL is http://<ComputerName>/reports.

ü  The report server might be configured to use a specific port. For example,
http:// <ComputerName>:80/reports or
http:// <ComputerName>:8080/reports.

Change Bascic look & fill of Report Manager 
To change the top area color in Report Manager, please refer to the following steps to achieve this:

1.      Navigate to ReportingServices.css file.
Path: <Drive:> \Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\Styles
2.      If want backup the ReportingServices.css file before you modify it, open it with notepad format.
3.      Change the following portion in ReportingServices.css file:

Original Available - table.msrs-header { background-color:#fff; }

After Changed - table.msrs-header { background-color: #f00; }

This way we can change the Look & Fill of the Report Manager.

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)
Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2

 

Monday, September 26, 2011

Project ‘Crescent’ - Reporting Services new reporting tool in Denali.

In my previous post (Business Intelligence Semantic Model (BISM)) I gave some information on Business Intelligence Semantic Model (BISM), which will be used to 'power' access to the data for the Microsoft Business Intelligence applications such as Excel, Reporting Services (SSRS) and Sharepoint.It is also intended that Project Crescent, the new self-service ad-hoc reporting tool available in SQL Server Denali, will be powered by the BISM.

What’s new Denali for Reporting Services (SSRS)
SharePoint Shared Service
Reporting Services in SharePoint now runs as a SharePoint shared service. Among other benefits, this provides integration with claims authentication and scaling and load-balancing across the SharePoint farm. Furthermore, report viewing performance in SharePoint has been enhanced significantly.

Self Service Alerting
Alerting is a new capability that we are adding to Reporting Services as well. It enables an end-user to setup alert rules and be alerted when report data changes occur that match a set of rules. No changes are required to the existing reports – you can create alerts for any reports created in previous versions of Reporting Services, as soon as upgrade/move to SQL Server Denali in SharePoint integrated mode.

Excel rendering as XLSX, Word rendering as DOCX
These new renderers produce now Open XML Office format and take advantage of some of the new capabilities in Office 2007 and 2010, such as export up to a 1 million rows in Excel. The original renderers for DOC and XLS, which produce binary formats for older Office versions are still available but hidden by default.

BI Development Studio integrated in Visual Studio 2010
Not only did we adopt the Visual Studio 2010 shell for BI Development Studio for report design, but we also rearchitected our integration with Visual Studio in general for RDLC designer and report viewer controls. This will be available with the upcoming next version of Visual Studio and enable much more simultaneous availability of SQL Server Reporting Services functionality in Visual Studio as well.

Project Crescent –
Is the Reporting Services’ new reporting tool targeted at data consumers to visually explore their data and answer ad-hoc questions with ease? 

Architecture Diagram of Project Crescent


Project “Crescent” is a new interactive data exploration and visual presentation experience coming in the next version of SQL Server, code-named “Denali”.  It will offer a fun, visual, and powerful drag-and-drop ad hoc reporting experience.  It is an web-based end-user BI tool based on Silverlight.
Highly Visual design Experience-
·         -Interactive web-Based authoring and sharing of information
·         -Familiar Microsoft office design patters
·         -Powerful data layout with banding, callout & small multiples visualization


Each Crescent report is based on a Power Pivot model that can be created within Excel or Visual Studio 2010.  The models are deployed to SharePoint and from there users can create their reports through the web front end.
Rich metadata-Driven interactivity
·         -Fully integrated with Power Pivot
·         -Drive greater insight through smart & powerful querying
·         -Zero configuration highlighting and filtering
·         -Animated trending and comparisons


It is done all in a browser – there is nothing to deploy.  It is presentation-ready, meaning there is not a design mode.  It is not meant to replace SSRS or Report Builder as those are for creating sophisticated static reports while Crescent is for ad-hoc reports.  It is also not meant to replace PerformancePoint as that is for KPI’s and scorecards.
Presentation -Ready at all times
·         -Interactive Presentation turns pervasive information into persuasive information
·         -deliver and collaborate through SharePoint
·         -Full screen presentation mode for interactive boardroom session

  

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)
Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2

Thursday, June 23, 2011

Different SQL Script for finding Last Backup History for All Database.

Below script is very helpful when you need to find last backups, physical device, and backup size for a database.

The msdb system database is the primary repository for storage of SQL Agent, backup, Service Broker, Database Mail, Log Shipping, restore, and maintenance plan metadata.

We can get all the information for this task out of below msdb System Table: 

      msdb.dbo.backupset.  - Provides information concerning the most-granular details of the backup process .The important columns of note for this object are itemized below.  Asterisks denote the columns we'll be using in this query:
§  database_name - name of the database the backup file pertains to.
§  type - Type of backup process performed:
·         D = Database
·         F = File or Filegroup
·         G = Differential File
·         I = Differential (database)
·         L = Transaction Log
§  backup_finsh_date - time when the backup process completed.
§  backup_start_date - time the backup process was initiated.
§  name - name of the backupset
§  user_name - user performing the backup process
§  expiration_date - date the backup expires

 
SQL Scripts 1

SELECT
      sys.sysdatabases.Name AS DATABASE_NAME,
      COALESCE(CONVERT(VARCHAR(12),MAX(msdb.dbo.backupset.backup_finish_date), 101),'NEVER') AS LAST_DATABASE_BACKUP_DATE
FROM
      sys.sysdatabases
      LEFT OUTER JOIN msdb.dbo.backupset
      ON msdb.dbo.backupset.database_name = sys.sysdatabases.name
GROUP BY
      sys.sysdatabases.Name 

SQL Scripts 2

SELECT
      msdb.dbo.backupmediafamily.physical_device_name AS FILE_LOCATION,
      msdb.dbo.backupset.database_name AS DATABASE_NAME,
      CAST(CAST(msdb.dbo.backupset.backup_size / 1000000 AS INT) AS VARCHAR(14))+ ' ' + 'MB'  AS BACKUP_SIZE,
      CAST(DATEDIFF(second, msdb.dbo.backupset.backup_start_date,      msdb.dbo.backupset.backup_finish_date) AS VARCHAR(4)) + ' '+ 'Seconds' AS BACKUP_TIME_TAKEN,
      msdb.dbo.backupset.backup_start_date AS BACKUP_DATE_TIME,
      CASE msdb.dbo.backupset.[type]
      WHEN 'D' THEN 'Full'
      WHEN 'I' THEN 'Differential'
      WHEN 'L' THEN 'Transaction Log'
      END AS BACKUP_TYPE,
      msdb.dbo.backupset.server_name AS SERVER_NAME
FROM
      msdb.dbo.backupset
      INNER JOIN msdb.dbo.backupmediafamily 
      ON msdb.dbo.backupset.media_set_id = msdb.dbo.backupmediafamily.media_set_id

SQL Scripts 3

SELECT
      ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(Backup_finish_date)))), 'NEVER') AS DAY_LAST_BACKUP_TAKEN,
      master.dbo.sysdatabases.name AS DATABASE_NAME,
      ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') AS LAST_BACKUP_DATE
FROM
      master.dbo.sysdatabases
      LEFT OUTER JOIN msdb.dbo.backupset
      ON msdb.dbo.backupset.database_name = master.dbo.sysdatabases.name
      AND msdb.dbo.backupset.type = 'D'
GROUP BY
      master.dbo.sysdatabases.Name

SQL Scripts 4

SELECT
      database_id AS DATABASE_ID,
      convert(varchar(25), sys.databases.name) AS DATABASE_NAME,
      convert(varchar(10), Databasepropertyex(name, 'status')) AS DATABASE_STATUS,
      (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = sys.databases.name AND type_desc = 'rows') AS DATAFILES ,
      (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = sys.databases.name AND type_desc = 'rows') AS DATA_FILE_SIZE_IN_MB,
      (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = sys.databases.name AND type_desc = 'log') AS LOG_FILES,
      (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = sys.databases.name AND type_desc = 'log') AS LOG_FILE_SIZE_IN_MB,
      CASE compatibility_level
      WHEN 60 THEN '60 (SQL Server 6.0)'
      WHEN 65 THEN '65 (SQL Server 6.5)'
      WHEN 70 THEN '70 (SQL Server 7.0)'
      WHEN 80 THEN '80 (SQL Server 2000)'
      WHEN 90 THEN '90 (SQL Server 2005)'
      END AS COMPATIBILITY_LEVEL,
      CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS DATABASE_CREATION_DATE,
      CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled' ELSE ' ' END AS FULL_TEXT,
      CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE ' ' END AS AUTOCLOSE,
      CASE WHEN is_read_only = 1 THEN 'read only' ELSE ' ' END AS  READ_ONLY,
      CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE ' ' END AS AUTOSHRINK,
      CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE ' ' END AS AUTO_CREATE_STATISTICS,
      CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE ' ' END AS AUTO_UPDATE_STATISTICS,
      CASE WHEN is_in_standby = 1 THEN 'standby' ELSE ' ' END AS STAND_BY,
      CASE WHEN is_cleanly_shutdown = 1 THEN 'cleanly shutdown' ELSE ' ' END AS CLEANLY_SHOUTDOWN,
      user_access_desc AS USER_ACCESS,
      recovery_model_desc AS  RECOVERY_MODEL
FROM
      sys.databases


Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)
Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2