Kaisa's blog

Writing to EventLog from PowerShell (and some language power)

kaisa — Wed, 06 Jan 2010 15:39:00 GMT

I've played around with Vidas Matelis' PowerShell scripts for processing SSAS databases the last couple of months. The scripts have all sorts of useful output, but I've been looking for a way to write the error messages to the Event viewer instead of just the command prompt. (Mostly because if you start the script from a short cut, it will just fly through and be gone from the screen when it's finished, and you have no way of knowing what on Earth the output was.)

So, today I've learned 3 new things about PowerShell
1) You can add the parameter -noexit to the command prompt string to make sure the script prompt window stays open after the script has finished running. (You can't rerun the script, though.)
Got the tip from Hey, Scripting Guy!

2) There are two ways to write to the Event Log
Number 1:
Use a Cmdlet to to create an instance of the .NET Framework class System.Diagnostics.Eventlog and then write the entry.

$a = New-Object -type System.Diagnostics.Eventlog -argumentlist System
$a.Source = "Windows PowerShell"
$a.WriteEntry("This is a test.","Information")

More info

Number 2:
Create a new Soure type and then write to the EventLog.
new-eventlog -LogName Application -Source MySource
write-eventLog -LogName Application -Message "Hello Eventing World" -Source MySource -id 1234
More info

I've used the first version in the original ProcessSSASDB.ps1 script, but I'm inspired by the second one to make more changes.

3) I still remember enough of my French lessons 15 years ago to be able to figure out why my code failed from reading a French dev forum on the same thing. :)
http://powershell-scripting.com/index.php?option=com_joomlaboard&Itemid=76&func=view&id=1053&catid=5
J'ai trouvé il y a un espace de trop sur la ligne
$applog.WriteEntry ("Ceci est un message d'erreur.", "error", "1234")
entre WriteEntry et ("Ceci

I had a space too many between WriteEntry and (" as well.

More on cube processing - Process only one partition so you can browse the cube

kaisa — Fri, 18 Dec 2009 12:59:00 GMT

Found this tip today over at Microsoft SQL Server Analysis Services 2005/2008 Tips

Process only one partition so you can browse the cube by Nicholas Dritsas. So if what I need to do is to check what one dimension looks like, I can just process that dimension and then process the structure of the cube to see how it works. Might come in handy!

More on DMV

kaisa — Thu, 19 Nov 2009 09:40:00 GMT

Learning how to read cube metadata through SQL Server queries sparked a few ideas, so today I had to find more information about this.

Once again, Vidas Matelis provides interesting information.

SSAS 2008 DMVs – querying from the SQL Server and database diagrams
http://www.ssas-info.com/VidasMatelisBlog/70_ssas-2008-dmvs-querying-from-the-sql-server-and-database-diagrams

He lists a lot of queries used for finding out more about the DMVs, and also provides diagrams of all the tables. Yey!

At SQLKit.com there's also some information about the views

Dynamic Management Views for SSAS
http://sqlkit.com/2009/07/15/dynamic-management-views-for-ssas/

How to: Query an OLAP cube for processing information

kaisa — Wed, 18 Nov 2009 16:45:00 GMT

Sometimes you just want a list of when were my dimensions last processed, or when was the schema updated last? You can always right click on the dimension and look at the Properties, but if you want to check for more than 1 dimension, that's kind of slow.

Once again, I've found some scripts and code from Vidas Matelis

Using SSRS to report SSAS 2008 database structure using DMVs

http://www.ssas-info.com/VidasMatelisBlog/144_using-ssrs-to-report-ssas-2008-database-structure-using-dmvs

It's a lot quicker to just fire some sql queries against a linked server to list "Last processed date" than checking one dimension at a time.

Note: Vidas Matelis blog engine changes the quotes and double quotes up a bit, so you'll need to run a find/replace on these characters in order to get it to work.

My first experience with Joomla

kaisa — Wed, 18 Nov 2009 16:27:00 GMT

Since January this year, I've been responsible for running a website for Od@ -nettverk, a network / community for women working in IT in Norway. By now we have more than 900 members. Our web site runs Joomla 1.5.

Coming from a strictly Microsoft background, the prospect of running the site was a bit daunting at first. PHP? MySQL? But it has turned out well. I'm more comfortable around it now.

I'm actually amazed at how many free modules and components there are. Here are a few of my favorites that we use in our site:

EventList by schlu.net
Phoca Gallery by Phoca
Agora Olympus forum from JVitals
JoomDOC from Artio
eXtplorer file manager

How to: process only dimensions in batches with a PowerShell script

kaisa — Wed, 18 Nov 2009 15:52:00 GMT

Sometimes, you want to process all your dimensions in one go, before processing your cube. This isn't an option in SSMS, you either do the dimensions one after the other manually, or you go for the whole database.

Fortunately, Vidas Matelis has written some rather nifty PowerShell scripts to help you out.

http://www.ssas-info.com/VidasMatelisBlog/116_powershell-scripts-to-process-dimensions-and-print-ssas-db-info

I've used his script for processing the whole database mostly over the last few weeks:

http://www.ssas-info.com/analysis-services-scripts/1238-powershell-script-to-process-all-dimensions-and-cubes-in-one-db-limiting-workload

You might have to sign the PowerShell scripts in order to make it work. I used this tutorial to sign my script and have it run locally.

http://www.hanselman.com/blog/SigningPowerShellScripts.aspx

The only thing I'd like to change with the dimension processing script is that all errors are written to the command prompt window. There are two problems with that:

1) The window disappears when the code has finished running

2) The window isn't scrollable, so even though the error messages are staying put (untill the window disappears), you can't go back and see what other dimension were also not being processed properly.

I'm going to investigate writing error messages with the hubby, who is 100 times better at PS than I am.

How to: create an SSAS Project from a live SSAS Database

kaisa — Wed, 18 Nov 2009 15:45:00 GMT

Sometimes you might need to download a live SSAS database into a Visual Studio project. Like if someone build an SSAS solution and then deleted the development project.

Here's an explanation on how to do it:

How to: Create an Analysis Services Project Based on an Existing Analysis Services Database

http://msdn.microsoft.com/en-us/library/ms365361(SQL.90).aspx

(Short version: File -> New project -> Import Analysis Services 2008 Database template)

Only 51% geek?

kaisa — Fri, 25 Jul 2008 21:32:00 GMT

I found this test over at a Martin Bekkelund, a former collegue's, blog (all in Norwegian).

How geek am I?

51% Geek

Martin is only 43% geek. I'm not sure if it's a good or bad thing to have a higher score than him, but I feel pretty confident the test is true enough.

How to import DTS packages from a txt file

kaisa — Tue, 06 May 2008 13:51:00 GMT

Recently I got a text file from a customer. They'd exported all the DTS packages to this file, and told me I just had to create a new DTS package to import them all into the SysDtsPackages table in SQL Server 2000. I'd never done that before, and had to find out how. This doesn't seem to be discussed a lot on the Internet, or I might have used the wrong words while searching.

In the end I found this very clear guide written by Darren Green, "Transferring DTS Packages":

http://www.sqldts.com/204.aspx

I did it slightly differently. I created a copy of the SYSDTSPackages table in the MSDB database, and used the Import / Export wizard to populate this new table from my text file. Then I created the package with a Source and Destination connection, did the "Disconnected edit" and imported it all. The only difference was thatI updated the SourceObjectName to be the name of my copy table. Brilliant! It worked like a charm!

After having looked through all the DTS packages, I must say that I'm a lot more happy working with SSIS...

Non technical update

kaisa — Sun, 04 May 2008 20:09:00 GMT

My blogging has been embarassingly sporadic. The second half of 2007 was crazy, I spent a lot of time working on a fairly big BI project as well as trying to pass Microsoft exam 70-445 (TS: Microsoft SQL Server 2005 Business Intelligence – Implementation and Maintenance). I also went on a 2 day training course on MEC, Microsoft Enterprise Cube. I've implemented PerformancePoint Monitoring Server a couple of times, even enabled it for Kerberos authentication. (Should write a post on that.)

But the biggest stress factor, as well as happiness factor, the last half year was to sell our old flat and buy a new and bigger one! Even though the estate market isn't as problematic as in the USA, we decided to sell first and buy afterwards. Our flat was on the market for about 2 months, which felt like forever... But in the beginning of December we sold it, just a bit under the asking price. The first weekend in January 2008 we went to see 10 appartments on sale and bought the first we visited... It sits on the ground floor, has 1 more room than our old one and a better floor plan. AND it has a terrace and a garage.

As soon as we'd signed the papers, we started furniture shopping. We wanted to throw out our sofa and lounge chairs and get a new bed and a fridge. New bed was easy, I wanted a Tempur bed. We visited a show room where we could try different Tempur beds and Bernt agreed that this was the way to go.

Then there was the fridge. We were kind of looking for a side-by-side fridge, but knew that the freezer part of these fridges might be a bit small. We found a really smart one from LG, with double (French) doors on the upper section and a freezer drawer in the bottom. Our fridge isn't as full as this picture. Yet.

When we'd settled for a bed and a fridge, we started looking for a sofa. I'd visited a rather nice and swanky furniture shop a few months earlier, and brought Bernt around to see if there were any sofas we liked there. Off course there was. We found a really nice Rolf Benz sofa. None of us have that much knowledge of fancy furniture brands, but this seems to be more like an Audi than a Volkswagen. After some though and visiting other furniture shops without finding any other sofas that we liked, we decided to go for the Rolf Benz Dono. Ours is dark brown with walnut railing and the right leg is longer and has one more pillow. It's awsome! Because our whole flat has white painted walls, it doesn't look too dark.

But the BEST part of moving house, was a coincidence that happened last week. A friend of our's sister has a really old cat, and she needed to give it a new home. And miracle over miracles, Bernt decided that we could take him in. I've wanted a cat since before we started dating, but he hasn't been too keen on the idea. I came home from a visit to my grandparents, and there Bernt was on the new sofa with an old cat in his lap. This cat is around 20 years old, has about 4 teeth, has blue eyes and mostly white fur (bit gray on top of his head and end of tail) and is very, very talkative! And cuddly. And shedding fur like mad. And I'm really fond of him. :) He loves being outside and to cuddle, or cuddle outside.

More pictures of The Cat will be posted at http://blogs.spipp.net/photos/kaisa/category1024.aspx

New Year, New Oppertunities

kaisa — Tue, 01 Jan 2008 21:44:00 GMT

So, the new year is here. The last year has been very interesting, with joining Avanade Norway the most important thing work wise, and our trip to Japan as the most important personally. Right before Christmas break, we sold our appartment, so 2008 will see us moving into a new place.

These are the technologies and programs I'll try focusing on in 2008:

SQL Server 2008
MEC
Getting a better grip on the whole BI project business

On a more personal side, I'll try to up my interior decorating skills and learn to cook more decent and quick every-day food... (Somehow, pasta, pizza and hot dogs doesn't seem to be very healthy in the long run.)

I've started liking the language tool bar

kaisa — Thu, 06 Dec 2007 09:34:00 GMT

For the next 2 days, I'm attending a training course at Microsoft UK (Chertsey office). Off course, all their keyboards are with English key setup, which lacks 3 letters (æøå), and also has the different symbols in very weird positions compared to a Norwegian keyboard setup. But hey, as long as you're using Windows, you can change your language settings fairly easily to your own keyboard setup. So now I have all 29 letters, and the symbols like ( )'- are in the "right" place.

Now, as long as I don't look at the keyboard while typing, I'm good to go.

The Official Roald Dahl Day (and Google has a Roald Dahl logo)

kaisa — Thu, 13 Sep 2007 13:13:00 GMT

One of my favourite writers, Roald Dahl, was born in Wales on September 13, 1916. His parents were Norwegian, but he never lived in Norway. He did come here on vacation, though.

Even though he is one of my favourites, I didn't really know that it was his birthday today. Neither did I know that there are lots of events in memory of him today. There's even a web site for celebrating the Roald Dahl day: http://www.roalddahlday.info. And Google celebrates this day by having a Google logo with elements from his books:

I'm not sure what the G part is, but I think it might be Matilda. The second O is a peach, and the L is a Wonka Bar with golden ticket.

He's mostly know for his childrens books, but he's also written lots of short stories for adults and two autobiographies. The short stories are rather dark and nasty. The childrens books are funny, witty, dark and nasty as well.

The first book I read (or rather that my mum read to me) was "The Witches". This was made into a movie in 1990, starring Anjelica Huston as the main witch. I hate that movie. It's actually very good up to the last 5 minutes, when it gets destroyed by making it a "happy ending". The movies I like the most are "Charlie and the Chocolate Factory", "Matilda", "Danny the Champion of the World". I'm really looking forward to the adaptation of "The Fantastic Mr. Fox", in which George Clooney and Cate Blanchett will lend their voices to Mr. and Mrs. Fox.

Lots of information about Roald Dahl can be found at http://www.roalddahl.com/!

Testing cross-posting to AvanadeAdvisor blog

kaisa — Sat, 18 Aug 2007 09:07:00 GMT

One of the first things I did starting at Avanade, was to create a blog there. It's at http://blog.avanadeadvisor.com/blogs/kaisal/default.aspx

But for some silly reason, I can't get the cross posting to work. Avanade is running the same edition of Community Server as I do. Other Avanauts have gotten it to work. But me? Nah. I can cross post from my Avanade-blog to my spipp.net blog, but not the other way. Weird.

Update: The mystery is solved. I started checking the error logs in Community Server. Turns out that our ISA server is blocking all outgoing traffic. Off course I knew that, but I didn't remember it when I started setting up cross posting. So that means no cross posting, no track backs and no ping backs. Well, I won't end up spamming anything then.

Cascading report parameters in OLAP reports

kaisa — Fri, 17 Aug 2007 21:15:00 GMT

One of the biggest problems I've been having with upgrading my mad reporting skills from RS 2000 to RS 2005 has been with cascading parameters in OLAP reports. WIth the new and "user friendly" connection method to Analysis Services, I couldn't use my old tricks with dynamic queries for parameters. And I never found a usefull description on how to actually do it in RS 2005. A few weeks ago, I was forced to figure it out. And in an enlightened moment, it dawned on me what to try, and I finally figured it out. Yey. So now I'm posting this for ... well... maybe someone needs it, or at least I know where to find it if I forget.

I created a stored procedure that returns three columns, and used it in a data set called MarketBrandsFromDB:

MarketBrandUniqueName

MarketBrandName

MarketBrandID

The MarketBrandUniqueName was formated to look like a cube member: [Market Brand].[Market Brand Name].&[Brand 1].

My first query created a result with all the members in one line. This stored procedure just returns a list of members, like a regular dataset.

Then I added the Market Brand dimension and Market Brand Hierarchy as a filter for the OLAP query, and marked it as a parameter. Then I went to the layout tab to make the Report Designer do its' magic and create both a parameter and a dataset, both called MarketBrandMarketBrandName. (Quite a mouthfull, one of the other guys wondered if I'd done a typo. Guess that's what user friendlyness does...)

So now I had a report parameter that refers to this data set that is just pulled directly from the cube, which isn't what I want. I wanted it to correspond to what's in the database. It turns out that the trick was to map the parameter called MarketBrandMarketBrandName to the dataset called MarketBrandsFromDB, and not the data set called MarketBrandMarketBrandName. I mapped the value field to MarketBrandUniqueName and the Label field to MarketBrandName. I used the same fields for the default values.

And there you have it. The report picks up my username, queries the sql database for which market brands I have access to, and gives me a choice of only those market brands, just as I wanted. It's all done using the GUI, so I don't have to make the other guys mess around in either mdx or the rdl file directly, which is also a good thing.

I think I couldn't find any documentation on how to do it because it's actually quite simple when you know how, so people don't bother documenting it. Or maybe it's in the documentation, I just didn't know what to look for.

My only question now is if it's safe to delete the dataset that was created for the MarketBrandMarketBrandName parameter?