ssas

What is the best way to use SQL Server Analysis Services data in a line of business application?

We'd like to see if we can get some improved performance for analysis and reporting by moving some of our key data into Analysis Services cubes. However, I haven't been able to find much in the way of good client front ends. Our users have Office 2003. The move to 2007 is probably at least a year out and the Analysis Services add-in fo...

Cannot display measure variance in Excel pivot table

I'm trying to display the difference in a measure from one day to the next in Excel (2007) by setting the value field setting to 'Difference From' with Base field = day and Base item = previous. If my underlying data is a SSAS cube, the value that displays for each cell is #N/A. If my underlying data is a set of records on another worksh...

First and last measure dates for sets in MDX

I'm looking for some guidance on how to approach an MDX query. My situation is that I have sales occuring, which make up the grain of the fact table, and are measures. I have a products dimension and a customer dimension. I also have a date dimension and a time dimension, I made them seperate to keep member counts low on the dimensions. ...

Why isn't SSAS loading any measure data for my cube?

Background: I'm writing a data provider that SSAS will use to get data from a third-party system. I wrote a tool that will build cubes, dimensions, measure groups and measures based on the configuration of the third-party system using AMO. Now, in the Cube Browser, I am able to pull the members of the dimensions, however, measure data ...

How to use 3rd dimension in MDX query (ON PAGES syntax)

Hello, I have a problems with 3rd dimension in MDX Query (on MS SQL Server 2005). I can use 3rd dimension in Visual Basic (I have a cube there, using browser I can make 3 dim. queries -- owing to ON PAGES). I snooped it via MS SQL Profiler (it records databases queries). But when I tried to put the query into MS SQL SERVER, only thing wh...

How do I report on completed work for a given week in TFS 2008?

We're on Team Foundation Server 2008 and I'm trying to find a way to report on the change in completed work from week to week at the task level. The MDX query below works pretty well, but I'd like to get rid of need to hard code last week's date. I've tried using prevmember and parallelperiod without success, but I'm no MDX expert. WIT...

What is the most efficient way to calculate running totals for consumption in Sql Server Analysis Services?

I have a cube in SSAS 2005 that calculates running sums over millions of records. The current implementation is using a PeriodsToDate sum function in MDX and it is horribly slow, taking 15 minutes or more to return results in some cases. There are a huge number of solutions to this problem so I am hoping someone else has already tested...

Getting Metadata / Schema information from Microsoft Analysis Services

I know you can get metadata and schema info from relational stores in Microsoft SQL Server 2005+ via: INFORMATION_SCHEMA Object Catalog Views What is the equivalent of this in Microsoft Analysis Services? ...

SSAS cube design, semi-additive measures, and running totals.

I have what is to me a bit of a tricky design issue in my SSAS cube. The question is related to general accounting practices, I have a fact table containing financial transactions (i.e. a ledger) and each of those transactions is tagged with a transaction date and a period. The period does NOT related directly to a day, or a series of ...

How to use a slowly changing dimension in Sql Server Analysis Services

In a data warehouse, I want to have a fact table which tracks certain metrics of a university application (average score on a standardized test, for example) and also the status of applications during different times of the year. For simpliciy, let's say a given application progresses through 3 states: New Being Assesssed Assessed an...

How to use server aliases in SSAS 2008?

I'm having trouble getting Microsoft Analysis Services 2008 to recognize a host alias that I created (even though the database engine recognizes it). Here's the deal: we have some code that looks for an analysis services server called PRODUCTION. For testing purposes we would like to have PRODUCTION point to localhost. Under SQL Serve...

Connecting to SSAS 2005 from Excel 2007 over http: how to make it ask for username and password?

I'm trying to let my users connect to OLAP cube in SSAS 2005 using Excel 2007 over the Internet. I've set up dynamic security using fact table in cube. It uses UserName function, so users should authenticate to SSAS using windows accounts. I've set up msmdpump.dll component on IIS, allowing windows authentication, but not anonimous one....

Use Results of an XMLA query in Integration Services

I have an XMLA query which returns the State and Last Processed date of an Analysis Services cube as XML, like so: Query: <Discover xmlns="urn:schemas-microsoft-com:xml-analysis"> <RequestType>DISCOVER_XML_METADATA</RequestType> <Restrictions > <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis"> <DatabaseID>S...

SSRS 2008 and SSAS 2008 transport error

I am testing an upgrade to SSAS 2008 and verifying existing reports working properly. I am able to get some SSRS reports that are using SSAS as a datasource to run without any issues. They are simple and only have a single dataset. The reports that I am unable to get to work correctly against SSAS 2008 have multiple datasets and h...

SSAS 2008 Account provision

During SSAS 2008 installation the Account provisioning tool comes up and you can select the current Windows user (or other windows user). However that account was deleted prior to adding another user as the administrator. In 2005 the tool existed on the server and could be run manually, however I’m not seeing it in 2008. I assume I ca...

How do I make this calculated measure roll up properly in SSAS?

I have a calculated measure in Analysis Services that returns the average daily revenue for a location based on their last three periods. Periods are location defined lengths of time, so one location may close their periods monthly, and another may close their periods quarterly. To return the average daily revenue I have a factPeriods ...

UNION a final ROW in MDX

Hi, I'm quite new to MDX and im having some trouble getting the following t-sql query to MDX. select distinct System from Systen where System <> 'MIS' UNION SELECT 'ALL' So far i got something like this. But i have no idea how to add that final row 'ALL'. SELECT {} ON COLUMNS, {[Concesionario].[Sistema].[Sistema].ALLMEMBE...

Dimension Security in SSAS & SSRS

I am stuck with a problem of implementing security at dimension level in SSAS. Here is what I did - 1. Defined a role in SSAS and applied security at dimension level (Unchecking cube dimensions that I don't want this role to access and setting Allowed & denied Sets). 2. Tested using Cube Browser, it worked fine. 3. Tested using SSRS, no...

SSAS MOLAP Aggregations not working

When we run the aggregation wizard from Visual Studio for a MOLAP cube, no aggregations are created. The graph stays flat. We tried every different scenario and setting that we could think of with no luck. Any help would be appreciated. ...

SUM up 4 measures in SSAS

Can anyone help me with this ,Urgently. i want to sum up 4 measures in SSAS and devide the total by 1 measure my code is as follows. =Sum((Fields!Unique_Visits.Value)+Sum(Fields!Ask_the_Expert.Value) +Sum(Fields!SSMembers.Value)+Sum(Fields!Poll_Members.Value))/ Sum(Fields!EnrolledMembers.Value) thanks in advance regards Andile ...