Tag: SSAS 2005

Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SSAS: Errors in the metadata manager. The dimension with ID of…

Crazy error today. In Analysis Services, (SSAS), some jobs got hung, sync command got hung, and what ended up happening is a cube on the default instance was totally unusable.

Try to delete, rename, resync, redeploy, anything, and I would get the error..

Errors in the metadata manager. The dimension with ID of..

Couldn’t do anything. The solution? I had to stop the SSAS service, go to the OLAP data directory under my SQL install and remove the folder for that cube. I then started the SSAS Service, and the SSAS Database was gone (finally!). I redeployed and reprocessed and everything is working again. Whew 🙂

Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SSAS Cube Rule #1 – YOU NEED CALCULATE

Ok, I just spent a few hours debugging a cube that wasn’t bringing back any data. Checking all the relationships, the source data, the marts, etc, etc. And lo and behold, find out that a colleague commented out all the calculations, including the CALCULATE statement which says:

The CALCULATE command controls the aggregation of leaf cells in the cube.
If deleted or modified, the data within the cube will be affected.
This command should only be edited if you intend on manually specifying how the cube will be aggregated.

Remember, you NEED the “CALCULATE” command!

Categories
Business Intelligence SQLServerPedia Syndication

Microsoft BI Client Tools: Easing the Transition from Excel 2003 to Excel 2007 – PivotTable Layout and Design

Being that we are now in the year 2009, you would think that most people are currently using or have been using the Office 2007 suite for a couple of years now. The truth is that there are many businesses “in the wild” that are still standardizing on the Office 2003 suite.

Why? Well there are a variety of reasons. Some places might cite cost to upgrade (as in dollars), where others might cite backwards compatibility with legacy applications. Some others might just say that end users “won’t understand” the new ribbon interface, and they might think that the pain and time of training and helpdesk support outweighs the benefits of using Office 2007.

Over the past year I have been in three different places and they all are standardized on Office 2003, and it puzzles me that it isn’t a harder push to upgrade. The benefits of Office 2007 are huge, once you get used to the new interface, and I could go into the benefits but that is probably another blog post, but Outlook 2007 a GTD (getting things done) booster.

As a Business Intelligence guy, it really works for me if every user is on the same client tool, same interface, some quirks and same training, etc. Excel 2007 adds many things when using cubes and pivot tables, and especially with SQL Server Analysis Services 2005, it is a no brainer to use Excel 2007 with SSAS 2005.

In trying to get users of cubes using 2007, there are a few things that I have encountered that can make the transition easier, and today I am going to talk about PivotTable layout and design.

Users of Excel 2003 are used to a pivot table that is laid out in a tabular form, and no subtotals, and maybe grand totals or not. When they use Excel 2007 by default, the are shell shocked by the default pivot table layout and get confused and maybe even sometimes “scared” of what they have gotten into with 2007.

Well, the thing is, it is really easy to get your pivot table to look like a 2003 pivot table in 2007. When you insert a pivot table into Excel, you see this kind of layout.

You can see under the “Pivot Table” tools there is an “Options” and a “Design” tab. Click on the “Design” tab before you set up any dimensions or measures or filters on you pivot table.

These settings on the design tab you can set how you want your Pivot Table to look. To make it “2003 style”, on Subtotals, pick “Do not show subtotals”, On Report Layout, choose “Show in tabular form”. If you don’t to see Grand Totals, then you can turn those off as well. And you can fiddle with the various design options as well.

One thing not on this tab is changing the setting for the +/- on the rows. On the Pivot Table options tab, under the Pivot Table name way on the right, there is an options button.

Here you can tweak other various settings, but you can uncheck “Display expand/collapse buttons” to remove the +/-. As you can see, you can also make the Pivot Table a “classic pivottable” if you really want.

Moving from Excel 2003 to Excel 2007, at least in the Pivot Table and OLAP cube browsing area, shouldn’t be a hard move, and you shouldn’t be scared of it, as you can see you can make your pivot tables look like 2003, or go wild and shift to the new 2007 style.

Categories
Business Intelligence Geeky/Programming Ramblings

Excel 2003 vs Excel 2007

It is the year 2008, we are half way through. Excel 2003 is 5 years old. Stop using it please.

Why? Excel 2003 has the old “limits” – 65,000 rows, 256 columns, memory limits etc. Excel 2007 on the other hand, 1 million row limit, etc etc. That coupled with the way pivot tables work in Excel 2003 compared to 2007, and the SQL Server Analysis Services features with 2007, it is a no brainer to go to 2007.

Companies will say – “But we can’t move all our users to 2007, we can’t afford it” – well, think about just moving your power users. The users that have huge spreadsheet extracts, etc. It is worth it. They can save files in 2003 format if they need to share a smaller file or something, and the 2003 users can install a 2007 viewer.

Other options for huge spreadsheets and extracts are… Access – which your users need training on, or need to be able to adapt to, or another options is SSAS and Cubes, which you need executive buy in, and the infrastructure, and the training to get your users up to speed, and by that time you will want Excel 2007 to connect to the cubes, so…

just start using Excel 2007 – 2009 will be here soon!

Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SQL 2005, SSAS 2005: Using ascmd.exe To Create SQLAgent Jobs That Give You Completion Status

In SQL2005, you can create SQL Agent jobs. They can be scheduled, have multiple steps, alert, notify, etc. Pretty great setup. There are some downfalls though.

Like in order to call a job from a job, you need to execute the second job with T-SQL. Thing is, it doesn’t run synchronously. It runs asynchronously, which really stinks if you want to wait to see if the second job completes successfully or what not.

Another thing, if you call an XMLA script from a SQL Agent job, if the XMLA query or command fails, the SQL Agent job still reports success – that’s no good! What can you do? Use ascmd.exe!

ascmd.exe is a utility that you actually have to download the SQL Samples from CodePlex (http://www.codeplex.com/SqlServerSamples/Release/ProjectReleases.aspx?ReleaseId=4000) and then build the ascmd solution, to get ascmd.exe A few notes. The samples are VS2005, and I don’t have that installed ,so I had to open with VS2008, then it is digitally signed, and when building couldn’t find the .snk file to sign it, so I turned that off as well, after I had it built, I did some testing locally and made sure it would work as I wanted it to.

You can test by just calling it from the cmd line, for example:

ascmd.exe -S SSASServerName -d SSASDatabaseName -i MyXMLA.xmla

you can use -Q and try to pass in XMLA, but then you have to handle all the special characters and what not, which is a pain. Now, if you just put this on your C drive, (or wherever), then create a SQL Agent Job to call this command, it will fail the job if the ascmd.exe reports back failure. Exactly what we want!!

You will notice in the SQL Agent job setup that you can specify the value for a failed command  – 0 is the default and that is what we want.

Now, get some XMLA that you know works, set it up in the xmla file and test it, the job should succeed. Now just change something in the xmla (like CubeId) to some fake value and test it, the job should fail, and you can have it alert your DBA’s or whoever.

Pretty sweet, but I wish SQL Agent would handle failed XMLA like a failed query and report failure, I am not sure if in SQL 2008 it does that or not, but it would make life a lot easier. Otherwise you could be scratching your head trying to find where stuff is failing, looking in logs, etc, but not seeing any failures. The only way you would be able to tell is to run the XMLA manually, ugh 🙂

 

Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SSAS 2005: Cube Perspectives Are Good, But Something Is Missing…

In SQL Server Analysis Services 2005, you can create "perspectives" on cubes. What a perspective can do, is allow you hide different dimensions, measure groups and attributes. This works great but I still think there are a few things that are missing.

The first thing is that the cube itself is a perspective, one that is always there and you cannot hide. In a scenario where you want to build a cube but then have multiple perspectives, but you don’t want end user clients to see the main cube, or use the main perspective, you can’t do it, or at least I cannot find a way to do it. 🙂

The second thing is, which really is more to do with linked objects, is that when you link in a dimension, for instance, you cannot hide or show attributes, you are stuck with what is in the main dimension in your source cube. So what do you do? Use a perspective. But if SSAS let you hide attributes, etc on the dimension, it would let you forgo the use of perspectives.

The third thing is just security in general. You cannot secure a perspective. If you want Accounting to see XYZ perspective, and HR to see ABC perspective, but you don’t want them to see each other’s perspectives, you are out of luck, and need to come up with a new solution, which probably involves crazy security in your cube, or creating new cubes that link in dimensions and measure groups from the main cube.

Don’t get me wrong, SSAS 2005 is a vast improvement over SSAS 2000, but there are just a few things that I feel are missing, or , I might not know about how to enable some of the things I want to do. 🙂 I know SSAS 2008 has more improvements and that will be a good change, hopefully there are some cool things that let you manage your cubes and perspectives a little better.

Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SSAS: Changing Object Id's Breaks Report Builder Reports

Ugh. Found this one out the hard way. Usually when you change underlying object id’s in SQL Server Analysis Services, it shouldn’t cause any harm. You might have some XMLA to process dim’s and measure groups, if so you would have to change those, etc. But all reporting services reports and excel 2007 pivot tables, and MDX should keep working. What breaks? Report Builder reports.

You can build reports with Report Builder (the link is in Reporting Services to open Report Builder) off a cube model. They are paired down reports, you can’t do as much as you can with SSRS, but for advanced end users, they do the trick.

Thing is, the use SemanticQuery XML behind the scenes for the query and data source is to the model, and the XML is build off the object id’s of the cube. Ugh again. Even worse is that all parameters that were set as drop down lists (in this list) type are converted to IN formulas, and all the other params are converted to formulas. Graphs break. Matrix Reports break. Tabular Reports break. It just sucks. They shouldn’t build the query off the underlying id’s of objects, they should build them off the displayed names, like everything else does. Whew 🙂

 

Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SQL 2005 SSAS Deployment Wizard – Where Did My Roles Go?

Ok.. if you are using SSAS 2005 and using the deployment wizard (not using BIDS) – probably because you want to retain your partitions or something. But anyways, if you use it and choose the option to retain your roles, you will deploy, and no one will be able to get to your SSAS DB or Cubes. What gives?

See this link on Microsoft. Doesn’t make sense to me but I guess in Microsoft speak Retain roles and members really equals Screw up roles and members and leave you scratching your head.

Technorati Tags: ,,,,
Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SSRS and SSAS Formatting Dates

Ok, add this one to the annoying bin. If you are writing SSRS (SQL Server Reporting Services) reports of a SSAS (SQL Server Analysis Services) cube, and there is a time dim, you need to format the date parameters in the SSRS report to match the SSAS date format, as a string.

I started out doing this a while ago, like so:

=”[Time].[Calendar Date].&[“& Year(DateAdd(“D”,-1,DateTime.Now())) &”-“& IIf(Len(Month(DateAdd(“D”,-1,DateTime.Now()))) < 2,Month(DateAdd(“D”,-1,DateTime.Now())),”0″&Month(DateAdd(“D”,-1,DateTime.Now()))) &”-“& IIF(Len(Day(DateAdd(“D”,-1,DateTime.Now())).ToString()) < 2,”0″ & Day(DateAdd(“D”,-1,DateTime.Now())).ToString(),Day(DateAdd(“D”,-1,DateTime.Now())).ToString()) &”T00:00:00]”

Wow.. Ugly, hard to read, hard to debug. Just ran into issues, etc.

I decided to finally just write a function to do the same thing and make sure it is correct once and for all..

 

Function GetSSASDate(ByVal DateToRun As DateTime) As String
    Dim result As String = “[Time].[Calendar Date].&[”

    result = result & Year(DateToRun).ToString() & “-”

    If Month(DateToRun).ToString().Length < 2 Then
        result = result & “0”
    End If

    result = result & Month(DateToRun).ToString() & “-”

    If Day(DateToRun).ToString().Length < 2 Then
        result = result & “0”
    End If

    result = result & Day(DateToRun).ToString() & “T00:00:00]”
    Return result
End Function

then, I go to my report properties, Custom Code and add that function, then in my parameter I call it like so:

=Code.GetSSASDate(DateAdd(DateInterval.Day,-1,DateTime.Now()))

Much cleaner, easier and just all around good. If I wanted to take this a step further, I would actually just make an assembly with some useful functions and deploy to my SSRS server, but I will save that for a later time.

Technorati tags: , , , , , , ,
Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SSAS Cubes – Speeding up Queries and Performance

So, you have a SSAS 2005 (SQL Server Analysis Services) Cube, and its not performing how you want it to. What do you do?

The first thing to look at is your attribute relationships (http://technet.microsoft.com/en-us/library/ms174557.aspx). Make sure you don’t have redundant relationships (AS should warn you as well) By default when you create a cube, and go through the wizard, only the default attribute relationships are set up. What this means is that all attributes are related to the key attribute by default. If you have related attributes, you want to set up those relationships!! This will help in a couple of ways. First, when creating hierarchies, it won’t warn you that you don’t have relationships set up. Second, your aggregations will actually have a better design, Analysis Services will be able to take into account the relationships and your aggregations should hopefully rock!

What’s next? Look at your reports and queries off the cube. Can you redesign them? Are you running some crazy query? Here is a good tip for using sp_create_plan_guide to influence query plans.

My suggestion is to run SQL Profiler and see how your cube is being accessed. Are the aggregations being used? Are things being read from cache? Use the BIDS Helper to take a look at your aggregations and see the row counts and make sure everything is working as you have planned.

Ok, what else? Cache Warming.(http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!994.entry) After running Profiler, or even setting up something automated to capture the queries being ran into another table, you can use this info to “warm your cache”. Basically when you process your cube, run the most frequently ran queries automatically so then the next time users run them, it will pull from cache.

Balancing your cache, aggregations and storage design modes is something you seriously want to dig into. Should you be purely MOLAP? Or MOLAP with Proactive Caching? Or do you want to go purely ROLAP or all the different designs in between the two? You want to minimize data latency.

Some of these things often get overlooked when first designing a cube, which then could make it tough to do once you have things deployed to end users. Also, SSAS 2005 isn’t the best at letting you tweak some of this stuff, I watched a presentation on SSAS 2008 by Donald Farmer and things look A LOT better in SSAS 2008, which I cannot wait for!

But that is a post for another day 🙂

Technorati tags: , , , , , , , , , , , ,