Tag | Analysis Services Posts

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 ...
Chris Webb recently blogged about a conversation that a group of us were having where the topic of the Timeout connection string property came up. Chris asked how I found out about this property. The technique I used is handy for exploring all types of connection string properties, so I thought I would share it here. Create a new text document on your desktop. Rename it to test.udl (its the extension that matters, the name can be anything you like) double click on this file and it brings up a "Data ...
Occasionally I have the pleasure of working with Analysis Services 2005 on an IA64 (64 bit Intel Itanium) servers and on the whole, most things work the same. But there is the occasional small difference that can cause some frustration. Background: In Analysis Services 2000, you could not use VBA functions in MDX calculations as the VBA library was COM based and had not been ported to the 64 bit platform. In SSAS 2005, the core VBA functions have been implemented in a .Net library which will run ...
[via Stefan Gossner] Wow, there is a bit over 1Gb over video content that can be downloaded for your viewing pleasure. I'm off to get my external USB hard drive. Below is a excerpt from the download site: Overview The Microsoft Office System Developers Conference 2006 featured more than 60 breakout sessions organized in eight technical tracks. In this track, you'll learn how the 2007 Microsoft Office system helps developers build smart client and browser-based BI solutions leveraging a wide array ...
Back in April, Chris Webb posted an entry on Analysis Services stored procedures asking for volunteers to joining him in creating a few useful examples that people could download. I was one of those volunteers and after several months of work behind the scenes, we are at a point where we are happy to release beta 1 on CodePlex here: http://www.codeplex.com/Wik... Have a read of the CodePlex site - it's reasonably well documented, and you can download the source ...
There was a question recently on the Analysis Services forum on how to enumerate calculated measures from AMO here. Unfortunately you can't do this from AMO. The best you can do is to get the MDX script and then parse it. If you need to do this the following Regular Expression should come in handy. (\bCREATE\s*MEMBER\s*.*\[?m... |(?:\bCREATE\s*(\[(?<Cal... I have wrapped up this regex ...
There are not a lot of example XMLA queries available, so I thought I might work through some examples on my blog. This first example was prompted be a question in the Olap newsgroup and demonstrates how to get a list of databases and cubes. Getting a list of the databases from Analysis Services is fairly easy with a simple XMLA query like the following: <Discover xmlns="urn:schemas-microsof... <RequestType>DBSCHEMA... <Restrictions /> ...
Resume – Senior, Hands-On, .NET, C#, VB, ASP, XML, SQL, SharePoint, Crystal, Reporting Services Developer, Team Leader, Hard-Core Coder Rodney Vinyard, 29 Windermere Road, Montclair, NJ 07043 rodney.vinyard@gmail.com · Seeking North NJ/Manhattan .NET/SQL developer job. · Hands-on .NET, C#, VB, SQL, ASP, XML, XSLT, Reporting Services, Crystal. · 30 years of productive, software engineering experience. · Inventive, focused, hands-on software designer, engineer & coder. · Effective leader, writer ...
There was an interesting question on the Analysis Services newsgroup tonight which involved a requirement to produce a product of a set of values. ie. value1 * value2 * value3 * .... valueN You could do something like this with a unary operator, but that would change the aggregation for all measures. In order to do this for just a single measure I proposed exploiting a technique I saw Itzik Ben-Gan use in T-SQL. The basic mathematical proof for this technique was the following: logN (val1*val2*...*valn) ...
I noticed that the scripts library at http://scripts.readify.net/ had an example on returning results from a SQL table. Not to be out done by the relational guys, I thought I would post an example of how to do the same thing against an Analysis Services 2005 cube. I can see that this could have some value in a production environment in that it could be used to automate the validation that a given tuples in the cube matched the results from a source system.function get-cell { param([string] $server ...
This is my first blog post ever. D'Arcy convinced me that I should start a blog to talk about all the good times at Tech-Ed. So here we go. I'm headed down to Boston for Tech-Ed 2006 in a couple days. Should be a great time. This is my first Tech-Ed and I've been looking through the list of sessions available and it's pretty overwhelming. Out of the 700 or sessions available, I picked out about 50 or so I might be interested in attending. I'll probably only have time for about a dozen or so, so I ...
[via Mosha] Edward is Program Manager for Analysis Services and is very active on the Analysis Services forum and he has just started a blog at http://www.sqljunkies.com/W... He has a great first post on connectivity problems which is a recurring theme in the forum and newsgroup. Welcome to blogging Edward ...
I love Chris Harrington's Thin Olap site http://www.activeinterface.... which has a stack of examples on sending XMLA using VBScript. However I recently came across an easier way to send XMLA to Analysis Services using .Net. There is an assembly called Microsoft.AnalysisServices.... that is distributed with Analysis Services 2005, it is installed in GAC on the server or can be found in “C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies... by default. If you add ...
I recently had an interchange with someone on the Olap newsgroup who was having trouble with a Report Builder model based on their Analysis Services UDM. Just as a bit of background, Marco Russo has an excellent post here http://sqljunkies.com/WebLo... on how to build a report model based on an Analysis Services UDM. This is not exactly what I would call an intuative operation, but I am assuming that the function was put in SSMS rather than BIDS as there is no real ...
Tonight's Toronto SharePoint Users Group meeting will feature the Business Scorecard Manager: Business Scorecards provide graphical snapshots of your business data, revealing underlying trends and critical inflection points. Come learn out how this latest Business Intelligence tool works with SQL Server Analysis Services (for SQL 2000 or 2005) to make real-time business dashboards that management will love. [See a demo] You can register at the link above. See you there ...
The more I play with PowerShell the more I like it. And now that I have a provider working that allows me to link in an Analysis Services server and navigate through the object hierarchy. Here is an early picture of the Shell (developed while PowerShell was still know by its code name of Monad) The screenshot above shows a PowerShell console that is connected to a drive called “amo“ which is linked to the SQL05 instance of Analysis Services 2005 on my localhost. One of the biggest differences ...
I think I'm with Mitch on this one. I'm not overly keen on “PowerShell“, the new name for Monad. The product team have already renamed their blog to PowerShell, so it could be pretty much a done deal, but there seems to be a fair bit of criticism on Scobles post about the new name. While there are some people who point out some worse possible names, there are not many people showering the new name in praises. I quite liked Monad and even MSH was acceptable (although I don't know if Microsoft ...
There was a question on the newsgroup a few months ago asking how to extract data from an Analysis Services cube and store it in a SQL table using SSIS. I responded saying that in theory you should be able to setup a connection using the OLE DB provider for MSOLAP 9.0 and then put an MDX query in place of the SQL text. And I even when as far as setting up a package and previewing the data to make sure this was a viable approach. To this point everything looked fine and I assumed that the next step ...
I have been playing with Monad (also known as MSH) for a couple of weeks now... and I like it! It is the new scripting engine from Microsoft that is being developed as part of WinFx. I started hearing whispers about Monad a few weeks ago, just before Scott Hanselman did his podcast on the subject. This stuff looks really cool, you can seamlessly get access WMI, COM and .Net components. There appear to be Monad resources popping up all over the web. The guys at Readify have started a script library ...
There was a question recently on the Analysis Services newsgroup asking how to process an Analysis Services 2005 cube using XMLA from the command line. Below is a script based on Chris Harrington's excellent Thin Olap blog. This is the absolute minimum script required to get an Analysis Services database to process (ie. No error handling <g>). On my machine I processed my “Sandpit“ database (the sandpit is where I play) with the following command cscript xmlaProcess.vbs Sandpit ...
I did a presentation at the Melbourne SQL Server User Group this week on the proactive caching feature in Analysis Services 2005. One of the topics that I covered was the different notification methods that Analysis Services 2005 can make use of in order to discover that something has changed in the underlying relational source. The first notification method on the list is SQL Server notifications, but you will notice that as soon as you select this option a warning message appears at the bottom ...

I'm speaking this Tuesday at the Melbourne SQL Server User Group on the new real-time BI features in Analysis Services 2005.

Details of the event can be found at http://www.sqlserver.org.au/Events/ViewEvent.aspx?EventId=154

I'm currently going over (& over) my presentation and triple checking my demos. :)

If you migrate an Analysis Services 2000 database to Analysis Services 2005 or if you have a database that has been edited directly you may find yourself without an upto date solution file. This is only really an issue when you want to make changes to the database and test them in a separate environment. Even then it is not much of an issue as you can easily create an XMLA script of the database and deploy it onto another server or instance. But BI Development Studio (BIDS) solutions do have a couple ...
I am writing this after a failed initial attempt to install Microsoft Business Scorecard Manager Evaluation Kit. My problems started because I tried to fly by the seat of my pants using the 5 DVDs that should have combined to form a usable BSM environment. Unfortunately, not quite everything you need is included and part of my problems had to do with the use of evaluation software. Also, there are still some legacy requirements left over from the original development of BSM that used SQL Server 2000 ...
BizTalk Server 2004 is not cluster aware, therefore it is not recommended to install BizTalk 2004 on a MSCS based server cluster. However, to achieve high availability of you BizTalk solution, you can still take the following actions. (I assume you are installing BizTalk 2004, SQL Server 2000 on Windows Server 2003) 1. Setup an Active\Passive server cluster based on Microsoft Cluster Services Install SQL Server 2000 with service pack 3a on the cluster. This step is very easy because SQL Server 2000 ...
I had an interesting experience this afternoon which I thought I would share in the hopes that it may save someone else the pain I went through. Today I moved a production Analysis Services server off a machine which was running both SQL Server and Analysis Services onto it's own machine for one of my clients. The repository was in SQL Server, so after installing AS we simply copied the data files off the old server, changed the repository connection string and we had our new server up and running ...
[via Chris Webb] I've just noticed Dave Wickert's post on the following thread on the Analysis Services MSDN forum, and thought I'd flag it up: http://forums.microsoft.com... If you've ever wanted to be able to execute MDX or XMLA from the command line then you might want to sign up to be a beta tester for the tool he's co-developing. Here's a summary from the original post: With it you can execute either an XMLA script or an MDX query. Input and output can ...
Chris Webb and a couple of other people mentioned in the last couple of months that if you see an error in a Microsoft help file, not to whine about it, but to click on the feedback link and let them know about the issue. I spotted a small error (back in November last year) where count measures in Analysis Services 2005 were listed as being semi-additive, when in fact they are additive. So I decided to give it a go and sent some feedback in. Well, I received feedback from a Programming Writer at ...
It appears that there is a bug in the RTM release of SQL Server when dealing with a linked server to an Analysis Services server. When you issue a query that has a “WITH MEMBER“ or “WITH SET“ clause like the following simple query: USE master GO /* Add new linked server */EXEC sp_addlinkedserver @server='LINKED_OLAP', -- local SQL name given to the linked server@srvproduct='', -- not used @provider='MSOLAP.3', -- OLE DB provider (the .2 means the SQL2K version) @datasrc='localhost', ...
[via Mosha Pasumansky] Mosha has recently released a couple of excellent posts on hist blog: Inside OLAP Engine: Cache Prefetching This is an extract from a whitepaper that Mosha is working on and he is asking for feedback on the technical level of the content. I thought it was great, I can't wait for the whole whitepaper to come out. Default members, MDX Scripts, Security, KPIs and Perspectives Another great post which gives some great insight on why some things work the way they do. It is knowing ...
If you're like me and LOVE samples and SDKs, MS just released this. It contains about 100 samples to exploit the new features of SQL 2005. These samples cover everything from Analysis Services to Integration Services. I think if you've been reading about all that SQL 2005 can do, but haven't been in the drivers seat yet download these and buckle up. Enjoy. -B ...
I guess I was sleeping and forgot to post the answers to the BizTalk Interview Questions from a few posts ago. No worries though, Gustavo Echeverry went thought and answered them complete with reference! Great work! In any case, here are my answers. Of course, in some cases it may be possible to have more then one right answer. Development Focused Questions What is BizTalk? · Biztalk is a messaging based integration tool. · Bonus: It consists of several different pieces including Business ...
Today the goodness just keeps on coming. I just read a post from Dave Wickert on the Olap newsgroup about the newly released feature pack for SQL Server 2005. It contains all the following goodies. Microsoft ADOMD.NET (x86, x64, ia64) Microsoft Core XML Services (MSXML) 6.0 Microsoft OLEDB Provider for DB2 (x86) Microsoft Operations Manager 2005 Management Pack for Microsoft SQL Server 2005 (coming soon) Microsoft SQL Server 2000 PivotTable Services (x86) Microsoft SQL Server 2000 DTS Designer Components ...
There have been a couple of questions on the Microsoft OLAP newsgroup (microsoft.public.sqlserver... recently about making dimension members appear in more than one place in the dimension. Have a look at this article for one method of implementing this. [More] [Updated 27 Oct 2005] I have add more detail on the Analysis Services 2005 solution as it was a little bit light on regarding exactly how the dimension usage was set up ...
There are a number of different methods available for backing up your Analysis Services 2000 database. I recently pulled together this vbscript for automating the calling of msmdarch.exe and thought I would post an article on the full range of backup/restore options. Analysis Manager This is the method that most people are aware of. It is fine for migrating databases, or for restoring databases. But for archiving/backing up databases you really want something that can be automated. If your database ...
I have a number of DSO scripts that I developed a couple of years ago when I was working on a very large Analysis Services 2000 project. I was recently catching up on the OLAP newsgroup and noticed that there was someone asking about DSO scripts for copying aggregations. I had one such beast in my tool box and decided to post it on my blog. As I get time I will go through and pull out some of the other more generic scripts and put them up here too. Copying Aggregations Script List Source Tables (added ...
Deploying BizTalk Server 2004 - Complete / Clustered - Production Setup (NOTE: If you find any errors here - please feel free to point them out - I worked hard to document each step and leave nothing out. Some of the steps that are not detailed are so because the process is pretty straight-foward.) This document takes the following considerations into account. Please note: You need to be familiar with basic network naming conventions for this setup. Before beginning the processes to setup BizTalk ...
My current project used Microsoft Business Framework (MBF), which is still under development I think. No wonder the resources are limited. Hard for me to understand the framework. It is based on MVC. everyone know that right. I still seeking for small-to-medium framework for my internal project. CSLA is one of my consideration. I also want to integrate Analysis Services and Reporting Services. I have read some article. It seems that knowledge in MDX is a must to integrate Analysis Services and Reporting ...
BizTalk Server 2004 Setup/Architecture – Stand-Alone Instance(s) To simplify this documentation, the BTS server is BTSSERVER and we will use SQL Server on a remote SQL Server called SQLSERVER. Service Account Setup Perform the following service account setup on both of these servers, and be sure the noted pre-requisite components are all in place: Login as Administrator, or an account that is a member of the Administrator’s Group on the machine. Create a user account that will be used ...
Resources Covered: - · Introduction· Review of Features· Enhancements· DTS Redefined· Deployment and Management· Hand Shake of Brothers (.NET and YUKON) or Marriage of Romeo and Juliet.Author: - Veer Ji WangooApplies to : - SQL SERVER “YUKON” Beta 1Target Audience :- SQL DBA,Solution Architects,IT analysts ,Developers etcIntroductionThe world of Programming had never moved so fast as it did in last couple of years. After Y2K the things got so fast that it ...
Yes. Today we will be distributing requests for evals to be submitted. Information on how to fill out an eval will be handed out during the second timeslot, as well as set in the CommNet area. The Mobile Devices team has secured SmartPhones for a drawing for those who submit evals. Fortunately the eval counts are going up. It's a good thing, the statistical data will be stronger for us to evaluate where we hit the mark. As of Thursday morning, this is who is hot: Top 10 Sessions - Event Running Rank ...
I ran into Kimberly L. Tripp this morning and she told me that she had someone tell her how much they had gotten from her presentation. Kimberly asked if they had posted their evaluation on CommNet, and they responded saying they hadn't and questioned if they had to log in. Yes. You have to log in to CommNet to fill out an eval. And, please do so. We do take the evals very seriously. It helps us decide which sessions will go on to the worldwide TechEds. It helps us train the speakers. And most importantly, ...
We had a couple of the boys from Panorama Software in today to give us a demo of their business intelligence offering. To be honest, I was expecting to be less than impressed. In a nutshell, this is a front end to OLAP. All I have to say is AMAZING! This product is one of the best applications I have seen. They provide information in a rich browser that uses DHTML (I believe) so there's no JVM or ActiveX to worry about, they have an Excel plug in, and they can also integrate with SQL Server's new ...