Tag | Analysis Services Posts

You have designed Aggregations for your cube, but how do you know that they are currently processed? Hopefully you have your processing routines setup in production so that your indexes are always kept processed. But maybe you are working in a development environment or you are performance tuning that you want to double check that your aggregations are currently processed. It is not immediately obvious how you can figure if the indexes for a partition or a set of partitions are processed as this ...
How do you stay up on new technologies? How do you approach learning a specific new technology? The first thing you have to accept is that the Microsoft technology stack has gotten so vast that it is simply impossible for a single person to be an expert in everything. This is the most important aspect to remember. This post is simply to describe my personal approach for staying up on new technologies in the hopes that other people will find something valuable in this to add to their toolbox. In terms ...
You may be thinking oh-no, not another one. Allan Mitchell recently posted an example of an SSIS package that would warm the SSAS cache which is an updated version with a slightly simpler SSIS data flow from something Chris Webb originally blogged. Being a PowerShell fan as I read Allan's post I realised that most of the tasks mapped to native PowerShell cmdlets and I already had PowerShell code to execute an MDX command, so all I was missing was someway of reading in the trace data. What I ended ...
Date: October 23 Time: 9am - 12:00am Location: New Horizons of MN 4510 W 77th St Suite 210 Edina MN 55435 Registration Link: http://www.nhmn.com/Courses... Seminar Overview This session will explore the various Business Intelligence options available from Microsoft and how they integrate with Microsoft Office SharePoint Server 2007. This session will include a number of demonstrations and tips on how to get started using SharePoint for business intelligence. Topics to ...
In the Baton Rouge Area with as open Tuesday afternoon? Check out the SQL Server Users Group Meeting this week. Location: At Lamar Advertising Thursday, October 23, 2008 5:45 PM - 8:00 PM Sponsored By: FuelTrac Presenter: Mike Huguet BIO Mike is an Enterprise Solutions Architect for Sparkhound, Inc. with over 9 years of experience in developing business solutions for Enterprise, Mid-market, and governmental customers. Five of those years have been as a consultant working with clients such as Jiffy ...
Last year I logged an issue on the connect site around deploying from BIDS and renamed databases. https://connect.microsoft.c... I got a couple of messages relating to this issue, one saying that it was being closed as it was a duplicate issue and then another one recently where it was updated to indicate that the issue has been fixed in SQL Server 2008. I think it is really great to get this sort of feedback that something is being done, unfortunately ...
Website: http://www.nhmn.com/Courses... Class Overview This three-day, instructor-led course provides students with the knowledge and skills to write MDX expressions, calculations and queries for Microsoft SQL Server 2005 Analysis Services in order to implement the most demanding requirements for a Business Intelligence project. This course was written by Chris Webb, a mentor with Solid Quality Learning. He is a specialist in Analysis Services and MDX, is a co-author of the ...
There was a question on the SSAS forum recently asking if it was possible to verify a backup file. Now if you have checked in Books Online to check the XMLA backup and restore commands, checked the UI in SSMS and even used reflector against the AMO library you would think that there was no way of doing this. I certainly did. However try the following... Navigate to: <Program Files>\Microsoft SQL Server\MSAS10.SQL08\OLAP\bin then type: msmdsrv /? And you will get the following information: Usage: ...
When trying to install the Adventure Works 2008 sample database on my new SQL Server 2008 instance I very quickly got a message box about the installation failing with an error of 2738. For some reason this sounded familiar and sure enough a quick search turned up this post from Mitch (see: The error code is 2738) where he had the same error (with a different installer). It appears to be the same issue as the same fix worked for me and now I am up and running with the new sample database. Technorati ...
In PerformancePoint 2007 you have the capability to setup custom properties and pass them into dashboard objects. This is especially useful for simplifying dashboards where you are linking scorecard KPIs to report views. Alyson Powell Erwin outlines how to do this in the PerformancePoint MSDN blog: http://blogs.msdn.com/perfo... This is very handy. By setting up a custom property that contains measure names for ...
Every now and then on the SSAS MSDN forum, the issue of doing a generic "ratio to parent" calculation comes up. Unfortunately there are a number of problems with the premise of giving users a generic "ratio to parent" measure. The first is that the concept of "parent", by definition, requires a hierarchy and SSAS 2005 supports multiple hierarchies. Consider this query: SELECT Non Empty [Product].[Product].[Produc... on Rows, {Measures.[Sales Ratio]} ON Columns FROM [Adventure Works] I am using ...
Solving Business Problems in MDX http://www.nhmn.com/Courses... November 10-12 Location: New Horizons of MN (www.nhmn.com) based in Edina MN Class Overview This three-day, instructor-led course provides students with the knowledge and skills to write MDX expressions, calculations and queries for Microsoft SQL Server 2005 Analysis Services in order to implement the most demanding requirements for a Business Intelligence project. This course was written by Chris Webb, a mentor ...
Do you need training on PerformancePoint Server? New Horizons of MN in partnership with Solid Quality Mentors has a class coming up the week of July 7 in Edina MN. Link for more information: http://www.nhmn.com/Courses... Course Title: Microsoft Office PerformancePoint Server 2007 End-to-End Class Overview Written and delivered by industry experts, this five-day course provides students with the technical skills required to design, develop and manage solutions using ...
A little while ago Jamie posted about how he formats SQL code which happens to be quite similar to the way I do it. But this then lead me to think about the equivalent formatting for MDX. So if I get MDX like the following:with member measures.ptd as 'sum(periodstodate([Date].[... [Date].[Calendar].currentme... Amount] )',format_string = "currency" select {[Measures].[Measures].[Sales Amount] ,measures.ptd} on 0, {[Date].[Calendar].[Month].... ...
Our www.Phillydotnet.org second installment of the 2008 Code Camp series will be held at the DeVry University campus in Fort Washington, PA on Saturday, May 17 from 8:30-5:00. Please register on our web site.-->Detailed directions are on the DeVry web site. sold out. There will be another Code Camp in May.--> Lots of code, just say no to slides! 8+ hours 40+ sessions (8:30, 10:00, 12:30, 2:00, 3:30) 8 tracks + lunchtime entertainment 500 seats with tables (laptops welcome) Free breakfast, lunch, ...
I had a comment recently on one of my older posts that I did about XMLA which asked about listing cubes in a database and whether the cube is processed and it's size. I have posted about a number of different ways of doing this over the last few years, but I thought I would use this opportunity to consolidate a list all the various ways of doing this that I could think of. Under the covers all these techniques are ultimately issuing some sort of XMLA discover against the SSAS database and returning ...
After my last post on how to list the aggregations for a given attribute using Powershell and PowerSSAS, miltruenos commented, asked if it was possible to do a similar script to show roles and allowed/denied sets, so here is just such a script. add-PSSnapin powerSSASnew-PSDrive ssas powerssas localhost $roles = gi "\Databases\Adventure Works DW\" ` | % {$_.Dimensions} ` | % {$_.DimensionPermissions} ` | % {$_.AttributePermissions} ` | add-Member ScriptProperty DimensionName {$this.Parent.Parent.Name}-... ...
If you have ever manually edited aggregation designs, you may have wondered what aggregation currently exist for a particular attribute. Well the following powerSSAS script will give you just that. (you could do this with straight PowerShell, but powerSSAS just makes it slightly easier to address a given measure group) Technically this script is just one single statement, with a couple of parameters to make it more re-usable. I have broken it up in order to make more readable by using the PowerShell ...
I had a question recently about how to quickly convert all the attributes in a given database from UPPER CASE to Title Case. Now you could certainly write a C# or VB.Net based program, but I feel that a script is the way to go for a once off requirement like this and what better scripting environment could we ask for than PowerShell. So here is a short PowerShell script that does the job. [System.Reflection.Assembly... > $null [Microsoft.AnalysisServices... ...
So, Mike Wells was kind enough to pass this information on to me so I will do the same… Register to Be the "First to Know" and Save on Your Exams (Worldwide) Know when Visual Studio 2008 and SQL Server 2008 exams are available and save 40% on selected exams in your track. Try Your Choice of Microsoft E-Learning for Free (U.S. only) For a limited time, you can try Official Microsoft E-Learning for free! Choose from 1,400 hours of IT professional and developer content covering all of the latest technology ...
I just spotted an interesting post on the Analysis Services forum. It was titled "Analysis Services Team Update" and was posted by Ariel Netz, Group Program Manager for Analysis Services. He starts off with .. "I could never understand people’s fascination with blogs." ...which just begged to be blogged about. :) In it he talks about how Analysis Services is looking for the SQL Server 2008 "Katmai" release "In all honesty, things are looking good. In fact, looking very good (relative to where we ...
I was just checking the DMVs in SSAS 2008 CTP6 to see if they had changed between CPT5 & 6. Particularly in regard to the list of limitations that Vidas posted. As far as I can tell the only thing that appears to have changed is that "SELECT DISTINCT" now appears to work. Technorati Tags: Analysis Services, DMV ...
Many legacy Analysis Services (or OLAP Services) implementations use ProClarity as a querying tool and publishing framework. The server infrastructure of ProClarity allows crafty implementers to push much of the customization in KPI’s and additional measures into the “Presentation” layer of the infrastructure. This is easier to manage and change in many cases than creating named sets and calculated members in the cubes as this generally takes a SSAS administrator. With Microsoft purchasing ProClarity ...
Following along on my recent theme of exploring the metadata rowsets and the new Dynamic Management Views (DMV) in SSAS 2008. I have added a new DMV function to the Analysis Services Stored Procedure project (www.codeplex.com/ASStoredP... which is included in the v1.2 release that I put out just before Christmas. This all started off when I decided to look at extending the existing Discover() function to provide for sorting and filtering. And I originally started off by building a wrapper ...
Disclaimer – this article describes a fix that worked for me – YMMV…proceed with caution. I recently encountered a weird error with the TFSWarehouse data cube and reporting services for our site. It started out innocently enough with an error I hadn’t seen before in the event log (shown below) Well that was odd enough because it suddenly appeared out of nowhere, but then I also noticed odd things going on with the reports that TFS generates. It was generating reports but it was all stale data…apparently ...
Mosha commented on my last post on this topic that there was another simpler way of doing the same thing from SSMS. And that is to execute the following commands from an MDX window. First run ...<BeginTransaction xmlns="http://schemas.micro... /> and then...<Lock xmlns="http://schemas.micro... <ID>496CEC1F-D66A-4C8... <Object> <DatabaseID>Adventure Works DW</DatabaseID> ...
If you have a reasonably large Analysis Services implementation with users running adhoc queries via multiple tools (ProClarity, Excel, etc...) then you have probably run into problems with long-running queries. While longer-running queries may be acceptable in many data warehousing implementations, there will occasionally be a query (maybe even more than one ;) ) that causes resource issues and runs beyond the established limits of reason for your environment. Analysis Services does not have a native ...
I put the following code sample together in response to this question on the Analysis Services forum. If you read Books Online, you might think that running the following statement in SSMS would work : <Lock xmlns="http://schemas.micro... <ID>496CEC1F-D66A-4C8... <Object> <DatabaseID>Adventure Works DW</DatabaseID> </Object> <Mode>CommitShared<... But it will throw the following ...
Just in time for another early Christmas present the Analysis Services Stored Procedure team have just released version 1.2. It incorporates and couple of new functions and a few alterations. Below are the main additions, you can follow the links to the wiki pages for more information about each of the following. StrToSet - is a new class that incorporates a couple of new functions. The functions in this class are designed to allow the StrToSet function to receive shorter strings by passing in just ...
Last Tuesday night witnessed a relatively new kind event held in Microsoft Egypt building @ smart village. It was said to be "announcing dotNETwork user group" which I thought was some Microsoft Egypt initiative for a new offline user group model of developer community. I was so excited about so, and it was even better! The Idea The dotNETwork group is just am offline user group that's done right. It just happened to have Microsoft Egypt host the group gathering, as this is the hardest issue to maintain. ...
I was reading Vidas Matelis' recent post on the metadata rowsets in SSAS 2008 and this got me to thinking about a discussion that Chris Webb and I had recently about the new "DMV" functionality is SSAS 2008. And that it basically that they are simply gives you an easier way to access information that is available through XMLA discover commands. And that you could already get to this information relatively easily through the Discover() function in the Analysis Services Stored Procedure (ASSP) project. ...
The November CTP of SQL Server 2008 was released on the connect MSDN Downloads site at the end of last week and when you open up a cube in BIDS you will see a new tab for aggregations. I figured I might give you a bit a walk through what you can expect from this new tab. What this tab does is to let you see all the aggregation designs that relate to a given measure group. It also lets you manage which partitions are using a particular aggregation design. As with the other Analysis Services designers, ...
Microsoft has a couple of articles on how to set up HTTP connectivity for SSAS, one for Win XP http://www.microsoft.com/te... and another for Win2003 server http://www.microsoft.com/te... But Vista Business/Ultimate includes the new version of IIS (IIS 7) which means some of the steps have changed a little. So let's walk through the process with the help of a few screen shots. Getting binariesCopy the contents of the %Installation ...
The SQL Best Practices Team has just published a new article entitled Scale-Out Querying with Analysis Services Using SAN Snapshots. The best practices site doesn't mention it yet (it was just posted today) but it can be found by searching the downloads site. This is one of the available methods for scaling out SSAS queries and is especially useful on large installations. It describes using SAN snapshots in a load-balanded environment to handle more simultaneous queries. Kudos to the best practices ...
I use the Templates feature in SSMS a fair bit when I am working with XML/A, but I sometimes wonder how many people are aware that it exists. On my copy of SSMS, it is docked on the right hand side. If you cannot see the Template explorer at all, you can access it through the View menu. Have a look at the following list of templates that are available for MDX and XMLA for Analysis Services. This feature is really great for XMLA queries. I don't really know of anyone that sits down and writes XMLA ...
I've been working on a custom process template for a client in TFS and came across some things that I didn't know. Now I am not an Analysis services guy so this was a little new to me. It took me forever to find decent definitions on how to correctly set "reportable" in TFS. The docs on this, how to use it, and what it should be set to, etc can be a bit tricky to find. It wasn't until I picked up Professional Team Foundation System that I got a clear answer. After a little bit of tinkering and some ...
Here is another gem from the Analysis Services MSDN forum by Adrian Dumitrascu from the product team. The one from this thread deals with what exactly the .Update() method does. And was in the context of talking about creating new roles in a database. MajorObject.Update() Method Even if you create the role as new, it's still not needed to call database.Update(). Calling role.Update() is enough. The .Update() method only saves the minor properties and collections (thus not the major children) of an ...
Greg Galloway commented on the previous post I made about exporting captured MDX queries from SQL Profiler, indicating that there was an issue with using the MDX cap Greg has posted an issue on connect which you can vote on here, but I can't see this behaviour changing in the near future. Maybe in Katmai they could make the extract smart enough to do the parameter replacement as it creates the .mdx file. As far as I am aware the Analysis Services provider in SQL Server Reporting Services (SSRS) 2005 ...
I am speaking tomorrow night at the Melbourne SQL Server User Group. The presentation will be based on the excellent white paper that Marco Russo produced about Many-to-Many relationships in SSAS 2005 called the Many-to-Many Revolution. Here is the session abstract: The Many-to-Many Revolution ---------------------------... Do you have a situation with something like categories that have one or more customers and customers that fit into more than one of these categories? ...
Having written quite a few .Net Stored Procedures for the Analysis Services Stored Procedure project, I have often needed to debug into these stored procedures and it is pretty much second nature. But I had a question about this recently which made me realise that getting this working is not really well documented. There is a page in the product documentation which probably has enough information to get you going, but to my mind some of the steps are in the wrong order - and there are no pictures ...
The following announcement was made recently on the Analysis Services forum. A collection of best practices on the design of OLAP objects in Analysis Services 2005 is now available at http://www.microsoft.com/te... These tips were gathered from the product team and our parners in order to help people create better OLAP databases. It looks like a lot of these Best Practices have been incorporated into the SQL Server Best Practices Analyzer tool ...
Overview Software Architect with 16 years of development experience using a myriad of technologies. Have exercised technical leadership in all stages of software lifecycle via collaboration with business stakeholders in defining system requirements, object-oriented analysis and design, design reviews, code reviews, and collaboration with developers and IT staff in troubleshooting/debugging production issues. Have written reusable frameworks and components to provide rapid delivery of reliable, extensible ...
There was a question recently on the Analysis Services forum asking how to get a list of the Warnings from a Process() method in AMO. There is an overload to the Process methods which includes an xmlaWarning collection, so this should be a pretty easy thing to do - right? Wrong! It's not as easy as it first appears. After inserting an invalid record into the fact table of a test cube, I was able to process the cube using the Business Intelligence Development Studio (BIDS) and see the relevant warnings, ...
Before you install SP2 you really should read the readme file. I have already seen one post on the MSDN forum who ran into the issue documented in section 5.3.2 of the readme. You can find the ReadMe file here http://download.microsoft.c... And the What's new file here http://download.microsoft.c... There are a lot of good things in SP2, ...
I work as a systems developer at a large food manufacturing firm in Australia. Recently we have been fighting with Microsoft's Analysis Services about a certain cube we were wanting processed. The quick details - Database size: 25.6Gb Dimensions: 9 Storage Model: MOLAP The data was the entire set of sales figures for the years 2002 to present in weekly installments. We had to process this cube during off-peak hours as the server it was running on was a high-demand corporate server. Taking this into ...
This post became too long that I had to split to two parts (1, 2). I intended to write this after the 3rd day in MDC, but I got REALLY busy after that, I was too stressed for Dody's travelling as well (he's already outside Egypt now, should be back in three weeks), and really tired as well, and even sort of sick too :(. I thought it was too late to blog it afterwards, until my friend Mohamed R. Samy called me yesterday, and we had discussions on some of the few good parts of the last MDC (mainly ...
Microsoft SQL Server 2005 Service Pack 2 (SP2) enables all editions of SQL Server 2005 to take advantage of the enhancements within Windows Vista and the 2007 Office System including SQL Server Analysis Services and Reporting Services improvements for Office 2007; increased interoperability; and enhanced manageability and data compression. http://www.microsoft.com/do... ...
Amazon has kindly let me know that, based on some of my previous purchases (notice the classic use of data mining here?), I might be interested in Edward Melomed's soon to be released book Microsoft SQL Server Analysis Services. Edward is a program manager on the development team at Microsoft and he and a few of the other co-authors joined Microsoft as part of Microsoft's aquisition of OLAP Services from Panorama back in the SQL Server 7.0 timeframe, so hopefully this book should have a few insights ...
A few of us that worked on the Analysis Services Stored Procedure project http://www.codeplex.com/ASS... were having a discussion on email the other day and the issue of trying to override some of the built-in functions with a different implementation came up. (specifically some of the Excel functions) My first attempt at this was to register an assembly at the server level with a name of "Excel", thinking that when you called "Excel.Round(...)" it might call my assembly instead of the ...
There was a thread today on the Analysis Services forum where someone appeared to be having an issue with a data type overflow. Multiple large, positive, integer values were aggregating up to a negative amount. If you are interested the full thread is here: Re- negative values on a measure Basically the database and the DSV were increased to a bigint data type, but the issue was still occurring. What I believe is happening here is that the measure in the cube was set with an int data type. To fix ...