Zoltán Horváth on MSBI

Performing and Automating Microsoft BI Related Tasks with PowerShell

2012-09-10T20:00:00.000+02:00

I just ran into PowerShell somehow. If you have some familiarity in using .NET, then it should be around 2 hours to pick it up. Of course, you will be on the same basic level like me, but these scripts will work as expected :)

I quicly wrote some scripts and shared them in the TechNet Galleries:

Listing Enabled SQL Agent Jobs Executing SSIS Packages: there was a request from someone to write it, after they could list all their packages in MSDB with an earlier T-SQL script.
List Elevated Permissions in an Analysis Services Database (PowerShell): this script is nice to have before starting some security audit activities on SSAS databases.
Listing Not Processed Objects on an SSAS Instance (PowerShell): this is for those who have a lot of databases, and would like to check if something should be processed.
Bulk Report Deployment with PowerShell: this is useful for those who have a nice report folder structure, and would like to deploy easily.

If you need some changes to the scripts, please let me know on the Q and A sections of these contributions. If you don't need any changes, and the scripts are OK, then please don't forget to rate them :)

T-SQL script for refreshing all views in a database

2012-08-07T21:00:00.000+02:00

There are scenarios when SELECT * is used in views - it's not uncommon in a relational data warehouse.

If WITH SCHEMABINDING is not specified when the view is created, then changing the tables behind the views might corrupt the mapping of the table columns to the output columns, which can have a very confusing result.

A stored procedure called sp_refreshview can be called to correct the mappings. But if you don't have time to look for the corrupted one, you can fabricate a script to loop through all of them.

Or, you can download my one and simply run it. I posted a script to TechNet Galleries, which refreshes all views in a database, regardless of their schema.

Basically it's a cursor solution, calling sp_refreshview for each view in the database.

You can find it here.

SSIS Transformations Wiki: Pivot

2012-07-21T10:00:00.000+02:00

Again, a new page for TechNet Wiki, now about the Pivot transformation.
I introduced it through a small example.

SSIS Transformations Wiki: Unpivot

2012-07-14T00:25:00.000+02:00

A new page to TechNet Wiki, about the Unpivot transformation.
Of course, I updated the List of Transformations page as well to refer to this one.

Sample: Automatically Incrementing Columns in SSIS

2012-05-07T18:47:00.003+02:00

There are cases when you don't have a chance to add an auto-incrementing column to a source of a data flow. Sometimes it's not possible (for example, using a flat file source), sometimes it doesn't make a sense (when the data will be resorted or filtered in the data flow later).

I just uploaded a small example to TechNet Gallery how you can add an auto-incrementing column in the middle of a data flow. It's an SSIS 2008 R2 package - if you need another version, let me know via the Gallery. The package adds two columns to the data flow with Script Components - the sample codes are the same, just written in C# and VB.NET as well.

The package also shows how to use a recursive CTE (in the data source) to generate 500 rows of data. You will need to have a tempdb on your localhost, or to edit the connection manager to refer to any accessible SQL 2008 database.

Ratings, comments and questions are always welcome.

SQL Server 2012 exams and certifications

2012-04-08T14:34:00.001+02:00

The changes arrived with SQL Server 2012 are reflected in the related exams and certifications as well.
The most important change is that two MCITP-level certifications will be introduced: the Data Platform and the Business Intelligence certifications. Yeah, the administrator and the developer certifications had been merged.

As the official MS Learning site writes, you'll have to pass five exams to achieve a Professional level (MCITP) certification. Nothing had been shared yet regarding the Technology Specialist level. If you already hold an MCITP certification for SQL Server 2008, it will be enough to pass three of them only, as an upgrade. More details will be provided in mid-April. But if you're planning to clear an MCITP exam on SQL Server 2008 - holding an MCTS SQL Server 2008 certification already -, you can save the fee of one exam by choosing the version upgrade path from MCITP 2008 to MCITP 2012.

It's just a guess from me, but since three of five exams are the same on the Data Platform and on the BI track, I presume these 3 will be needed for the MCTS level.

The common exams for both tracks will be:
70-461: Querying Microsoft SQL Server 2012
70-462: Administering Microsoft SQL Server 2012 Databases
70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012

The MCITP level Data Platform exams will be:
70-464: Developing Microsoft SQL Server 2012 Databases
70-465: Designing Database Solutions for Microsoft SQL Server 2012

The MCITP level exams for Business Intelligence will be:
70-466: Implementing Data Models and Reports with Microsoft SQL Server 2012
70-467: Designing Business Intelligence Solutions with Microsoft SQL Server 2012

There are some seats announced for a beta exam program, but these are filling up. The exam codes start with 71 in this case.

Good luck!

You can find BIDS features in SQL Server Data Tools for SQL Server 2012

2012-03-20T12:50:00.000+01:00

The development environment for business intelligence (and database) solutions - earlier Business Intelligence Development Studio - is called SQL Server Data Tools in SQL Server 2012.
It can be installed using the SQL Server 2012 installation kit.

It's a Visual Studio 2010 Shell in Integrated Mode. Check out my earlier post regarding VS modes, BIDS, and SSMS.

HUG-MSSQL website launched today

2012-03-13T15:13:00.001+01:00

Some of you might already know that I organize the Hungarian User Group of Microsoft SQL Server (HUG-MSSQL).

Today we launched its website! There will be blogs, news, and events posted regarding everything about MSSQL and MSBI.

It will be in Hungarian, but some of you already translated this blog with free online tools anyway :)

How to install SQL 2012 on Windows 8

2012-03-12T11:04:00.000+01:00

During the weekend, I was playing around with Windows 8 Customer Preview.

First I tried to install SQL Server 2012 RC0 as I had the installer already, but I ran into an issue (missing system CLR types). I decided not to handle this, since SQL Server 2012 RTM is available now. So I tried to install that one instead.

A smaller issue came up again. But Andre Ziegler saved my day with his post in one of the Windows 8 Developer Preview forums.

So, it's better running the script before you start the installation.
You'll have to run cmd as administrator, insert your Windows install disc and type:

dism.exe /online /enable-feature /featurename:NetFX3 /Source:d:\sources\sxs /LimitAccess

Don't forget to check the drive letter of the source and replace it if necessary.

SQL Server 2012 is installed and running now on Windows 8. Thanks Andre :)

A common misunderstanding: VS vs BIDS vs SSMS

2012-02-15T18:17:00.001+01:00

I saw many posts on community sites where people are trying to figure out:

why business intelligence projects cannot be created in VS after installing Visual Studio 2008
why they can't create Visual Studio projects in BIDS
why they don't see VB, C# and other Visual Studio project types, if they have a machine with only BIDS installed
why all the project types are missing from Visual Studio that they had in SSMS, even if they installed SSMS and Visual Studio as well
etc.

The answer is because these three environments are not the same. However they are built on nearly the same base, they are different because

Visual Studio 2008 is a set of development tools.
SSMS uses an isolated Visual Studio Shell, including 3 own project types.
BIDS uses an integrated Visual Studio Shell, and is able to handle the BI related project types. However these projects can be used in VS2008 as well, if both installed.

SSMS and BIDS are not extensions of Visual Studio. They are separate applications running Visual Studio Shell.

For a better understading and more information on the VS Shell and SSMS:

Visual Studio Integration SDK: Visual Studio Shell-Based Applications
SQL Server 2008 R2 Books Online: Files that manage solutions and projects

Regarding BIDS: as of 15th Feb 2012, the Books Online is wrong. It states that BIDS (for SQL Server 2008 and SQL Server 2008 R2) is "Visual Studio 2008 with additional project types".

No, it is not. For those who already read themselves through the links given above, it might be clear.

By the way, BIDS and its BI project types (Reporting Server, Analyis Services, Integration Services and 4 others) can be installed using the SQL Server installation utility. SSMS as well.
A SQL Server Developer Edition costs about $50 per developer.

Hope it helped.

[Update: There is no BIDS for SQL Server 2012. Instead, there is another tool.]

New SSIS Wiki pages for you, and a surprise for me

2012-02-11T00:36:00.000+01:00

I have continued to add content to Technet Wiki this week, and I just spot that I'm on the leaderboard. Very funny - it seems I'm more active this week than Ed Price. He must be on holiday or something :)
OK, I have created less popular articles, but hey - he is an Official Wiki Ninja! :) [LOL, nice acronym.]

Anyway, it's not unthinkable that I'll get higher on this list since there is a lot of things to write and link here.
If you're interested in SSIS transformations, and you would like to see short summaries including references to the details, just follow the link.

SSIS Transformations Wiki: Conditional Split

2012-02-07T07:00:00.001+01:00

Again, I added a new page to Technet Wiki.

The second page is about the Conditional Split transformation.

Gallery script: List all SSIS packages in MSDB (SQL 2008-2012)

2012-02-07T04:55:00.000+01:00

Just uploaded a small contribution to the Technet Gallery. It was created originally by Olaf Helper, however it was working on SQL Server 2005 only.

So this script will list the SSIS packages stored in the MSDB from SQL Server 2008 to 2012, with some additional details:

Package type
Owner name
Encryption
Date of creation
Version
Version comment
Package size in bytes

This script fails on SQL 2005.
I checked the documentation of SQL 2012, and it is subject to change, but it seems that the system table names for the MSDB package won't be modified.

Analysis Server Properties A-Z: CollationName

2012-02-06T19:30:00.009+01:00

The official documentation says...

This property is "A string property that identifies the server collation." according to the documentation.
It is a property which can have a value from the Windows collation list. For those who are familiar only with the SQL engine: Analysis Services supports only Windows collations - no SQL collations are allowed.

How and where it can be set?
In the Language/Collation page of the Analysis Server Properties window:

Analysis Server Properties - Language/Collation page

What happens if the value of this parameter changes?

This change will be applied in the <CollationName> tag of msmdsrv.ini. A change in collation can affect sorting and character comparison. Just for an example, "fruit" and "Fruit" won't be the same if you set a case-sensitive collation (check out this related post of Jamie Thomson).
If you don't specify collation from the client side, then this will be the collation used in the session, which also affects sorting.

Restart needed to apply changes:

No. Your changes are applied immediately.

Category:

N/A. It's a property which is not on the General page.

Default Value:

The value of this property is specified during installation, when Latin1_General_CI_AS is offered by default.

Analysis Server Properties A-Z: AllowedBrowsingFolders

2012-02-06T12:38:00.000+01:00

The official documentation says...

As the official documentation says here, the AllowedBrowsingFolders property is "A string property that defines the names of the folders where logs, backups, and other objects are allowed to be created on the server, in the form of a comma-separated list."

But well, it's a pipe-separated list.

How and where it can be set?
In the General page of the Analysis Server Properties window, after the "Show Advanced (All) Properties" checkbox had been set:

This change will be written into the msmdsrv.ini file, in the <AllowedBrowsingFolders> tag.

What happens if the value of this parameter changes?

For example, when you connect to your instance with SSMS, and add a new database, you will be prompted for the storage location of the new database:

Analysis Services New Database dialog

When you click the ellipsis button, a list will appear where you can select the location of the new Analysis Services database:

Browse for Remote Folder dialog

The AllowedBrowsingFolders property controls this list, so only items defined in the Analysis Services Properties window at this property can be seen here.

Restart needed to apply changes:

No. Your changes are applied immediately.

Category:

Advanced

Default Value:

"\data". This is a relative path to your Analyis Services installation folder.

SSIS Transformations Wiki: Union All

2012-02-06T11:00:00.000+01:00

I just added a new page to Technet Wiki. I plan to add all transformations of SSIS, however someone might be faster :)

The first page is about the Union All transformation, you can read it here.

2012-03-07: SQL Server 2012 Virtual Launch Event

2012-01-25T12:11:00.000+01:00

Yeah, at last we have a date! :)
http://www.sqlserverlaunch.com/ww/Home

Untrusted foreign keys in Reporting Services

2012-01-25T11:49:00.003+01:00

This week one of my colleagues, Janos Berke mentioned a script from Brent Ozar. Since I have a quite fresh sandbox of SQL Server 2008 R2, I became curious what the takeover script will find.
This instance is on a dev box, so SSDE, SSAS, SSIS, and SSRS are installed as well.
The result wasn't shocking, however it was very interesting.
The ReportServer and ReportServerTempDB databases of SSRS contain untrusted foreign keys right after installation.

It is not a big issue, you might think. You might be right. But the fact that the result of Brent Ozar's script includes issues regarding your SSRS databases is very annoying :)
It's easy to correct the issue... Just an example:

ALTER TABLE [ReportServer].[dbo].[Catalog] WITH CHECK CHECK CONSTRAINT [FK_Catalog_ParentID]

..., etc. I hope you can fabricate the rest. (The double "CHECK" is not a typo.)

I reported this on Connect - hopefully no cleanup will be needed in future releases.

...ah, all right, here is a script which will have a result with all necessary statements :)

SELECT 'ALTER TABLE [ReportServer].[' + s.name + '].[' + o.name + '] WITH CHECK CHECK CONSTRAINT [' + i.name + ']' AS Command FROM ReportServer.sys.foreign_keys i INNER JOIN ReportServer.sys.objects o ON i.parent_object_id = o.object_id INNER JOIN ReportServer.sys.schemas s ON o.schema_id = s.schema_id WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0
UNION ALL
SELECT 'ALTER TABLE [ReportServerTempDB].[' + s.name + '].[' + o.name + '] WITH CHECK CHECK CONSTRAINT [' + i.name + ']' AS Command FROM ReportServerTempDB.sys.foreign_keys i INNER JOIN ReportServerTempDB.sys.objects o ON i.parent_object_id = o.object_id INNER JOIN ReportServerTempDB.sys.schemas s ON o.schema_id = s.schema_id WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0

Analysis Server Properties A-Z: BackupDir

2012-01-23T17:50:00.005+01:00

The official documentation says...

As the official documentation says here, the BackupDir property is "A string property that identifies the name of the directory where backup files are created by default, in the event a path is not specified as part of the the Backup command."

How and where it can be set?
In the General page of the Analysis Server Properties window. After connecting to your SSAS instance with SSMS, right-click on its node, and select Properties.

What happens if the value of this parameter changes?

Nothing immediately - except this change will be applied in the <BackupDir> tag of msmdsrv.ini. But later when you want to backup one of your databases with SSMS, you will be prompted for the backup file name:

Backup Database dialog

As you can see, no directory is explicitly set. It means that the backup file will be created in the folder specified in the BackupDir property. When you click the Browse button, a list will appear where you can select the location of the backup file:

Backup SSAS database - Save File As dialog box

The AllowedBrowsingFolders property controls this list, so only items defined in the Analysis Services Properties window at that property can be seen here. If you select an item here, it will be filled in the Backup file editbox:

Backup Database dialog with a directory path applied

Restart needed to apply changes:

Yes. Your changes are applied only after a restart of the Analysis Services instance.

Category:

Basic

Default Value:

None. The default value is empty.

How to set the location of the SSIS top-level folders

2012-01-23T12:17:00.000+01:00

Look for the %ProgramFiles%\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml file. Open it in any editor. By default, it will look like this:

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
    <Folder xsi:type="SqlServerFolder">
      <Name>MSDB</Name>
      <ServerName>.</ServerName>
    </Folder>
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>..\Packages</StorePath>
    </Folder>
</TopLevelFolders>
</DtsServiceConfiguration>

You can change the "." (localhost) to any of your SQL Servers. It's especially useful when this name can't be resolved, e.g. a clustered environment :) Or, if you would like to change the location of the File System folder, just simply replace the "..\Packages" to any other location which is accessible by the Integration Services service account.

You can add, of course, additional top-level folders, if you follow the structure:

    <Folder xsi:type="SqlServerFolder">
      <Name>MSDB-sqlserver2-1567</Name>
      <ServerName>sqlserver2,1567</ServerName>
    </Folder>

Of course, copying a <Folder> block, then changing its content means less effort :)

In this example, the number after the comma is the port (when you don't know the name of the SQL Server instance, or you don't want to use SQL Server Browser because there are only fixed ports set up). If you know the instance name, you can use the servername\instancename format as well.

After you've set up everything, save this xml file, then restart the SSIS service. Your new top-level folders will be available after then.

SQL Server 2012 Licensing

2011-11-08T13:27:00.002+01:00

There are a few changes in the licensing options compared to SQL Server 2008 R2. Just to list the main changes:

There will be no Datacenter Edition of SQL Server 2012. Customers should buy Enterprise Edition instead.
There will be three main editions: Standard Edition, BI Edition, and Enterprise Edition.
There will be no processor-based licenses. There will be core-based licenses and Server/CAL licenses.
BI Edition will be available only with the Server/CAL licensing option. The Enterprise Edition will be available only with the core-based licensing option. Standard will still be available with both.
The price of 4 core licenses of SQL Server 2012 will be around the price of a processor license of 2008.
The core licenses will be sold in as 2-core packs. Minimum amount to order is 4 cores (two 2-core packs) per physical processor.
The price of a CAL increases with around 25%.

Sources: