Search this blog

Thursday, December 8, 2011

How To Create XMLA Script For Cube Process Using SSMS

Following steps will explain “how to generate a XMLA Script for Cube process” using SQL Server Management Studio (SSMS).
1.       Open SQL Server Management Studio by clicking on the SSMS shortcut icon which located in the Start --> All Programs --> Microsoft SQL Server 2008 R2 --> SQL Server Management Studio.
See the given exhibit for reference (select any version whichever you installed)
2.       Click on the “Connect” button, which is available at Object Explorer, Choose “Analysis Services…” as shown Below
3.       Enter the server detail/credential you want to connect
4.       After you connected to the with proper server details, you can see the list of databases available on the server in Object Explorer Windows as shown below
5.       In Object Explorer, Select the cube which you need to process --> Right Click on “Process”
6.       Upon click on process, Following model dialog window will appear  --> Click on Script link
7.       Script link helps you create a script in 3 ways as listed in the exhibit
8.       I’ve selected the option to generate the output script on “New Query Window”, so it generated the script on New XMLA Script window as mentioned below:
Sample XMLA Script:-
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Parallel>
    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200">
      <Object>
        <DatabaseID>Analysis Services Tutorial</DatabaseID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel> 
</Batch>
Note: system will generate the script as per the input you provided on Process window in step 6

Friday, November 25, 2011

SQL Server 2012 RC0 Available for Download

Microsoft SQL Server 2012 Release Candidate (RC0) is now available for Download.

There are many highlights and stunning BI features available in SQL Server 2012.
If you evaluated the previous release SQL Server 2012 Community Technology Preview 3 (CTP3), then you might aware that “PowerPivot” now supports the features like,
  • Hierarchies
  • Multiple relationships between tables
  • A measure grid to easily create, edits, and manages measures
  • Key Performance Indicators (KPIs)
  • New DAX functions (Time series, distinct count to name a few)
  • Importing of binary large objects (BLOB)
  • Etc.,
All the above listed features and more… in Analysis Services Database
  • Row-level security
  • Partitions and Direct Query mode
  • Direct access to source system
  • Etc.,
Other improvements:
  • Upgraded SharePoint Components
  • Separated setup and configuration strategy
  • Project "Crescent" (now called Power View)
  • And more…
Here are the highlights and product overview for SQL Server 2012 Release Candidate from Microsoft:

Microsoft SQL Server 2012 RC0 enables a cloud-ready information platform that will help organizations unlock breakthrough insights across the organization as well as quickly build solutions and extend data across on-premises and public cloud backed by capabilities for mission critical confidence.

SQL Server 2012 RC0 enables a cloud-ready information platform that will help organizations unlock breakthrough insights across the organization as well as quickly build solutions and extend data across on-premises and public cloud backed by capabilities for mission critical confidence:
  • Deliver required uptime and data protection with AlwaysOn
  • Gain breakthrough & predictable performance with ColumnStore Index
  • Help enable security and compliance with new User-defined Roles and Default Schema for Groups
  • Enable rapid data discovery for deeper insights across the organization with ColumnStore Index
  • Ensure more credible, consistent data with SSIS improvements, a Master Data Services add-in for Excel, and new Data Quality Services
  • Optimize IT and developer productivity across server and cloud with Data-tier Application Component (DAC) parity with SQL Azure and SQL Server Data Tools for a unified dev experience across database, BI, and cloud functions
Note: Registration takes only a few moments and allows Microsoft to provide you with the latest resources relevant to your interests, including service packs, security notices, and training.

Other References:

Thursday, November 24, 2011

SQL: Database Backup Strategies

This topic describes strategies you can use to establish appropriate backup guidelines. 
The strategies are:
  1. Full Backup
  2. Differential Backup
  3. Partial Backup
  4. Partial Differential Backup
Full Backup:
A full database backup backs up the whole database. This includes part of the transaction log so that the full database backup can be recovered. Full database backups represent the database at the time the backup finished.

A full database backup contains all the data in the database. For a small database that can be backed up quickly, the best practice is to use just full database backups. However, as a database becomes larger, full backups take more time to finish and require more storage space. Therefore, for a large database, you might want to supplement full database backups with differential backups.
This back up contains all the file-groups (files containing data)
Differential Backup:
A differential database backup records only the data that has changed since the last full database backup. This full backup is called the differential base. Differential database backups are smaller and faster than full database backups. This saves backup time at the cost of increased complexity. For large databases, differential backups can occur at shorter intervals than database backups. This reduces the work-loss exposure.
Differential database backups are especially useful if a subset of a database is modified more frequently than the rest of the database. In these cases, differential database backups enable you back up frequently without the overhead of full database backups.
This backup contains the extents of the database which are changed after the previous full back up. In case if a full back up was taken at 2pm. Then if we try to take a differential back up  at 4 pm it contains all the changes since 2pm. After taking this differential back up, if we try to take another differential back up at 6pm, still it contains all the changes since 2pm.
Partial Backup:
A partial backup resembles a full database backup, but a partial backup does not contain all the file-groups. Instead, a partial backup contains all the data in the primary file-group, every read/write file-group, and any optionally-specified read-only files. Partial backups are useful whenever you want to exclude read-only file-groups. 
A partial backup of a read-only database contains only the primary file-group.
Partial Differential Backup:
A differential partial backup records only the data extents that have changed in the file-groups since the previous partial backup.
If a partial back up (backing up specific file-group B excluding read only file-groups A ) is taken at 2pm. If there are certain changes occurred in the file-group B at 3 pm. Then if we perform the differential partial  back up at 4pm, it gives the only change occurred at 3pm (contains all the changes between 2pm and 3pm)

Wednesday, November 23, 2011

SQL:Generate Script for Table Data

Today, one of my colleague asked me, how to generate a Script for a table-schema as well as data-Schema?

Whatever I explained to him, I thought to share the same in my blog as well, hope it helps to someone!!!

Most of you are aware that we can generate T-SQL scripts for multiple or individual objects by using the "Generate Scripts” Wizard thru Object Explorer.

You can also generate a script for data as well, the following steps will explain you, how to generate the Script for Table Schema with Data.

1. Right Click on Database (in which you want to extract schema for Data) >> Tasks >> Generate Scripts >>

2. “Generate Scripts” wizard will be appearing with 5 Tabs like
  • Introduction
  • Choose Objects
  • Set Scripting Options
  • Summary
  • Save or Publish Scripts
By default, it is pointing to Introduction Tab

Note:  if you want to skip the Introduction Page from next usage of this wizard, then you can check the option “Do Not Show this Page Again”. After this setting, if you open the “Generate Scripts” wizard, by default it will point to
“Choose Objects” Tab.

3. Click on the button “Next” to redirect to “choose Objects” Tab.“Choose Objects” Page has following two options
  • Script entire database and all database objects (Default Option)
  • Select Specific Objects
By Default, it will be pointing to Entire Database Option (#1). If you want to create a script for specific table then check the second option as exhibit below.

4. After Specific SQL Objects, Click on the “Next” button to redirect to “Set Scripting Options” Tab, here follow the 2 steps
  • Specify the output path of the script file in File name Text box (refer the exhibit which is marked with maroon color box)
  • Click on the Advanced Button

Model popup will appear with list of options which are categorized as General and Table/View Options
Under General category, Find the option labeled as “Type of data to Script” (see the exhibit below), this option has following 3 values
  • Schema Only (Default Option)
  • Schema and Data
  • Data Only
Select it accordingly based on your requirement. Once you select the appropriate option, click on the OK button in the model popup window

Note: Choose “Schema and Data” option to generate the script for Schema as well as Data.

5.Click on Next Button to redirect Next Tab which is named as “Summary”, here you can review/verify all your selection and settings.
6.After review your setting, Select Next generates the script.
7.Click on the Finish Button after the status of selected action says success.
 
8.Check the Path which you specified on step 4(a), you can see file with “.sql” extension.
Note: If there is no record in the selected table then wizard will extract only schema.

Sample Output File:

Friday, November 18, 2011

Virtual Lab: Team Foundation Server(TFS) 2010

Today, I came across the Hand on Labs for Team Foundation Server 2010 which is released by Microsoft.

MSDN Virtual Lab: Planning your Projects with Team Foundation Server 2010:-

Objectives of this TFS Virtual Lab:-
After completing this lab, you will be better able to:

* Manage user stories
* Use iteration work items and team capacity
* Work with hierarchical work items in visual studio and project
* Introduction to Microsoft Solutions Framework (MSF) agile process sample documents


Have a look at it.... Happy Learning :-)


To be used this Lab efficiently; the following hardware/software components should be present on your computer.


System Requirements:
1.       Windows:
Only Microsoft Windows XP, Windows 2000, Windows Server 2003, Windows Server 2008, Windows Vista, and Windows 7 are supported.
2.       Microsoft Internet Explorer 6 or Later:
Due to the complex requirements of presenting these Virtual Lab sessions over the internet, only Microsoft Internet Explorer 6 or later are supported.
3.       Screen Resolution of 1024 X 768:-
The Virtual Lab sessions are best viewed at a screen resolution of 1024x768 or greater. Otherwise you may experience clipping of content.
4.       Cookies Enabled:
You must have cookies enabled to run these Virtual Lab sessions.
5.       Activex Control:
The Microsoft "Remote Desktop / Terminal Services" ActiveX control is required by this site. If you do not have it installed/enabled, you will be asked to when launching the Virtual Lab.
6.       Firewall:
Connecting to the Virtual Lab sessions require that you have port 443 open. Please contact your network administrator for assistance.

Thursday, November 17, 2011

Maximum Capacity of DB Engine Objects


The following table shows what is the maximum size/number of objects can be created/Used in DB Engine by thru wizard or by T-SQL Statements
SQL Server Database Engine object
Maximum sizes/numbers SQL Server (32-bit) Maximum sizes/numbers SQL Server (64-bit)
Batch size1 65,536 * Network Packet Size 65,536 * Network Packet Size
Bytes per short string column 8000 8000
Bytes per GROUP BY, ORDER BY 8060 8060
Bytes per index key2 900 900
Bytes per foreign key 900 900
Bytes per primary key 900 900
Bytes per row8 8060 8060
Bytes in source text of a stored procedure Lesser of batch size or 250 MB Lesser of batch size or 250 MB
Bytes per varchar(max), varbinary(max), xml, text, or image column 2^31-1 2^31-1
Characters per ntext or nvarchar(max) column 2^30-1 2^30-1
Clustered indexes per table 1 1
Columns in GROUP BY, ORDER BY Limited only by number of bytes Limited only by number of bytes
Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement 10 10
Columns per index key7 16 16
Columns per foreign key 16 16
Columns per primary key 16 16
Columns per nonwide table 1024 1024
Columns per wide table 30000 30000
Columns per SELECT statement 4096 4096
Columns per INSERT statement 4096 4096
Connections per client Maximum value of configured connections Maximum value of configured connections
Database size 524,272 terabytes 524,272 terabytes
Databases per instance of SQL Server 32767 32767
Filegroups per database 32767 32767
Files per database 32767 32767
File size (data) 16 terabytes 16 terabytes
File size (log) 2 terabytes 2 terabytes
Foreign key table references per table4 253 253
Identifier length (in characters) 128 128
Instances per computer 50 instances on a stand-alone server for all SQL Server editions.

SQL Server supports 25 instances on a failover cluster.
50 instances on a stand-alone server.

25 instances on a failover cluster.
Length of a string containing SQL statements (batch size)1 65,536 * Network packet size 65,536 * Network packet size
Locks per connection Maximum locks per server Maximum locks per server
Locks per instance of SQL Server5 Up to 2,147,483,647 Limited only by memory
Nested stored procedure levels6 32 32
Nested subqueries 32 32
Nested trigger levels 32 32
Nonclustered indexes per table 999 999
Number of distinct expressions in the GROUP BY clause when any of the following are present: CUBE, ROLLUP, GROUPING SETS, WITH CUBE, WITH ROLLUP 32 32
Number of grouping sets generated by operators in the GROUP BY clause 4096 4096
Parameters per stored procedure 2100 2100
Parameters per user-defined function 2100 2100
REFERENCES per table 253 253
Rows per table Limited by available storage Limited by available storage
Tables per database3 Limited by number of objects in a database Limited by number of objects in a database
Partitions per partitioned table or index 1000 1000
Statistics on non-indexed columns 30000 30000
Tables per SELECT statement Limited only by available resources Limited only by available resources
Triggers per table3 Limited by number of objects in a database Limited by number of objects in a database
Columns per UPDATE statement (Wide Tables) 4096 4096
User connections 32767 32767
XML indexes 249 249