Why I am excited about SQL Server 2012 (Part 2)

28 03 2012

Earlier this month I published a blog entry on this same sumagenic-custom-soltionsbject.  In honor of the local Minneapolis launch event, I decided to expand the list.  You can find five more reasons I am excited out on Magenic’s blog.

Here is the link and enjoy SQL Server 2012.

http://magenic.com/Blog/WhyIAmExcitedaboutSQLServer2012Part2.aspx





X on XMLA: ii. Basic Structure of XMLA

27 03 2012

image_thumb

The second post in the XMLA series focuses on the basic structure of XMLA.  At its heart, XMLA is XML.  As a result it is usually not a comfortable work environment for most database developers or administrators.  It is no different for Analysis Services developers or administrators.  While a discussion on XML formatting is out of scope for this conversation, I will break down the XML structures used in XMLA so you can see how the basic construction of the language works.

XML Namespace

In XML, a namespace is required to help the tools understand how to interpret the content.  The namespace used is urn:schemas-microsoft-com:xml-analysis. Usage of the namespace is handled at the method level.

XMLA Methods

There are two methods supported in the XMLA standard – Discover and Execute.

Discover

The Discover method will return XML-formatted results.  The Discover statement is similar in function to a SELECT statement in SQL.  Discover can be used to query the data within the multidimensional database as well as server information.

Within the Discover method, the following properties are supported which are implemented as child elements:  RequestType, Properties, and Restrictions.  The RequestType property determines the schema rowset to be returned.  The Properties property is a collection of options that can be implemented which are determined by the RequestType.  Finally, the Restrictions property can be used to restrict the results returned by the Discover Statement.

Here is an example of getting the list of dimensions from the Adventure Works sample cube (available on CodePlex.com).

image

Execute

The Execute method executes statements against the multidimensional database.  The Execute statement can be used to process, deploy and execute MDX statements.  It too returns its results in an XML format, including error messages.

The Execute method supports two properties which are implemented as child elements: Command and Properties.  The Command property contains the actual command that is being implemented.  Properties, like for Discover, enables Command specific properties which can be used to direct the command.

This method is implemented two ways in Management Studio.  In the first example, the syntax is explicitly called.

image

In this example, you can see that Management Studio will wrap execute statements with the Execute and Command elements making it simpler for you to work with Execute methods.

image

SOAP

What I do not discuss in this blog is the SOAP implementation.  As most of us use Management Studio or similar tools which don’t look at the SOAP envelope, I did not cover it.  However, if you need more details on SOAP or on the XMLA standards refer to http://xmla.org for more details.





PASS for Today (and Tomorrow)

20 03 2012

I justPASSMNLogo wanted to plug two events that are free SQL Server training.

First, Thomas LaRock (@sqlrockstar) and Jason Strate (@stratesql) are teaming up to bring you “Choose Your Own Adventure – Performance Tuning”. Join us at the Microsoft Technology Center in Edina, MN or online for this great adventure. This adventure starts at 3:00 PM CDT. More details can be found at http://minnesota.sqlpass.org.

Is that all?, you ask. No. Starting tonight at 0:00 GMT (7:00 PM CDT) is 24 Hours of PASS. Check out the awesome roster of speakers24HOP_Speaker including the likes of Denny Lee from SQLCAT, Marco Russo, and Dejan Sarka. These sessions run through the night and will be close captioned in 15 languages, making it a truly international event.  Oh, did I also mention, I will be speaking at this as well.

Take advantage of these free training opportunities by leaders within the SQL Community.  We all look forward to seeing you at both or either of these events.





Why I am excited about SQL Server 2012

7 03 2012

By now many of you have had the opportunity to hear or see something about the next release of SQL Server.  It is later on March 7, launch day.  I have enjoyed a number of sessions on the SQL Server Launch site.  Many people have talked about what they look forward to in the product, so I thought I would discuss some of the things that excite me.

image

SQL Server Integration Services Improvements

I think the updates to SSIS make the best case for early adoption of SQL Server 2012.  If you are currently using SSIS or are planning to use SSIS, the changes to the platform are significant and reduce the overall development time for enterprise class solutions.  And yes, developers will see many changes that will further help productivity, “beyond rounded corners.”

ColumnStore Indexes

As I work with large data stores for reporting and loading SSAS, this index type will greatly improve performance.  Once again Microsoft has made significant improvements for large, set based query operations.

Enhanced Window Functions

I have done a lot of work with window functions in Oracle lately.  I am thrilled to see similar functionality being added to SQL Server.  This allows for more complex aggregations and query results while not sacrificing performance.  Join me during the 24 Hours of PASS to see these functions in action.

Power View

Wow.  This visualization tool really fills a gap within in Microsoft’s BI offering.  I am excited to see this in operation at customers as this is a game changer for the end user.  The replay capability is both cool and very functional.

Extended Events for SQL Server Analysis Services

While I have not worked with extended events in SQL Server 2008 as much as I would have liked to, the introduction of extended events for SSAS will allow us to follow the path taken by many SQL Server DBAs who have used these events.  This will allow us to monitor SQL Server more closely while minimizing the impact we experiences using SQL Profiler.

These are just a few items within SQL Server 2012 that excite me, what excites you?





Oracle Tips for MSBI Devs #1: Executing an Oracle Stored Proc with No Parameters in SSIS Execute SQL Task

4 03 2012

Originally posted: 3-4-2012

Welcome to a new blog series of tips related to Oracle with a focus on helping those of us who are delivering Microsoft Business Intelligence solutions using Oracle in some way.

The Solution:

In an Execute SQL Task the proper syntax for executing an Oracle stored procedure with no parameters is:

{CALL <schema name>.<stored procedure name>}

image

This works with the OLEDB driver from Oracle. (Do not use the Microsoft Oracle OLEDB driver as it is being deprecated.)

image

The Situation:

The following list highlights the most common errors when trying to get an Oracle procedure to execute.

  • EXEC or EXECUTE – as SQL Server pros we often use EXEC to make the procedure call. When working with Oracle, you need to use CALL
  • Forget the schema name – when using Oracle, you should specify the schema name when calling the procedure. In some cases, you may pick up the schema from the user, but for a consistent pattern and result, you should explicitly specify the schema name.
  • No curly braces – this is the one I did not know and a coworker tracked down some examples of this usage. Enclosing the call in curly braces appears to let the driver know that it is a procedure call and handle it appropriately.
  • Adding a semi-colon – most work with Oracle statements results in a semi-colon to conclude the statement. In this syntax, you will get an error when you conclude your statement with a semi-colon.

Versions:

This tip has been confirmed to work in both SQL Server 2008 R2 Integration Services and SQL Server 2012 Integration Services. The Oracle version tested with is Oracle 11g.

Note on Parameters:

This syntax has not worked consistently with parameters. I will post a followup tip for handling parameters at a later time.








Follow

Get every new post delivered to your Inbox.

Join 694 other followers

%d bloggers like this: