DBPedias Blog

Getting APEX to play with Ref Cursors

Wed, 22 Feb 2012 16:45:09 -0600

It’s that time of year again. Things are a bit tense around the house.
The other morning, I woke up to find that someone had placed a leek in my slippers.
Yes it’s Six Nations time again. England are playing Wales on Saturday. The lovely Debbie is getting into the spirit of the occasion…by exhibiting extreme antagonism to all things English.

Whilst the patriot in me would like to cheer on the Red Rose on Saturday, I have decided that discretion ( or in this case, cowardice) is the better part of valour and will instead, sit quietly in the corner, hoping for a draw. That way, I’ve not sold out completely and next week will be far more pleasant if Wales have not lost.

For those readers who know Rugby Union as merely another one of those odd games that we English let our former colonies win at, all you need to know is, the Welsh take this sport very seriously.

In the meantime, I’m trying to keep a low profile, which means playing around with APEX 4.1.

The heady excitement of discovering the first decent GUI development environment for PL/SQL programmers since Oracle Forms is now starting to be replaced by some of the harsh realities of modern web development.
For example, how can I reuse all those terribly useful functions that return Ref Cursors ?
I mean, they work fine in PHP and various other languages, and APEX itself is written in PL/SQL. Should be easy, shouldn’t it ?

Er, no.

APEX simply refuses to play. “I laugh in the face of your weakly typed Ref Cursor” it seems to say. Clearly, some persuasion is required if I’m not to end up with a lot of code locked away in my APEX application, unusable by any other programming language I might want to use to build a web front-end for my database.
The way to an APEX application’s heart is, as will become apparent, through Pipelined functions.

A Simple Example

Let’s say we have a table called simple. It’s created like this :

CREATE TABLE simple (
	first_name VARCHAR2(30),
	msg VARCHAR2(50))
/

INSERT INTO simple( first_name, msg) VALUES('MIKE', q'[Oh, it's you]')
/

INSERT INTO simple( first_name, msg) VALUES('DEB', 'Hey gorgeous!')
/

COMMIT
/

We’ve also got a function that PHP plays nicely with, which returns a Weakly Typed Ref Cursor :

CREATE OR REPLACE FUNCTION get_simple_fn RETURN SYS_REFCURSOR 
IS
	l_ret_rc SYS_REFCURSOR;
BEGIN
	OPEN l_ret_rc FOR
		SELECT first_name, msg FROM simple;
	RETURN l_ret_rc;
END;
/

We want to reuse this function in our APEX application. For this purpose, we need to turn to a technique more usually associated with ETL, the pipelined function.
To do this, we will need some or all of :

a database Object Type
a database Table Type of the Object type
a pipelined function to act as a wrapper for the Ref Cursor
a giant inflatable plastic daffodil

Deb was looking over my shoulder so I had to add that last item.

The Database Types Method

Create the Database Object Type

CREATE OR REPLACE TYPE simple_typ AS OBJECT (
	first_name VARCHAR2(30),
	msg VARCHAR2(50))
/

And now for the Table of objects type…

CREATE OR REPLACE TYPE simple_tab_typ IS TABLE OF simple_typ
/

Finally, the pipelined function to act as a wrapper…

CREATE OR REPLACE FUNCTION simple_pipe_fn( i_cursor SYS_REFCURSOR)
	RETURN simple_tab_typ PIPELINED IS
	l_row simple_typ := simple_typ(NULL, NULL);
BEGIN
	LOOP
		FETCH i_cursor INTO l_row.first_name, l_row.msg;
		EXIT WHEN i_cursor%NOTFOUND;
		PIPE ROW( l_row);
	END LOOP;
	RETURN;
END;
/

So, the Pipelined function takes a REF CURSOR as an argument and returns a value of the table type we’ve just created.

Now we test this little lot from SQL …

SELECT * 
FROM TABLE(simple_pipe_fn(get_simple_fn)); 

FIRST_NAME MSG 
---------- -------------------- 
MIKE	   Oh, it's you 
DEB	   Hey gorgeous!

Let’s see what APEX makes of all this…

In Application Builder, go to whatever your playground application is and Create Page
I’m going for an Interactive Report.

Page Name is Pipeline Test
Region Name is Simple.

Now to enter the select statement :

Come on, eat you're Ref Cursors or you'll never grow up to be a proper web technology

Click through the rest of the creation wizard and then run it …

There's a good little declarative development environment.

OK, not the most elegant report ever, but it does actually work.

There are one or two things that are a bit unsatisfactory with this approach ( apart from the obvious drawback of having to persuade a PL/SQL development tool to play with a Ref Cursor).
First off, this database type business. Well, it’s not exactly robust, is it. If I change the table definition, I’ll need to remember to change the object type as well.

The other minor niggle is, well, there does seem to be quite a bit of type-ing. Ahem.
Moving swiftly on, let’s see if we can solve both of these issues in one fell swoop….

And now for something completely different…

Right, we’re going to drop those boring fuddy-duddy database types we’ve just created and use a PL/SQL package instead.

DROP TYPE simple_tab_typ 
/ 

Type dropped 

DROP TYPE simple_typ 
/

Type dropped

Now to replace these with a package header :

CREATE OR REPLACE PACKAGE pipe_types_pkg AS
	TYPE simple_tab_typ IS TABLE OF simple%ROWTYPE;
END pipe_types_pkg;
/

As we’ve declared this type in a package, we can use an anchored declaration to base it on the table.
If the table structure changes, so will the type.

Finally, we need to change the pipelined function to reference the type we’ve declared in the package :

CREATE OR REPLACE FUNCTION simple_pipe_fn( i_cursor SYS_REFCURSOR)
	RETURN pipe_types_pkg.simple_tab_typ PIPELINED IS
	l_row simple%ROWTYPE;
BEGIN
	LOOP
		FETCH i_cursor INTO l_row.first_name, l_row.msg;
		EXIT WHEN i_cursor%NOTFOUND;
		PIPE ROW( l_row);
	END LOOP;
	RETURN;
END;
/

If we now test this again …

SQL> SELECT * FROM TABLE(simple_pipe_fn(get_simple_fn)); 

FIRST_NAME MSG 
---------- -------------------- 
MIKE	   Oh, it's you 
DEB	   Hey gorgeous! 

SQL>

It must be said that, even though we haven’t explicitly created a database type , Oracle has taken matters into it’s own hands

SELECT object_name, object_type 
FROM user_objects 
WHERE TRUNC(created) = TRUNC(SYSDATE)
/
SYS_PLSQL_27148_21_1	       TYPE 
SYS_PLSQL_27148_DUMMY_1        TYPE 
SYS_PLSQL_27158_9_1	       TYPE 
SYS_PLSQL_27158_DUMMY_1        TYPE 
PIPE_TYPES_PKG		       PACKAGE 
SIMPLE_PIPE_FN		       FUNCTION 
GET_SIMPLE_FN		       FUNCTION 
SIMPLE			       TABLE

Hmmm, not sure why it’s felt the need to define four types on my behalf.
On the plus side, we can leave the database to look after it’s own types. Ours is all future-proofed and low maintenance.

Of course, you have the option of declaring all of your required types in a single package header, or leaving them in the packages in which your Ref Cursor functions reside.

To prove a point, let’s see what happens if we make a change to the simple table :

ALTER TABLE simple MODIFY (
    msg VARCHAR2(100))
/

Let’s see how our package version of the code copes :

SELECT * 
FROM TABLE( simple_pipe_fn( get_simple_fn)) 
/ 

FIRST_NAME MSG 
---------- -------------------- 
MIKE	   Oh, it's you 
DEB	   Hey gorgeous! 

SQL>

Running the report in APEX shows a similar lack of concern for the change in the table structure.

Whilst all this does mean that you have to create an API for the web API you’ve already got, it does mean that you can re-use the code in APEX.

Deb has just wandered by humming “Land of My Fathers” which I will take as my queue to run away and hide in the cupboard under the stairs for a bit.

Filed under: Oracle, OraDBPedia Syndication, PL/SQL, SQL Tagged: apex 4.1, create type, create type as object, create type is table of, pipelined functions, REF CURSOR, sys_refcursor, weakly typed ref cursor

Database Mirroring Failover Considerations

Wed, 22 Feb 2012 11:33:44 -0600

Database mirroring is a great option for many disaster scenarios. If using a witness server automated failovers can be achieved. However, just like any other disaster recovery solution database mirroring should be tested. Before you execute your first test of failing over a database from one server to another you may want to take into consideration a few different items.

User Accounts

Problem – When a new SQL Server login is created on a server, a row is added to a system table in the master database. The Unique Identifier on that row of data is called the SID. The SID is unique based on the account and the server on which the login is created.

When a login is then added to a database as a user, and the user is granted permissions to work with data inside a database, those permissions are added to the individual databases. Logins are related to the server while users are related to the database. The relationship between the login and the user are based on the SID.

When a database mirror becomes the Principal on a different server, the users that are in that database remain due to the user account information being stored in the user database. The relationship from the User in the database to the Login on the server is broken. This is referred to as an orphaned user.

Solution- There are a couple ways of correcting orphaned user. One method is to correct an orphaned user using the sp_change_users_login stored procedure. I don’t use this method because it requires either additional activity on your side by either running that procedure manually or via SQL Server Agent job and, depending on how you have it configured, may take some time before those accounts can log on to the database.

The solution I choose to use is by creating all the user accounts on the mirror with the same SID as they have on the Principal. By having the same Login SID on both the mirror and the Principal, the accounts relationship remains. To create a new login by declaring the SID use the Create Login statement.

SQL Server Agent Jobs

Problem – Many servers have jobs that reach into databases to manipulate data, or have automated backups that are scheduled via the SQL Server Agent Service. On mirrored versions of the database not marked as being online, these jobs will fail if that is not taken into consideration. Some may consider just adding the job to the server and letting it run and fail and when the mirror becomes the Principal, the jobs will already be scheduled and ready when the next execution time comes around. (I am not a big fan of this, if for nothing else because I monitor for failed jobs and a false positive will eventually be ignored so when the job is needed to run and fails the notification may be missed.)

Solution – Kevin Cox and Glenn Berry, two SQL Server Professionals that I respect a lot posted a solution on the SQL Cat site that solves this issue. In short… Add a category of jobs to your server that you can assign all your jobs to. Once your database status changes the jobs can be enabled.

Indexing

Problem – Running a reindex on a database that is mirrored can create a number of transactions that need to be propagated over to the mirror. On servers that are located in different physical environments, an additional load can be placed on the bandwidth, and the server that is the mirrored.

Solution – It is important to know the impact of database mirroring on your database and the servers that are hosting those databases. In the situation that I am thinking about the mirror server did not have the same hardware as the Principal server. This meant that indexing was a bigger load on the mirror than it was the Principal.

One of the solution options is to make sure that only the indexes which need to be reindexed are reindexed. This can be done by checking the fragmentation before issuing the index statements.

2012 MVP Summit Preview

Wed, 22 Feb 2012 10:35:18 -0600

Next week I will be attending the 2012 Microsoft MVP Summit. This will be my third Summit but it feels like my first because there are going to be a lot of new faces there for the first time. That’s why I wanted to take the time to remind everyone that the MVP Summit is essentially like Fight Club, but with less soap.

Not only is the MVP Summit crowded in secrecy, but so is the process that Microsoft uses to even select someone as an MVP. Oh sure, they list some details on their website but the process itself is rather a mystery even to those of us in the program. Many days I find myself channeling my inner Groucho Marx as I wonder why I am so lucky to be able to mingle with so many great people. The bottom line is this: no one can tell you what it takes to be an MVP.

Everything about the MVP program, especially the Summit, is clouded in secrecy just like the club started by Tyler Durden. In fact, let’s look at some of the rules for the MVP Summit that were emailed to me earlier this week:

Rules of MVP Summit

You do not talk about the MVP Summit.
You do not talk about the MVP Summit.
When someone yells “JAVA” or goes limp, or taps out, the session is over.
Only two MVP’s to a hotel room.
One session at a time.
No Twitter, no Twitpic, and no G+.
Sidebars go on as long as they have to.
If this is your first time at the MVP Summit, you have to try the salmon.

After reading those rules it just seemed like a natural fit. Let’s get to the preview!

“The first rule of Fight Club is: you do not talk about Fight Club. The second rule of Fight Club is: you DO NOT talk about Fight Club!”

Attendees at the MVP Summit are under a strict non-disclosure agreement with regards to the materials they will be shown during the sessions. The NDA we sign covers us at all times throughout the year; not just the Summit. You may not know it but people are asked to leave the MVP program periodically for violating their NDA. Wait…I wonder if I just violated my NDA by telling you about how others have violated their NDA? Oh, the irony.

The bottom line here is that we are asked to provide feedback on features and products that have not gone to market yet. Discussing these items is not really an option for those that wish the remain in the program.

“You wake up at Seatac, SFO, LAX. You wake up at O’Hare, Dallas-Fort Worth, BWI. Pacific, mountain, central. Lose an hour, gain an hour. This is your life, and it’s ending one minute at a time. You wake up at Air Harbor International. If you wake up at a different time, in a different place, could you wake up as a different person?“

This quote covers a couple of things. It gives a glimpse into the grind that is travel. Fortunately I do not travel as frequently as Tyler did, but even when I do travel things can be difficult. Just being away from the family these days gets harder and harder. This quote also touches upon how fleeting our lives are, ending one minute at a time. In this case, my current MVP status is ending one minute at a time. I am up for renewal in April which means if I don’t get renewed then I may not get another chance to attend a Summit. So for me it is a no-brainer to attend.

“You had to give it to him: he had a plan. And it started to make sense, in a Tyler sort of way. No fear. No distractions. The ability to let that which does not matter truly slide.”

The whole point for the Summit is for Microsoft to get feedback from the most active members of their communities. Now, think about what it must be like to be in Microsoft’s shoes. A year ago, some member of the SQL development team was given an assignment. They have been working on something for months. They slaved to get some feature put into SQL 2012 that will be shown to us during a session. And when we see it we will likely be underwhelmed, or critical of the implementation chosen. Some of us try to be polite about our feelings; others are a bit more…direct in their approach.

People and companies that build software have this ability about them to take suggestions or criticisms, shrug off the ones that do not matter, and instead focus on the ones they recognize as important. And the good companies do that very well.

“In that case, sir, may I advise against the lady eating clam chowder?”

I have heard a lot about the food at the Summit, and last year I was also able to read about it on Twitter. I have no idea what is on the menu this year, but I am looking forward to breaking bread and bacon with some good friends for a few days.

“He was full of pep. Must’ve had his grande-latte enema.”

The MVP Summit is technically not in Seattle, but it is certainly close enough (Bellevue) for us to have a reference to coffee somehow. I have no idea at what point if and when they will be offering enemas at this years Summit, as I didn’t see that on my schedule builder. It may just be a service offered at the Hyatt for their Platinum members.

“Can I get the icon in cornflower blue?”

This quote is for the inane requests that all software companies can receive. I have seen and heard the crazy requests that the product team is given during the Summit. I would get them all the time as a DBA. My personal favorite was the time I was asked to increase the size of the data page for one database and to “let them know when it is complete” (not if it was possible, just when I finished getting it done). The point here is that people are going to ask for some crazy things, as well as for things that have no functional purpose whatsoever. Having icons in certain colors really doesn’t matter. Being forced to see the blue-on-blue in PBM does matter, but there may be little to nothing that will be done about it. But, hey, we are there to provide feedback, right?

“Uh, well… You’re not gonna believe this…”

Besides feedback the product and development teams get a lot of support requests in the form of sidebars. I know that I do not think twice about asking for help with anything “strange” that I have been seeing. I have seen times where someone is asking for feedback during a session and someone raises their hand and starts in with a “why are you wasting your time with this as opposed to fixing this other thing?” Or, it could be the case where we are sharing a meal and we find out that we are talking with someone who works on the product team and we start in with a “you won’t believe the stuff I have seen in my shop.” Microsoft is looking for feedback from real world scenarios, and I am always happy to oblige where I can.

“Without pain, without sacrifice, we would have nothing.”

This quote is perfect for recognizing the effort given by a lot of different people. The amount of effort that the product and development teams put into their work is nothing short of amazing. It takes a lot of hard work, a lot of effort, and a lot of coordination in order to get things done, specifically for shipping a product as complex as SQL Server. And it also warrants mentioning the event itself. Putting on the Summit requires a great deal of effort as well, and should be recognized.

“Would you like to say a few words to mark the occasion?”

I am arriving late in the afternoon on Monday and leaving Friday afternoon. I live on the East Coast and getting home from Seattle makes for a long day (or a long day and night). I try to time my arrival and departure as best as possible to attend sessions and every year it seems as if extra sessions are added to the agenda after I have already booked my travel. It’s frustrating but I can’t complain because just being invited to the MVP Summit is like winning the lottery and I would have to be an idiot to complain about not being able to have everything work in my favor.

I did notice that there isn’t a keynote on the agenda for this year. I’m not sure why that is the case, and perhaps one has been added recently.

“What do you do for a living? Why? So you can pretend like you’re interested?”

This quote speaks directly to the networking aspect of the MVP Summit. I have no idea how many MVP’s there are in the world, but I do know that MVP’s exist for just about everything. xBox, Zune, OpsMgr, SQL, and even Visio have people recognize as MVP’s. That means I am going to have the opportunity to meet a lot of new people outside of SQL Community in addition to the people inside the SQL team itself. If you are reading this and heading to the MVP Summit, track me down and say hello, I would love to meet as many new people as possible.

And if you are every uncertain about something, or need some direction in life, you can always stop and ponder the question: “What would Tyler Durden do?”

Can I get the icon in cornflower blue?

2012 MVP Summit Preview is a post from: SQLRockstar | Thomas LaRock

Join Denny Cherry (@mrdenny) and me for two days of SQL instruction, training, and wine tasting in the California sunshine this May for $799.

Want Another Reason to Hate iTunes?

Wed, 22 Feb 2012 10:13:39 -0600

I’m not one to whine. Really. I’m totally not a whiner. However, I’m going to sound like one with this statement…

I fricken HATE iTunes.

There, I said it. I’m already starting to feel better.

Playing on Elisebeth Barrett Browning and her fantastic poem, “How do I love thee? Let me count the ways.”, I’m going to count some ways that iTunes is filling me with inhuman, Hulk-like rage:

Ridiculously frequent updates. Not the “Update Tuesday” sort of thing we get from Microsoft, but the “I’m going to interrupt you all the time, any time sort of upda…” – hold on, iTunes wants me to update it.
Genius. You’re an idiot.
Shuffle. You don’t.
Home Sharing. If by “sharing”, you mean making it impossible to get music onto other devices without copying and moving it manually, you’re perfect.
Relentless Focus on Making a Buck. Yeah, I know that Apple is the biggest capitalized company since the Iron Age and that they had a better Q4 in 2011 than the rest of humanity combined. But couldn’t you give it a rest for plain ol’ music, especially if you’re a user who still uses CDs? It seems like they’d monetize punctuation marks if they had the opportunity, for Pete’s sake!
Duplicate Songs in Library. Take a few minutes and Google on ‘Remove duplicates from iTunes library’. (See it on Let Me Google That for You). People are about to grab torches and pitchforks on this one. WHY ISN’T THIS BUILT IN?!? WHY DOESN’T THE TOOL HANDLE THIS?!!!? Someone please make an app for this!
Duplicate Songs on Disk. So my last disk backup took a lot longer than usual. Hmmm, I wonder why? So I looked at my backup info and saw this (image). Just in case you don’t see what I saw – 70.3 GB of hard disk sucked down for music. Keep in mind that I actually have only about 6 GB of music files. So, in the vernacular, W-T-F?!?

I’m now going to spend hours of precious time burning down iTunes and rebuilding my library. And I must’ve already done this two or three times in the past few years already. Look, Apple, I don’t fricken need this. I need troublefree music that doesn’t require an IT certificate to manage. Didn’t Apple used to be the company where everything was slap-your-mammy easy? Well, it ain’t so now. And it’s pissin’ me off.

I’m sure that there are other things about this product that makes your blood boil. Lay it on me! I want to hear your rant!

Enjoy!

-Kev

-Follow me on Twitter!

SQL Server 2012 (“Denali”): FileTable

Wed, 22 Feb 2012 10:00:47 -0600

FileTable is a new feature in SQL Server 2012 that is built on top of SQL Server FILESTREAM technology, which allows BLOB data to be stored as individual files separate from a database’s data files. Interactions with the FileStream files took place only through T-SQL or through code which engaged with OpenSqlFileStream API.

FileTable goes one step further in adding support for the Windows file namespace and compatibility with Windows applications to the file data stored in SQL Server, accessing the files as if they were stored in the file system. It also employs a familiar, hierarchical folder structure, and includes the storage of file attributes, such as created date and modified date.

FileTable lets an application integrate its storage and data management components, and provides integrated SQL Server services – including full-text search and semantic search – over unstructured data and metadata.

More info:

Beyond Relational – Installing SQL Server FileTable

Denali CTP 3 – How to Set Up the FileTable and access the unstructured data from SQL Server

Creating Your First FileTable in SQL Server Denali CTP3

SQL Server 2012 FileTables in T-SQL part 1: functions and methods

SQL Server 2012 FileTables in T-SQL part 2: new rows

SQL Server 2012 FileTable – Part 1

SQL Server 2012 FileTable – Part 2

Video Microsoft SQL Server Beyond Relational Landscape: Current and Future

SQL SERVER – T-SQL Constructs – Declaration and Initialization – SQL in Sixty Seconds #003 – Video

Tue, 21 Feb 2012 19:30:36 -0600

We got tremendous response to our very first video of SQL in Sixty Seconds #001 and SQL in Sixty Seconds #002. We talked about how to convert Subquery to CTE and Error and Reaction. My co-authors Vinod Kumar and Rick Morelan, we often came across very interesting and useful tips which we believe would be helpful to readers.

In today’s SQL in Sixty Seconds Vinod Kumar has presented very visual reach concept of SQL Server. T-SQL has many enhancements which are less explored. In this quick video we learn how T-SQL Constructions works. We will explore Declaration and Initialization of T-SQL Constructions. We can indeed improve our efficiency using this kind of simple tricks. I strongly suggest that all of us should keep this kind of tricks in our toolbox.

More on Errors:
Declare and Assign Variable in Single Statement
Declare Multiple Variables in One Statement

I encourage you to submit your ideas for SQL in Sixty Seconds. We will try to accommodate as many as we can. Do you know any such tricks

Reference: Pinal Dave (http://blog.sqlauthority.com)

Filed under: Database, Pinal Dave, PostADay, SQL, SQL Authority, SQL in Sixty Seconds, SQL Query, SQL Scripts, SQL Tips and Tricks, SQLServer, T SQL, Video

Julie Presents SSIS 2012 A Fresh Start in Columbia

Tue, 21 Feb 2012 17:20:36 -0600

Thanks for attending tonight. Here is the link to the slidedeck.

Using the MDX Script Debugger in BIDS

Tue, 21 Feb 2012 15:56:45 -0600

Almost every time I write MDX calculations, I end up using scoped assignments – it’s by far the easiest way to control where your calculations work inside a cube. However making sure that your scoped assignments work in the places that they’re meant to work and don’t overwrite each other can be very tricky indeed (for a brief introduction to the subject and these problems, see this session I gave at SQLBits a year or so ago), so in this post I’m going to show you how you can use the MDX Script Debugger in BIDS to help you do this.

Despite its name the MDX Script Debugger doesn’t actually help you to debug individual MDX expressions or calculations. What it does is clear the MDX Script of the cube then allow you to step through each statement in the MDX Script, applying them one after the other, so you can see the cumulative effect on a query. This is only really useful when you’re working with scoped assignments because it allows you to see which cells in your query are changed with each successive assignment.

To illustrate, let’s use the Adventure Works cube. Comment out everything on the MDX Script (ie what’s on the Calculations tab) except the Calculate statement and add the following code to the bottom:

CREATE MEMBER CURRENTCUBE.MEASURES.DEMO AS 1;

SCOPE(MEASURES.DEMO);
    SCOPE([Date].[Date].MEMBERS, [Date].[Calendar Quarter].[Calendar Quarter].MEMBERS);
        THIS=2;
    END SCOPE;
    SCOPE([Date].[Calendar Year].[Calendar Year].MEMBERS);
        THIS=3;
    END SCOPE;
END SCOPE;

SCOPE([Measures].[Internet Sales Amount]);
    SCOPE([Date].[Date].MEMBERS, [Date].[Calendar Quarter].[Calendar Quarter].MEMBERS);
        THIS=2;
    END SCOPE;
    SCOPE([Date].[Calendar Year].[Calendar Year].MEMBERS);
        THIS=3;
    END SCOPE;
END SCOPE;

Then deploy your database and go to Debug menu in BIDS and click Start Debugging:

The MDX Debugger screen will then be displayed:

In the top pane you can see the Calculate statement highlighted – this is the point in the script that has been reached as you step through the code. In the bottom pane you have a browser control where you can construct a query plus four panes where you can enter your own hand-written MDX queries.

In the browser control, drag the Internet Sales Amount measure onto columns and the Calendar hierarchy from the Date dimension onto rows, until you see something like this:

You don’t see any data at the moment because the Calculate statement hasn’t been executed yet (see here for more details on what the Calculate statement does). If you hit F10 to step to the next statement in the MDX Script you’ll see the values for Internet Sales Amount appear because the Calculate statement has now been executed:

If you hit F10 again, the calculated member DEMO will be created and you can now drag it into the browser; at this point you’ll see it always returns the value 1 because none of the scoped assignments have been executed yet:

Hit F10 again until you reach the first END SCOPE statement and you’ll see the following:

You can see that MEASURES.DEMO now returns 2 for the Date, Month and Quarter level as a result of this first assignment; you can also see that only the values that have been affected by this assignment have been changed. Hit F10 some more to execute the second assignment and you’ll see that DEMO returns 3 at the Year level and the affected cells are again highlighted:

Notice how, in this case, because you’re scoping on a calculated measure only the cells you scoped on have their values changed. This is in contrast with scoped assignments on regular measures: because regular measures aggregate up, scoping on a regular measure not only affects the values in the cells you scoped on, but those values will then also be aggregated up though the cube.

To show what does happen when you scope on a regular measure, look at the next set of scoped assignments on the Internet Sales Amount measure. The first assignment scopes on the Date, Month and Quarter levels and sets their values to 2; however the Year level values now show the aggregated totals of all the quarters in the year, so if there are four quarters in a year then the year will show 4 * 2 = 8. The All level total is also similarly affected.

The final assignment sets the Year totals to 3 for the Year level; this overwrites the values that have been previously aggregated up from the Quarter level, and the Year level values are again aggregated up to the All level:

Hit F10 some more and you’ll reach the end of the MDX Script, whereupon you’ll go back to the beginning and can start all this again. Go to the Debug menu and click Stop Debugging to finish. Useful bit of functionality, isn’t it? Certainly one of the least-known features of BIDS too.

One last point – if you try to use the Debugger and hit the infamous SSAS Locale Identifier bug, check out Boyan Penev’s post here on how to solve this issue.

Support me and Team #SQLPlunge for the Special Olympics

Tue, 21 Feb 2012 11:00:00 -0600

On March 3, 2012 I will be jumping into a hole in the ice in Lake Calhoun to raise money for the Special Olympics. One item on my bucket list will be crossed off – I don’t think I want to get good at this, but I do want to try it.

The big reason we are doing this is to raise money for the Special Olympics, this has always been one of my favorite charities. I love sports and competing in anything – this organization gives people with disabilities the opportunity to experience the thrill of competition and the fun of learning a skill and working on a craft.

This is where you come in. I need your help raising money. If you wish to donate to the cause, you can do so here.

Here are the facts about the event:

Who - Tracy McKibben* (B/T), Jason Strate (B/T), Afroza Ahmed and I make up Team #SQL Plunge

Here is Team #SQLPlunge’s page

*denotes team captain

What – Jumping in a hole in the ice.

Why – I’m still not sure, Tracy tweeted something about not knowing if he should do it this year, I responded that with these temperatures, this would be an easy year to do it. The next thing I know…

Where – Lake Calhoun, Minneapolis

When – March 3rd, 2012

Here is more info on the Minneapolis Polar Plunge.

Other Info – My alma matter participated in an event this year – Gusties Support Special Olympics Minnesota

This is just cool – not what we are doing, but just too cool not to share.

Still Using Windows Logins for your Databases? You’re Doing it Wrong

Tue, 21 Feb 2012 10:58:44 -0600

If your DBA is still adding Windows logins to your database servers then they are doing it wrong.

Using logins (versus Windows groups) is an inefficient method that hasn’t been considered a favorable practice for almost ten years. Oh…sure…it works. I won’t argue that point. But if you are administering hundreds of database servers and you are using logins instead of groups then you are making your environment more complex than it needs to be. When shops exploded with exponential growth in the past decade Microsoft recognized this by publishing guidelines around security best practices that reflect the preference for using groups. Here’s one from 2005 that states simply at the top:

“Because it is inefficient to maintain user accounts directly, assigning permissions on a user basis should be the exception.”

Exception. As in “not the norm”. And say what you want about SQL 2000, but even they knew that using groups was the way to a simpler life.

Here are three reasons why you should be using Windows groups.

Audit compliance

Since those best practices guidelines were published there has been an ever tightening of a grip on systems with regards to security and audits. Most companies that have to be in compliance with government legislation of some type will prefer to use Windows groups to access there database servers. Why? Three words: separation of duties.

The DBA should not be the person that is adding or removing individuals from groups within Active Directory. Those actions should be handled by a security team, and only after being given an approval by the IT custodian (or manager) responsible for the application (also, not the DBA). In other words, security in your company should have two parts. One part is handled by the DBA who, working with the database developers, creates the necessary roles, schemas, and Windows group logins on the database instance. The other part is the security team who controls the people added (or removed) from the Active Directory group(s) so that the users can access the applications as needed.

That is the separation: the DBA doesn’t control the users in the groups, just the structure the groups are allowed to access. Likewise the security team has no say in how the structure inside the database instance is built, they just fulfill the requests for access after the IT custodian has approved.

Reduce your administrative overhead

Besides the likely compliance issues you will face, if you are adding in Windows logins by hand your actions will not be able to scale to hundreds of instances. I’m certain that someone will leave a comment telling me that Powershell can get the job done with just a few lines of code. This would be where I remind people that automation is a wonderful way to make mistakes faster than ever before.

Let’s consider the case of a developer who needs different levels of access for development, test, QA, and production servers. That would mean you need to keep four copies of your scripts for each environment, and server, and person. That is a lot of scripts to maintain and troubleshoot. I can’t even imagine trying to add or modify those scripts as needed.

Even with just a handful of servers the idea of trying to maintain the correct permissions on a per-user level is more overhead than I would want. Every time a new person joins the company I would need to recreate all the permissions necessary for that person to access the databases they need. And the minute a person gets an ‘access denied’ error message returned it would be in my lap to figure out what was missed.

I can’t imagine why anyone would prefer to spend their time making things more difficult than they need to be. With Windows groups your could have all the permissions, roles, and groups defined in advance and then the security team (remember them?) could add and remove users as needed.

Less mess to clean up afterwards

One area I see customers and clients struggle with centers around the removal of logins after a person has left the company or even changed jobs within the company. When there is no defined removal process the end result is a glut of logins defined on an instance of SQL Server and most DBAs have no idea who should stay or who should go.

When companies have a defined access process that is tied to Windows groups it helps to avoid the issue of having logins lingering around for months and possibly years. We’re talking about one step versus hundreds of steps. Which would you prefer?

If you think not having separation of duties would make an auditor have cause for concern just sit across from the table and look them in the eye and say “those logins have been there for years, we rarely go back to remove them, we wouldn’t know which ones are no longer valid”. Good times.

Exceptions are not the rule

There are always going to be exceptions to using Windows groups. And for those exceptions you will have the extra overhead, you will need a defined cleanup process, and you will likely need to file for an audit exception. But they are exceptions, not the rule. Exceptions always introduce complexity into your environment. Unless you enjoy having an environment more complex than necessary, unless you enjoy having failed audits, you should start using Windows groups as your standard.

If you only have a handful of servers, and your shop doesn’t need to comply with audit guidelines, then you can get away with continuing to use Windows logins versus groups. But at some point you will cross a threshold where the overhead for maintaining your environment outweighs the usefulness. And it is then, at the precise moment, when you will look back and say “crap, I should have listened.”

You’ll thank me then. And you’re welcome.

Still Using Windows Logins for your Databases? You’re Doing it Wrong is a post from: SQLRockstar | Thomas LaRock

Join Denny Cherry (@mrdenny) and me for two days of SQL instruction, training, and wine tasting in the California sunshine this May for $799.

Please vote for my SQLRally session

Tue, 21 Feb 2012 08:00:28 -0600

I’m hoping to do a presentation at SQLRally in Dallas on May 10-11, but I need your help!

There is a Community Vote for the remaining 20 sessions, so I would greatly appreciate it if you would go to http://www.sqlpass.org/sqlrally/2012/dallas/CommunityChoice.aspx and vote for my session (described below). You have to be a PASS member to vote, but if you are not, it is free to register. Thanks for your help!

Scaling SQL Server to HUNDREDS of Terabytes OR Overview of Microsoft Appliances

Learn how SQL Server 2008 can scale to HUNDREDS of terabytes for BI/DW solutions. This session will focus on Fast Track Solutions and Appliances, Reference Architectures, and Parallel Data Warehousing (PDW). Included will be performance numbers and lessons learned on the very first production PDW deployment in the world and how a successful BI solution was built on top of it using SSAS.

Learn about all the different appliances and how they can save you a tremendous amount of time and money instead of building your own: HP Business Decision Appliance (BDA), HP Business Data Warehouse Appliance (BDW), HP Enterprise Data Warehouse Appliance (EDW), and HP Database Consolidation Appliance (DBC).

If you are involved in the decision-making in your company for purchasing one or more servers to be used for SQL Server, this session will make you aware that there are better options outside of the usual ordering of a server and internally installing the hardware, OS, and SQL Server. And then hoping it will handle your databases!

TNS-12508: TNS:listener could not resolve the COMMAND given

Tue, 21 Feb 2012 06:25:54 -0600

LSNRCTL> set  log_directory /u01/app/oracle/product/11.2.0.2/dbhome_2/network/admin/lsnrlog

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxxxxxxxxx)(PORT=1521)))

TNS-12508: TNS:listener could not resolve the COMMAND given

the above issues happen while i’m trying to set log directory for listener. But It refused the comments, Why it’s refused the commands means I enabled the ADMIN_RESTRICTIONS parameter option in listener.ora file. So that reason, I cann’t set command used as administrator level, It restircted for executing the following set command options like SET TRC_FILE, SET TRC_DIRECTORY,SET TRC_LEVEL, SET LOG_DIRECTORY,SET LOG_STATUS,SET CURRENT_LISTENER,SET STARTUP_TIME.

When enabled, ADMIN_RESTRICTIONS instructs the Listener not to accept any administrative commands from lsnrctl. Instead, an administrator must log in to the Listener’s host OS and make configuration changes directly in listener.ora.

So In this case, We should disable the ADMIN_RESTRICTIONS_LISTENER = OFF and after reload the listener.

This parameter used for preventing from hackers who are trying to take the listener trace file through remote and It refused the set commands…

Creating Your First SSAS 2012 Tabular Project

Tue, 21 Feb 2012 06:00:00 -0600

No matter how simple it is, new things come with some confusion; at least until you get used to it. Analysis Services 2012 Tabular is no different. This post will look at steps to create your first Tabular project.

Let’s start with firing up SQL Server Data Tools located under Microsoft SQL Server 2012 RC0. I’ve selected to create a new project.

In the above image, I’ve selected Analysis Services Tabular Project.

You might see a error message similar to the one below. This happens when the server instance specified in the Workspace Server property doesn’t have an analysis services running in tabular mode. For instance, in this example the name of my instance is localhost\SQL110, but I purposefully changed it to an incorrect name to show this error.

To fix this, I’ve changed Workspace Server to the correct one from the properties of model.bim

Importing Data

The Model option located on the top allows you to create model files. You’ll begin with importing data from a source.

The next screen lets you select a type of data source. I’ve selected Microsoft SQL Server. In the next screen, you choose a server name and database name as the source.

You’ll provide impersonation information in the next screen. This is important because, analysis services uses this account to connect to the data source. So, make sure that this account has required permissions on both the server instances: data source and analysis services.

Next screen asks how you want to import the data. You can either choose from a list of tables or view, or specify a query to retrieve the data. I’ve opted to select from a list.

From the list of tables and views in the below image, I’ve selected FactInternetSales, DimProduct, DimProductSubCategory, DimProductCategory, DimCustomer, and DimGeography.

Clicking finish will complete by the data import and the next screen displays a status of the tables and views that were imported.

Toggle to the diagram view to see the tables and relationships among the objects in the model. In the below image, you’ll notice the wizard identifies the relationships in the data source.

Toggle to the grid view to see the data that was imported from data source to the model. This interface looks similar or Excel workbooks with one tab for each table in the data source.

Create Measures

The first thing you should do after importing data is to create measures. I’ll select Sales Amount and choose Sum from the measure drop down in the following image.

I’ve repeated the same steps for Tax Amount, to create another measure.

Deploy To Target Server

Before deploying to the server, take a few seconds to verify the target server properties. Target Analysis Services must be running in tabular mode.

In the below image, I’ve selected to deploy the model.

Browse Your First Tabular Model

I selected Analyze in Excel option located on the top. An excel pivot table with a connection to the model you just deployed allows you to analyze data in Excel.

Conclusion

Well, Well, Well. I know this has been a lengthy post. So I’ll let you go with no further ado, but please use the comments below should you have any questions or comments.

~Sam

TNS-01251: Cannot set trace/log directory under ADR

Tue, 21 Feb 2012 04:17:57 -0600

When you’re trying to set new listener log directory, you may face a same error below

LSNRCTL> set log_directory /u01/app/oracle/product/11.2.0.2/dbhome_2/network/admin/lsnr_log
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxx)(PORT=1521)))

TNS-01251: Cannot set trace/log directory under ADR

Solution :

If the parameter DIAG_ADR_ENABLED_listenername is set to ON in the listener.ora file, the trace and log file should be located under ADRBASE_listenername location. If ADR is enabled, all listener related trace file and log file stored in under ADR base location. If you don’t want to store there, you can disable ADR.

In listener.ora file

DIAG_ADR_ENABLED_LISTENER = OFF

reload the listener.

LSNRCTL> reload

LSNRCTL> set log_directory /u01/app/oracle/product/11.2.0.2/dbhome_2/network/admin/lsnr_log
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxx)(PORT=1521)))
LISTENER parameter "log_directory" set to /u01/app/oracle/product/11.2.0.2/dbhome_2/network/admin/lsnr_log The command completed successfully

Calling ProcessData in SSIS to incrementally load data into tabular partitions

Mon, 20 Feb 2012 22:33:26 -0600

Previously, I showed a pattern for incrementally loading data by creating a partition, doing a ProcessData on the partition, then doing a ProcessRecalc on the database all in a single transaction. It is possible to do the same thing in an Integration Services package. Let’s take a look.

We cannot use the Analysis Services Processing task for this chore. This is because the task was not updated for SQL Server 2012, so ProcessRecalc is not an option. Therefore, if we want to use a built in Analysis Services task, we must use the Analysis Services Execute DDL task to do this work.

We also need to do this work transactionally. Even though it is is something of a design antipattern to use transactions in an SSIS package, we must use one because we cannot leave the database in an unqueryable state while processing (recall that ProcessData renders a database unqueryable until a Recalc is performed). In theory, you could try to use the built-in transaction support for SSIS, but transactions do not work properly with Analysis Services. The easiest way to send the commands in a single transaction is to use one Analysis Services Execute DDL task and wrap all three commands we need inside of an XMLA Batch statement.

Imagine we want to create a partition for the year 1812 on the DemographicFacts table on the Hans Rosling demo project database. Here is how to do the incremental data load:

From SSDT, create a new Integration Services project.
Right click in the Connection Managers pane, add a new connection to your tabular server.
Drop the Analysis Services Execute DDL task into the Control Flow area (it is under “Other Tasks” in the SSIS Toolbox).
Double-click to configure the task.
In the dialog box that appears, click DDL.
In the DDL properties, set the Connection property to the connection manager you created in step 2.
Click the … button in the SourceDirect property to launch the DDL dialog box.

Paste in the following XMLA command:


<Batch xmlns='http://schemas.microsoft.com/analysisservices/2003/engine' Transaction='true'>    
<!-- Create the partition for the year 1812 on the DemographicFacts table -->  
<!-- To create the partition, alter the measure group-->  
<Alter AllowCreate="true" ObjectExpansion="ExpandFull" xmlns="<a href="http://schemas.microsoft.com/analysisservices/2003/engine">http://schemas.microsoft.com/analysisservices/2003/engine</a>">
   <Object>
    <DatabaseID>hans_rosling_project</DatabaseID>
    <CubeID>Model</CubeID>
    <MeasureGroupID>DemographicFacts_c2655f3e-3570-4a34-92bf-3f9ed44ad24b</MeasureGroupID>
    <PartitionID>Partition 1812</PartitionID>
   </Object>
   <ObjectDefinition>
    <Partition xmlns:xsd="<a href="http://www.w3.org/2001/XMLSchema">http://www.w3.org/2001/XMLSchema</a>" xmlns:xsi="<a href="http://www.w3.org/2001/XMLSchema-instance">http://www.w3.org/2001/XMLSchema-instance</a>" xmlns:ddl200_200="<a href="http://schemas.microsoft.com/analysisservices/2010/engine/200/200">http://schemas.microsoft.com/analysisservices/2010/engine/200/200</a>">
     <ID>Partition 1812</ID>
     <Name>Partition 1812</Name>
     <Source xsi:type="QueryBinding">
      <DataSourceID>bc946c5c-ec5b-4fbb-87d9-5e4f721e6adc</DataSourceID>
      <QueryDefinition>SELECT [dbo].[DemographicFacts].*   FROM [dbo].[DemographicFacts]  WHERE ([Year] = 1812)</QueryDefinition>
     </Source>
     <StorageMode valuens="ddl200_200">InMemory</StorageMode>
    </Partition>
   </ObjectDefinition>
  </Alter>

<!-- Now do a ProceessData on the newly created partition -->
  <Process xmlns="<a href="http://schemas.microsoft.com/analysisservices/2003/engine">http://schemas.microsoft.com/analysisservices/2003/engine</a>">
   <Type>ProcessData</Type>
   <Object>
    <DatabaseID>hans_rosling_project</DatabaseID>
    <CubeID>Model</CubeID>
    <MeasureGroupID>DemographicFacts_c2655f3e-3570-4a34-92bf-3f9ed44ad24b</MeasureGroupID>
    <PartitionID>Partition 1812</PartitionID>
   </Object>
  </Process>

<!-- Finally, do a ProcessRecalc on the database to make the DB queryable -->
  <Process xmlns="<a href="http://schemas.microsoft.com/analysisservices/2003/engine">http://schemas.microsoft.com/analysisservices/2003/engine</a>">
   <Type>ProcessRecalc</Type>
   <Object>
    <DatabaseID>hans_rosling_project</DatabaseID>
   </Object>
  </Process>
 </Batch>

Press OK to all dialog boxes, and F5 to run the package. The run should be successful and you should be able to verify in SSMS that the partition was created, populated, and is queryable.

Let’s look a bit closer at that XMLA and look at how the scripts were generated. There are three commands in those 43 lines. The commands were generated by scripting out actions from three different places in the SSMS UI. I manually wrapped these commands in a batch, and inserted some comments which I hope are helpful. Use the comments to find the start of each command.

The first command, the Alter, creates a partition by modifying the DemographicFacts measure group. Notice that you must refer to the measure group by ID. The ID for the measure group was automatically generated by the SSDT UI when the table was created using the Import Wizard. The partition definition is again bound using a query binding. The data source ID is the GUID for the magical “Sandbox” object, which was also automatically created by the SSDT UI. None of these IDs are exposed in the UI in SSMS or SSDT, so you will need to discover them by scripting out.

To generate script for creating a partition, do the following:

Launch the Partition Manager in SSMS on the desired table.
Select a partition that is bound via query binding, then click the Copy button in the toolbar.
From the New Partition dialog, click the Script button. This script will contain both the data source IDs and the measure group IDs necessary for the script.

That takes care of creating the partition. The next command is a ProcessData on the partition. Again, you must pass the measure group ID to do the processing.

To generate script for processing a partition:

Launch the Partition Manager in SSMS on the desired table.
Select a partition, click Process.
Set the desired processing option in the dialog (in this case ProcessData), then script out. This script will contain the measure group ID you need for processing.

The last Process command is the ProcessRecalc on the database. Fortunately the database ID is quite readable, and also exposed via the properties page on the database, so you have some hope of actually being able to write this script yourself. Nevertheless, it is easier to generate the script by right-clicking the database in the SSMS object explorer, selecting Process, choosing ProcessRecalc from the processing options, then scripting out.

Simple, huh? Compare this to the similar code in AMO. The AMO snippet I posted was 26 lines and doesn’t contain any horrible GUIDs, so stylistically it may be a preferable approach to the one I outlined above for creating the DDL. You can call AMO from Integration Services from a scripting task. However, from a performance standpoint, you might want to stick with the ugly DDL to avoid having to deal with the VSTA dependency and overhead associated with the scripting task.

Also compare this to the approach for doing a ProcessAdd in SSIS. ProcessAdd is a lot neater, although there are scenario tradeoffs to be made. Comparing the two processing approaches merits a future post.

Updated – February 2012 PASSMN Meeting – Geographic Visualizations Using Maps in SSRS

Mon, 20 Feb 2012 22:32:35 -0600

Sponsor

Meeting Details

Location: 3601 West 76th Street, Suite 600 Edina, MN 55437
Date: February 21, 2012
Time: 4:00 PM – 6:00 PM – Later Time than normal
Live Meeting:
- URL: https://www.livemeeting.com/cc/usergroups/join?id=R9352R&role=attend&pw=QqGKDQ7%5Bp
- Meeting ID: R9352R
- Entry Code: QqGKDQ7[p

Please click here for meeting details and to RSVP

Geographic Visualizations Using Maps in SSRS

Brian Larson, Superior Consulting Services

This session will explore the many and varied uses of the map report item in Reporting Services. From providing geographic analytics to diagramming locations on a warehouse floorplan, the map report item – when combined with SQL Server spatial data types – can be used in a variety of ways to create spatial representations of your data. We may even look at a game or two created using the map report item.

Brian Larson, as a contract member of the Reporting Services development team, contributed to the original code base. Brian is the Vice President of Technology for Superior Consulting Services. Brian has presented at national conferences and events and is the author of “Microsoft SQL Server 2008 Reporting Services: 3rd Edition” and “Delivering Business Intelligence with SQL Server 2008: 2nd Edition” both from McGraw-Hill Professional.

RMOUG 2012 Presentation Slides!

Mon, 20 Feb 2012 19:53:49 -0600

Graham Wood was kind enough to send me a quick email and let me know that my presentation slides weren’t downloading correctly from RMOUG’s website, so I’ve put them here for anyone that may like to have them.

I’m still recovering from the post RMOUG Training Day festivities, aka Foak Table, which was held up in Breckenridge again this year. I will make sure to blog about Training Days and the post event at a later date, but until then, here is a link to my slides. Please email me with any questions or comments you may have- always glad to chat!

EM12C
Sherlock Holmes for DBA’s

~Kellyn

Still time to sign up for my SQL 2012 Class

Mon, 20 Feb 2012 18:56:56 -0600

My SQL 2012 class is just a few short weeks away, but there are still seats available for the class. Take the time and get signed up now for this great four day class where we will be diving into SQL Server 2012 with loads of hands on labs to really get you ready to [...]

Big Data - A Microsoft Tools Approach

Mon, 20 Feb 2012 16:16:27 -0600

(As with all of these types of posts, check the date of the latest update I’ve made here. Anything older than 6 months is probably out of date, given the speed with which we release new features into Windows and SQL Azure)

I don’t normally like to discuss things in terms of tools. I find that whenever you start with a given tool (or even a tool stack) it’s too easy to fit the problem to the tool(s), rather than the other way around as it should be.

That being said, it’s often useful to have an example to work through to better understand a concept. But like many ideas in Computer Science, “Big Data” is too broad a term in use to show a single example that brings out the multiple processes, use-cases and patterns you can use it for.

So we turn to a description of the tools you can use to analyze large data sets. “Big Data” is a term used lately to describe data sets that have the “Four V’s” as a characteristic, but I have a simpler definition I like to use:

Big Data involves a data set too large to process in a reasonable period of time

I realize that’s a bit broad, but in my mind it answers the question and is fairly future-proof. The general idea is that you want to analyze some data, and using whatever current methods, storage, compute and so on that you have at hand it doesn’t allow you to finish processing it in a time period that you are comfortable with. I’ll explain some new tools you can use for this processing.

Yes, this post is Microsoft-centric. There are probably posts from other vendors and open-source that cover this process in the way they best see fit. And of course you can always “mix and match”, meaning using Microsoft for one or more parts of the process and other vendors or open-source for another. I never advise that you use any one vendor blindly - educate yourself, examine the facts, perform some tests and choose whatever mix of technologies best solves your problem.

At the risk of being vendor-specific, and probably incomplete, I use the following short list of tools Microsoft has for working with “Big Data”. There is no single package that performs all phases of analysis. These tools are what I use; they should not be taken as a Microsoft authoritative testament to the toolset we’ll finalize for a given problem-space. In fact, that’s the key: find the problem and then fit the tools to that.

Process Types

I break up the analysis of the data into two process types. The first is examining and processing the data in-line, meaning as the data passes through some process. The second is a store-analyze-present process.

Processing Data In-Line

Processing data in-line means that the data doesn’t have a destination - it remains in the source system. But as it moves from an input or is routed to storage within the source system, various methods are available to examine the data as it passes, and either trigger some action or create some analysis.

You might not think of this as “Big Data”, but in fact it can be. Organizations have huge amounts of data stored in multiple systems. Many times the data from these systems do not end up in a database for evaluation. There are options, however, to evaluate that data real-time and either act on the data or perhaps copy or stream it to another process for evaluation.

The advantage of an in-stream data analysis is that you don’t necessarily have to store the data again to work with it. That’s also a disadvantage - depending on how you architect the solution, you might not retain a historical record. One method of dealing with this requirement is to trigger a rollup collection or a more detailed collection based on the event.

StreamInsight - StreamInsight is Microsoft’s “Complex Event Processing” or CEP engine. This product, hooked into SQL Server 2008R2, has multiple ways of interacting with a data flow. You can create adapters to talk with systems, and then examine the data mid-stream and create triggers to do something with it. You can read more about StreamInsight here: http://msdn.microsoft.com/en-us/library/ee391416(v=sql.110).aspx

BizTalk - When there is more latency available between the initiation of the data and its processing, you can use Microsoft BizTalk. This is a message-passing and Service Bus oriented tool, and it can also be used to join system’s data together than normally does not have a direct link, for instance a Mainframe system to SQL Server. You can learn more about BizTalk here: http://www.microsoft.com/biztalk/en/us/overview.aspx

.NET and the Windows Azure Service Bus - Along the same lines as BizTalk but with a more programming-oriented design are the Windows and Windows Azure Service Bus tools. The Service Bus allows you to pass messages as well, and opens up web interactions and even inter-company routing. BizTalk can do this as well, but the Service Bus tools use an API approach for designing the flow and interfaces you want. The Service Bus offerings are also intended as near real-time, not as a streaming interface. You can learn more about the Windows Azure Service Bus here: http://www.windowsazure.com/en-us/home/tour/service-bus/ and more about the Event Processing side here: http://msdn.microsoft.com/en-us/magazine/dd569756.aspx

Store-Analyze-Present

A more traditional approach with an organization’s data is to store the data and analyze it out-of-band. This began with simply running code over a data store, but as locking and blocking became an issue on a file system, Relational Database Management Systems (RDBMs) were created. Over time a distinction was made between data used in an online processing system, meant to be highly available for writing data (OLTP) and systems designed for analytical and reporting purposes (OLAP).

Later the data grew larger than these systems were designed for, primarily due to consistency requirements. In analysis, however, consistency isn’t always a requirement, and so file-based systems for that analysis were re-introduced from the Mainframe concepts, with new technology layered in for speed and size.

I normally break up the process of analyzing large data sets into four phases:

Source and Transfer - Obtaining the data at its source and transferring or loading it into the storage; optionally transforming it along the way
Store and Process - Data is stored on some sort of persistence, and in some cases an engine handles the acquisition and placement on persistent storage, as well as retrieval through an interface.
Analysis - A new layer introduced with “Big Data” is a separate analysis step. This is dependent on the engine or storage methodology, is often programming language or script based, and sometimes re-introduces the analysis back into the data. Some engines and processes combine this function into the previous phase.
Presentation - In most cases, the data wants a graphical representation to comprehend, especially in a series or trend analysis. In other cases a simple symbolic representation, similar to the “dashboard” elements in a Business Intelligence suite. Presentation tools may also have an analysis or refinement capability to allow end-users to work with the data sets. As in the Analysis phase, some methodologies bundle in the Analysis and Presentation phases into one toolset.

Source and Transfer

You’ll notice in this area, along with those that follow, Microsoft is adopting not only its own technologies but those within open-source. This is a positive sign, and means that you will have a best-of-breed, supported set of tools to move the data from one location to another. Traditional file-copy, File Transfer Protocol and more are certainly options, but do not normally deal with moving datasets.

I’ve already mentioned the ability of a streaming tool to push data into a store-analyze-present model, so I’ll follow up that discussion with the tools that can extract data from one source and place it in another.

SQL Server Integration Services (SSIS)/SQL Server Bulk Copy Program (BCP) - SSIS is a SQL Server tool used to move data from one location to another, and optionally perform transform or other processes as it does so. You are not limited to working with SQL Server data - in fact, almost any modern source of data from text to various database platforms is available to move to various systems. It is also extremely fast and has a rich development environment. You can learn more about SSIS here: http://msdn.microsoft.com/en-us/library/ms141026.aspx BCP is a tool that has been used with SQL Server data since the first releases; it has multiple sources and destinations as well. It is a command-line utility,and has some limited transform capabilities. You can learn more about BCP here: http://msdn.microsoft.com/en-us/library/ms162802.aspx

Sqoop - Tied to Microsoft’s latest announcements with Hadoop on Windows and Windows Azure, Sqoop is a tool that is used to move data from SQL Server 2008R2 (and higher) to Hadoop, quickly and efficiently. You can read more about that in the Readme file here: http://www.microsoft.com/download/en/details.aspx?id=27584

Application Programming Interfaces - API’s exist in most every major language that can connect to one data source, access data, optionally transforming it and storing it in another system. Most every dialect of the .NET-based languages contain methods to perform this task.

Store and Process

Data at rest is normally used for historical analysis. In some cases this analysis is performed near real-time, and in others historical data is analyzed periodically. Systems that handle data at rest range from simple storage to active management engines.

SQL Server - Microsoft’s flagship RDBMS can indeed store massive amounts of complex data. I am familiar with a two systems in excess of 300 Terabytes of federated data, and the Pan-Starrs project is designed to handle 1+ Petabyte of data. The theoretical limit of SQL Server DataCenter edition is 540 Petabytes. SQL Server is an engine, so the data access and storage is handled in an abstract layer that also handles concurrency for ACID properties. You can learn more about SQL Server here: http://www.microsoft.com/sqlserver/en/us/product-info/compare.aspx

SQL Azure Federations - SQL Azure is a database service from Microsoft associated with the Windows Azure platform. Database Servers are multi-tenant, but are shared across a “fabric” that moves active databases for redundancy and performance. Copies of all databases are kept triple-redundant with a consistent commitment model. Databases are (at this writing - check http://WindowsAzure.com for the latest) capped at a 150 GB size limit per database. However, Microsoft released a “Federation” technology, allowing you to query a head node and have the data federated out to multiple databases. This improves both size and performance. You can read more about SQL Azure Federations here: http://social.technet.microsoft.com/wiki/contents/articles/2281.federations-building-scalable-elastic-and-multi-tenant-database-solutions-with-sql-azure.aspx

Analysis Services - The Business Intelligence engine within SQL Server, called Analysis Services, can also handle extremely large data systems. In addition to traditional BI data store layouts (ROLAP, MOLAP and HOLAP), the latest version of SQL Server introduces the Vertipaq column-storage technology allowing more direct access to data and a different level of compression. You can read more about Analysis Services here: http://www.microsoft.com/sqlserver/en/us/solutions-technologies/business-intelligence/analysis-services.aspx and more about Vertipaq here: http://msdn.microsoft.com/en-us/library/hh212945(v=SQL.110).aspx

Parallel Data Warehouse - The Parallel Data Warehouse (PDW) offering from Microsoft is largely described by the title. Accessed in multiple ways including using Transact-SQL (the Microsoft dialect of the Structured Query Language), it scales in parallel to extremely large datasets. It is a hardware and software offering - you can learn more about it here: http://www.microsoft.com/sqlserver/en/us/solutions-technologies/data-warehousing/pdw.aspx

HPC Server - Microsoft’s High-Performance Computing version of Windows Server deals not only with large data sets, but with extremely complicated computing requirements. A scale-out architecture and inter-operation with Linux systems, as well as dozens of applications pre-written to work with this server make this a capable “Big Data” system. It is a mature offering, with a long track record of success in scientific, financial and other areas of data processing. It is available both on premises and in Windows Azure, and also in a hybrid of both models, allowing you to “rent” a super-computer when needed. You can read more about it here: http://www.microsoft.com/hpc/en/us/product/cluster-computing.aspx

Hadoop - Pairing up with Hortonworks, Microsoft has released the Hadoop Open-Source system - including HDFS and a Map/Reduce standardized software, Hive and Pig - on Windows and the Windows Azure platform. This is not a customized version; off-the-shelf concepts and queries work well here. You can read more about Hadoop here: http://hadoop.apache.org/common/docs/current/ and you can read more about Microsoft’s offerings here: http://hortonworks.com/partners/microsoft/

Windows and Azure Storage - Although not an engine - other than a triple-redundant, immediately consistent commit - Windows Azure can hold terabytes of information and make it available to everything from the R programming language to the Hadoop offering. Binary storage (Blobs) and Table storage (Key-Value Pair) data can be queried across a distributed environment. You can learn more about Windows Azure storage here: http://msdn.microsoft.com/en-us/library/windowsazure/gg433040.aspx

Analysis

In a “Big Data” environment, it’s not unusual to have a specialized set of tasks for analyzing and even interpreting the data. This is a new field called “data Science”, with a requirement not only for computing, but also a heavy emphasis on math.

Transact-SQL - T-SQL is the dialect of the Structured Query Language used by Microsoft. It includes not only robust selection, updating and manipulating of data, but also analytical and domain-level interrogation as well. It can be used on SQL Server, PDW and ODBC data sources. You can read more about T-SQL here: http://msdn.microsoft.com/en-us/library/bb510741.aspx

Multidimensional Expressions and Data Analysis Expressions - The MDX and DAX languages allow you to query multidimensional data models that do not fit well with typical two-plane query languages. Pivots, aggregations and more are available within these constructs to query and work with data in Analysis Services. You can read more about MDX here: http://msdn.microsoft.com/en-us/library/ms145506(v=sql.110).aspx and more about DAX here: http://www.microsoft.com/download/en/details.aspx?id=28572

HPC Jobs and Tasks - Work submitted to the Windows HPC Server has a particular job - essentially a reservation request for resources. Within a job you can submit tasks, such as parametric sweeps and more. You can learn more about Jobs and Tasks here: http://technet.microsoft.com/en-us/library/cc719020(v=ws.10).aspx

HiveQL - HiveQL is the language used to query a Hive object running on Hadoop. You can see a tutorial on that process here: http://social.technet.microsoft.com/wiki/contents/articles/6628.aspx

Piglatin - Piglatin is the submission language for the Pig implementation on Hadoop. An example of that process is here: http://blogs.msdn.com/b/avkashchauhan/archive/2012/01/10/running-apache-pig-pig-latin-at-apache-hadoop-on-windows-azure.aspx

Application Programming Interfaces - Almost all of the analysis offerings have associated API’s - of special note is Microsoft Research’s Infer.NET, a new language construct for framework for running Bayesian inference in graphical models, as well as probabilistic programming. You can read more about Infer.NET here: http://research.microsoft.com/en-us/um/cambridge/projects/infernet/

Presentation

Lots of tools work in presenting the data once you have done the primary analysis. In fact, there’s a great video of a comparison of various tools here: http://msbiacademy.com/Lesson.aspx?id=73 Primarily focused on Business Intelligence. That term itself is now not as completely defined, but the tools I’ll show below can be used in multiple ways - not just traditional Business Intelligence scenarios. Application Programming Interfaces (API’s) can also be used for presentation; but I’ll focus here on “out of the box” tools.

Excel - Microsoft’s Excel can be used not only for single-desk analysis of data sets, but with larger datasets as well. It has interfaces into SQL Server, Analysis Services, can be connected to the PDW, and is a first-class job submission system for the Windows HPC Server. You can watch a video about Excel and big data here: http://www.microsoft.com/en-us/showcase/details.aspx?uuid=e20b7482-11c9-4965-b8f0-7fb6ac7a769f

Reporting Services - Reporting Services is a SQL Server tool that can query and show data from multiple sources, all at once. It can also be used with Analysis Services. You can read more about Reporting Services here: http://www.microsoft.com/sqlserver/en/us/solutions-technologies/business-intelligence/reporting-services.aspx

Power View - Power View is a “Self-Service” Business Intelligence reporting tool, which can work with on-premises data in addition to SQL Azure and other data. You can read more about it and see videos of Power View in action here: http://www.microsoft.com/sqlserver/en/us/future-editions/business-intelligence/SQL-Server-2012-reporting-services.aspx

SharePoint Services - Microsoft has rolled several capable tools in SharePoint as “Services”. This has the advantage of being able to integrate into the working environment of many companies. You can read more about lots of these reporting and analytic presentation tools here: http://technet.microsoft.com/en-us/sharepoint/ee692578

This is by no means an exhaustive list - more capabilities are added all the time to Microsoft’s products, and things will surely shift and merge as time goes on. Expect today’s “Big Data” to be tomorrow’s “Laptop Environment”.

SQL Server – Powershell Active Directory search

Mon, 20 Feb 2012 10:13:30 -0600

Working with Powershell and Active Directory simplifies some complex tasks for the DBA.

Active Directory is LDAP compliant. This means the RFC 1779 and RFC 2247 standards are met.

This example lists all employees on an LDAP path. This method requires knowledge of the LDAP path .

Common Name (cn)

Organizational Unit (ou)

Domain Component (dc)

$group = [ADSI] "LDAP://cn=MYORGAllEmployees,ou=MYORG,dc=south,dc=syborg,dc=net" 
foreach ($member in $group.member) 
{ 
$member 
}