Using Powershell to create Local Users from AW Customers

Posted on 04/09/2013 by Jens Vestergaard

As a part of my upcomming presentation at SqlSaturday Cambridge, I will probably be needing a lot of users to demo some aspects of security in Analysis Services. Therefore I have created a Powershell script, my first actually, to generate a bunch of users. The first source for a lot of users that came to mind was the company AD, but that probably wouldn’t be given the green light, if I asked. So what to do? Adventure Works to the rescue!

The Customer dimension in the Adventure Works database has roughly 18.000 entries. This would make a nice pile of users for a demo. So, connecting to the database, selecting the distinct customers and just adding them as local users can actually be done rather quickly through Powershell. The run-time is just around 10 minutes, to create or delete.

The script looks like this:

PowerShell_Script1

Just to take a peek at what this script generates, here is a screendump:

Users

I have attached both a create and a delete script: PS_Create Local Users From AW

As this is my first script, I’d be happy to hear if there is a better way of doing this!?

Posted in Programming | Tagged , , | 2 Comments

Same members declared in both Sub Select and Where clause of same MDX

Posted on 02/09/2013 by Jens Vestergaard

What to make of it?

Currently I am conducting a review on all existing solutions. These solutions have seen their share of developers over the years. Hence there are several “trademarks” to be found. One such trademark threw me off a bit. It seemed as if the same method had been applied to all queries in the solution. For every expression in the where clause, there would be a similar sub select statement. So if there was a slice by [Product].[Category].&[4], there would be a Sub Select eg. ( SELECT [Product].[Category].&[4] ON COLUMNS FROM… )

I hadn’t seen this construct before, so I felt challenged to find out, if this had some purpose or it was poorly written MDX. I acknowledge, that sometimes development is rushed, and you leave behind unwanted code. But this looked as if it was written to stay. There was almost no complexity to the MDX, other than the odd construct… An example of a statement converted into Adventure Works lingo, would look something like this:

SELECT 
    {[Measures].[Internet Sales Amount]} ON COLUMNS
    , NONEMPTY {[Promotion].[Promotion].[Promotion]} ON ROWS
FROM
    ( SELECT [Scenario].[Scenario].&[1] ON COLUMNS FROM (
        SELECT [Sales Territory].[Sales Territory].[Group].&[Europe] ON COLUMNS FROM (
            SELECT [Product].[Category].&[4] ON COLUMNS FROM
                [Adventure Works] ) ) )
WHERE (
    [Scenario].[Scenario].&[1]
    , [Sales Territory].[Sales Territory].[Group].&[Europe]
    , [Product].[Category].&[4]
)

I looked up Sub Cubes in Analysis Services 2008 Unleashed, which is a ressource I would highly recommend it, if you don’t already know of it. In this book, a sub cube is described as an arbitrary shaped discret and limited sub space of the cube. This illustrated by the following query (Query 1) creating the following sub space (Figure 1 – grey area):

Sub Cube Figure 3

Query 1

Sub Cube Figure 2

Figure 1

One of the important Things to notice about sub cubes are, that the Default member can change, if the “normal” default member is not a part of the sub space. Then the first member of the sub space will become the default member.

I was reconfirmed in most of my knowledge on sub cube when I dug into the documentation on the subject, and nothing would lead me to believe, that the construct was other than a misunderstanding. So I tweetet a question using hashmark #ssashelp, and Brent Ozar (blog|twitter), Chris Webb (blog|twitter) and Robert L Davis (blog|twitter) replied, after twisting some arms :) This lead to this blog post, describing the issue, in just a little more than 140 characters…So lets see what replies are in store! To my best knowledge, the first query in this blog post, is more confusing than brilliant.

Posted in Programming | Tagged , , | 2 Comments

“Quick” Dimension Attribute Settings Report

Posted on 27/08/2013 by Jens Vestergaard

This week I needed to check up on the design of several cubes, in several environments. As I somehow failed to install the SQL Server 2012 Best Practice Analyzer (BPA) properly, I wasn’t able to use that tool. So I build a quick command prompt tool, to check the most basic stuff on attributes?

I have attached the csproj (see below) and the flow of the program is as follows:

First you execute the command prompt, and wait for the process to finish.

AttibuteStatisticsCommandPrompt

After the program has run through all the attributes, you should see the file(s) pop up in the folder you supplied.

AttibuteStatisticsFiles

The CSV file would look something like this (this is AW):

CSV_ScreenShot

Get the solution here:  Attribute Statistics

Posted in Programming | Tagged , , | Leave a comment

Speaking at #SQLSatCambridge

Posted on 28/07/2013 by Jens Vestergaard

Do you sometimes drift off, thinking/dreaming crazy scenarios, that you almost instantly know won’t ever become a reality?
I had one of those moments, when I spotted, that #SQLSatCambridge and the NFL game at Wembley between The Pittsburgh Steelers and Minnesota Vikings were scheduled only a day apart. Oh how I wished to be in England that particular weekend. I had a pretty strong feeling, that the wife back home wouldn’t be too happy to ship me of, on grounds of just the NFL game. She has every right to, on the account of our third child arrived in July. So I dared to submit two abstracts, one on Custom Assemblies which I presented at #SQLSatDenmark and one on Cube Security, which I have been working with intensively for the last couple of years. In my childish bliss, I drifted of on the thought of me being in Cambridge on Saturday, and at Wembley on Sunday. Double blast!

Imagine my reaction, when I recieved a mail from the Mark Broadbent (blog|twitter), stating that my abstract on cube security had been elected. The mail got in around midnight, and I had to re-read it several times, just to check I wasn’t making it up. 12 hours later, I replied and confirmed my availability to the event. Time to go Work the misses.

As my wife instantly recognized, how proud I was, to have been elected to speak at #SqlSatCambridge, she saw no reason to why I couldn’t stay an extra day and watch the game. She has been shaking her head for the last several years, when the NFL season begins and I claim the TV-set. She knows I’m a huge fan of football, as well as SQLServer ;)
Note to self: Go Large on wife’s Christmas gift

So sometimes the most outrageous, mind bending, far fetched ideas can become a reality. One of mine will, the last weekend in September this year.

Posted in Community | Tagged , , | 4 Comments

The New Blue in Town…

Posted on 11/07/2013 by Jens Vestergaard

Ch-Ch-Ch-Changes

It seems to be the season, where a lot of people switches jobs. At least in my social network LinkedIn states that 14 people have a new position, just this past month. Of course there are always those who head in a new direction every 24 months. And I’ve never really understood how they manage to establish themselves so quickly, but I seem to have a need to settle more in, when I finally decide on something. I held my previous position for 4 yrs, and my current position I have held for about 5½ yrs, until now.

Looking back, over my shoulder

During my years in Rehfeld, I have been engaged in several roles. I started out as a Business Intelligence developer doing custom dashboards for our clients. The customization was almost epic in proportion, in a way where the consultant in the end was no longer needed. Everything could be controlled from the data imported into the database (there were several databases, but only one (1) containing meta data) No, not that kind of meta data! But boy did I get smarter as I got along…
The bold directors then made an organizational change, in where I was to team up with a database developer and a .Net developer. The three of us were to distill the lessons learned in the organization during the last many years and make it into a product: Effektor. We wanted to build a toolbox, to enable the consultant to have the tedious parts of developing a DWH done by a click of a button, and at the same time with a very high level of quality. This across all major projects, so now every consultant would be able babysit others work, without the need of having to read up on how this dude implemented the T2 on that column, and why on Earth that implementation wasn’t the same as on these other two columns…
The consultant was the first job function the product should support. As of the latest version, we now support operations for security administrators, data stewards, data managers, super users and of course regular users.

Can’t Explain

In the last year+, I have been back into the field, as a Business Intelligence consultant, helping several clients on projects involving Data Mining, ETL, Server Upg., Custom Applications. I have been fortunate enough to have Effektor create much of the plumbing on several projects, and it really has made the development process a whole lot faster. As long as the business requirements are not too quirky or far out, the tool actually does most by meta data definition only. Thumbs up to Thomas Meltofte Eriksen (LinkedIn) and Mikael Nygaard (LinkedIn|Twitter) for doing such a smashing job on the tool. I really enjoyed our time on the PRO team.
As I left PRO, I joined Team SPOK (abb. for Stat, Privat og Kommune ~ Government, Private and Municipal). The team focused on clients in those sectors. There are a lot of team members that really do a great job everyday, are open-minded, happy and always willing to help and make everybody become a better developer. Some stand out, as I have worked with them on several assignments or do community work with them. Rasmus Reinholdt Nielsen (LinkedIn|Twitter),) whom I will be presenting at Campus Days with is a great co-worker/team lead whose wisdom and humor I am really going to miss. Luckily we get to meet, hopefully often, in the community. Rolf Veinø Sørensen (LinkedIn) who always has the demo of the newest stuff already done, sometimes even before the news hit your inbox. Finally Brian Bønk Rueløkke (blog|twitter) who would be a fantastic resource on any team. This guy really knows his stuff!
Hopefully I will be able to work with you guys in the future, sometime again. There’s just this something I need to do first…

Babe, I’m Gonna Leave You

As some of you may have guessed from the subtitle, and the post i general, I have decided to give it a GO in another company. I have come to an agreement with Mærsk Line IT, to start as a Senior Business Intelligence Developer in August. My primary focus areas will be training and guiding new Business Intelligence developers on the SQL Stack, delivering proof of concepts, establishing a baseline of best practices for Business Intelligence development. Besides that, I will engage in the ongoing projects as a developer and try to maintain or up my engagement in the SQL Server Community.
This position also requires some traveling, since some of the projects are done by a Business Partner in India. Traveling, as a part of a job, is new to me, but I’m very excited to go explore the possibilities that lies within.
I am really looking forward starting in the new position. But first – three (3) weeks of vacation :)

Posted in Mixed | Tagged , , | Leave a comment