SQL Server Analysis Services Forum

Filter cube data with SessionID (Get SessionId with MDX?)

Wed, 25 Nov 2009 10:05:59 Z

Hi,

I have a cube which is queried by multiple users at the same time.
The goal is to filter a given dimension so that each user can see, via the frontend client, what they asked for. Note that a user can ask for more than one report which means that the username alone will not be sufficient to filter the reports.

So the idea is to filter the dimension using the sessionID. How can I get the sessionID of the current connection at the SSAS server level? Is there an MDX function that can do this? Apart from the sessionid, is there another solution to acheive my goal?

Regards,
Didi..er

Using dimension key comparison , how to use <=

Fri, 27 Nov 2009 22:14:59 Z

I'm doing a comparison between two role playing date dimensions.

When I select the member the 1 shows up incidentally, but not with its intention. When booking>snapshot, sometimes I also notice a 1 which shouldn't be the case.

The only time it works appriopriate, is when i use the = sign. But that's not the reason I want to use it.

What is the alternative construction to <= to gain correct results?

WITH MEMBER [measures].[compare]

CASE WHEN
[Snapshot ].[Year - Period -].CurrentMember.Properties('Key')
<=
[Booking].[Year - Period].currentmember.Properties('Key')
THEN 1
ELSE 0
END

Business Intelligence professional

Sorting Attribute - using Secondary Attribute - Bids Helper showing as Problem.

Thu, 26 Nov 2009 20:34:54 Z

Hi,

I have a dimension, creating [Hour Interval 4] text and sorting based on [Time Hour] column. Same way I did for [Hour Interval 8].

Data (all columns in the same table - I cannot change the design):

[Id]: 0, 1, 2, ... 46000+ (symbolizing the minutes in a month: Count: 46000+ rows)

[Time Hour]: Null, 0, 1, ... 700+ (Count: 700+ rows)

[Hour Interval 4]: "Range: Null", "Range: >= 0 and <= 4", "Range: >= 5 and <= 8", "Range: >= 9 and <=12", ... "Range: >24" (Count: < 10)

[Hour Interval 8]: "Range: Null", "Range: >= 0 and <= 8", "Range: >8" (Count: Only 3 rows)

I followed the article "Defining Attribute Relationships and Sort Order in the Customer Dimension" in the url:http://msdn.microsoft.com/en-us/library/ms166763(SQL.90).aspx

Sorting data is working fine for both [Hour Interval 2] and [Hour Interval 8] with Attribute key as [Time Hour]. What I need, I am getting it - no worries on it.

Problem showing in Bids Helper: I launched the "Dimension Health check", it is giving the relationship as Problem.

Attribute relationship [Hour Interval 4] -> [Time Hour] is not valid because it results in a many-to-many relationship. Show/hide problem rows

Attribute relationship [Hour Interval 8] -> [Time Hour] is not valid because it results in a many-to-many relationship. Show/hide problem rows

Am I doing wrong? or Ignore the error shown in the Bids helper?

Thanks,

-----------------------

Same thing I posted in Bids helper: http://bidshelper.codeplex.com/Thread/View.aspx?ThreadId=76353

Using MAX Function to retain a constant across all members

Fri, 27 Nov 2009 16:10:36 Z

Happy Black Friday! :-)

I have a regular measure which pulls from a fact table having a field which is a constant cummulative sum of all associated records and so the value of the field never changes until the next table update.

Because our client wants to see this value in the cube as a non-changing value across all corresponding attributes as well, I have set the measures Aggregate function to MAX instead of sum which gives them the results they expect.

Now they'd like to have the regular measure changed to a calculated measure but retaining the same affect.

I have attempted this my using the MAX function within the calculated measure but when the associated attributes are dragged onto the browser, the measure is broken and distributed across all the attributes. (As I'd expected)

Is there an equivalent way using a calculated measure to acquire the same results returned by the regular measure when using the MAX function against the table or should I push back on the request?

Thanks

Cube/DWH Design Problem

Thu, 26 Nov 2009 10:52:41 Z

Hi,

I have fact table with a contract value. Also I have a dimension with a contract states

- Actual
- Replace
- Canceled

Now I have to display a ratio with following calculation

ratio = Actual - Replaced - Canceled

I have a cube which can be used to see the contract values by time, state, region etc.

Is there a way in Analysis Services to make such a calculation in a cube or do I have to change my fact table or create a new one.

Thanks for every hint

PowerPivot and SQL Server 2008 R2

Thu, 26 Nov 2009 16:51:47 Z

Hi guys,

I'm testing PowerPivot on Microsoft Excel 2010 and sad in many places people comments that PowerPivot needs of SQL Server 2008 R2, but in my tests i used the powerpivot without SQL Server R2 and no have any problems, I installed the SQL Server 2008 R2 and no realized any diferent behavior in my tests about PowerPivot.

Someone, knows if is necessary the use of SQL Server 2008 R2 to PowerPivot. If yes, what's change?

Regards,
gcardoso

Distinct Count of Active Customers

Fri, 27 Nov 2009 12:35:05 Z

I have distinct count measure

aggregate(null:[Date].[Calendar].CurrentMember,[Measures].[Count of Custs])

*[Count of Custs] is Distinct Count measure

how can i find only active of them?

like [measures].[Count of Contracts]>0

any ideas, please

Vitam impendante vero!

Income Statement Budget

Fri, 27 Nov 2009 18:10:57 Z

I am working on an Income Statement Cube that supports Budget versus Actuals analytics. My question is should I have a seperate fact table for budget amounts or just a dimension to distinguish between Actual amounts and Budgeted amounts... Also, I am attempting to provide transaction level drill-down on this same cube, but I am forced to use Excel '03 as a frontend and the only way I've come up with is to expand my Chart of Accounts Parent-child dimision table to the transaction level of detail (very challenging)... Is there a better way?

Thanks for advice or thoughts...

David

Analysis Services Security

Fri, 27 Nov 2009 21:29:45 Z

I need to know if I can put security on an analysis services 2008 cube so that for a specific dimension attribute if there is a specific value only a specfic NT group can see any of the measures and other related attributes (ie. for State = 'Hawaii' only the specified group can see the measures). Also, can we have a calculation that will give the totals including Hawaii and display this even if the end-user isn't in the group. I am using Excel 2007 to access the cube.

Pivot Table Services

Tue, 24 Nov 2009 18:15:12 Z

Is Pivot Table Services a downloadable component from Microsoft? I am trying to get my ProClarity Desktop Pro 6.3 to install on my machine.

Error when refreshing DSV in BIDS for SSAS 2008 SP1

Fri, 27 Nov 2009 21:27:35 Z

Hi,

I get this error when I try to refresh my DSV in BIDS for SSAS 2008 SP1:

System.Data - "A child row has multiple parents"

Any idea what could be causing this? I checked to see if this error meant some of my PKs in the datamart dim tables had somehow gotten duplicated - but that did not seem to be the case.

All the DSV entities are pointing at Oracle 10R2 views via the System.Data.OracleClient MS provider (I know its deprecated but worked fine so far).

I can't refresh my DSVs anymore so any help is appreciated. Thanks!

JGP

Decimal precision in cube measures

Tue, 17 Jun 2008 19:04:27 Z

I have a cube with two fact tables. One based on a table and one based on a named query in the DSV. In both cases, my numeric measures are stored as NUMERIC(19, 6). When the measures are brought forth in the cube they are shown with only four decimal places. If I change the format string to #,#.000000, SSAS is still rounding the values to four digts. For example, the value .015255 in the fact table is displaying as .015300 in the cube browser. This is causing me a problem with some calculations.

Originally, the values in my fact tables were CURRENCY but I changed that to increase the precision and reloaded the fact table. When I refreshed the DSV, it did not detect the changes so I actually deleted the fact tables from the DSV and re-added them. I have also tried changing the data types on the measures in the cube to both float and currency to no avail.

Is there a way to force SSAS to increase the number of decimal places it retains for measures?

Recursive Calculations

Fri, 27 Nov 2009 13:35:30 Z

Is it possible to deal with recursion using SSAS Stored procedures.

Proactive caching and rolap

Fri, 27 Nov 2009 13:32:43 Z

Hi, I'm try to implement the proactive caching in my cube but I have some problems:
1) I have set the storage mode for my time dimension and for my measure group as ROLAP and all works perfectly: when I delete all data for a particular day and I delete this particular day from my time dimension the excel pivottable reports exactly the change (data and day). If I re-add the same data, after a seconds all came back as it was at the beginning.
2) I have set the storage mode for my time dimension and for my measure group as MOLAP enabling proactive caching, the toy works no more....Happens that when I delete data (all data of a day and the same day from time dimension) excel pivottable reports exactly the change, but if I re-add the same data, after 10 seconds the day reappears in the time dimension in my excel pivottable but data doesn't reappear, the measure group is not automatically updated.
Maybe I'm setting up in a wrong manner my proactive caching but I dont't understand where I'm wrong. One of you can help me?
My proactive settngs are:
1) Cache settings: update cache when data changes.
Silence intervals 10 seconds
Silence override interval 2 minutes
2) Notifications: SQL server (checked the specify tracking table )

Thanks, Francesco

Launching a cube processing with a job in SQL Server 2005 Standard Edition

Fri, 27 Nov 2009 11:54:47 Z

I cannot using a dtsx to process a cube or a dimension in a SQL Server Agent job.
Can I execute a cube/dimension processing as a SQL Server Agent job?
Many thanks

Custom semiadditive behaviour

Wed, 25 Nov 2009 10:26:42 Z

Hello, I have an inventory problem and I wish to add custom semi-additive behaviour to an aggregate. I would like to now the max value over time (week, month, year, ...) of a quantity (a daily snapshot). This means that the aggregate should be summed across all dimensions, except the time dimension (where I want max to be applied). Default semiadditive aggregate functions do not support this (the max function maximizes over all dimensions). So how can I solve this? Do I have to define a calculated measure? Jochen

Date Dimension and Dates like 0001-01-01 / 9999-12-31

Fri, 27 Nov 2009 13:47:43 Z

Hi,

I have a fact table / cube with a date column with some entries like 0001-01-01 / 9999-12-31. Now I want to add a date dimension.
When I add the entries 0001-01-01 / 9999-12-31 to my date dimension SSAS is not able to process the dimension. Is there a way to do that?

Thanks

OLAP Server Formatting modified after upload ODC to sharepoint

Fri, 27 Nov 2009 12:13:23 Z

Hi,

I don't know if this post should be here, but I found similar posts while searching for the solution...

What happens is the following:

If I create a new ODC (for an analysis services connection), Excel 2007 shows the measures as formatted in the cube properties. The ODC "connection properties" dialog shows, under the Usage tab, all the "OLAP Server Formatting" ticks CHECKED. OK so far.

Then I upload the ODC to a sharepoint data connection library.

The problem is, when I open the ODC from sharepoint to an Excel worksheet, measures are shown without format. If, again, I open the ODC's "connection properties" dialog, all the "OLAP Server Formatting" ticks are UNCHECKED. If I recheck the "Number Format", everything works fine again...

Do somebody know why are those checks changed?? Is it a property of the ODC, or of the Excel file??

Thanks!

cannot operate on a set with more than 4,294,967,296 tuples

Fri, 27 Nov 2009 11:26:50 Z

Hi All,

Have been facing an issue from couple of days, was trying to form a query both in the reporting services and proclarity and I am getting this error.

Parsing the query ...

The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.

Parsing complete

Have been trying to dig through the issue but in vain, I have tried to create sets and filter the data to only required, but still getting this error.

What affects this,

Is there a chance of getting this error when we use more number of Measure Groups/ Fact Tables.

Can any body throw some light on this, Thanks.

Srira

MDX - have a measure use one hierarchy or another depending on which is present in the query

Thu, 26 Nov 2009 15:27:05 Z

Hi all,
I have a time dimention with 2 hierarquies:

[Year - Short Month] ex:
2008
   Jan
   Feb
   ...
   Dec
2009
   Jan
   Feb
   ...
   Dec

and [Year - Day]

2008
   Semester 1 of 2008
      Trimester 1 of 2008
   Jan
            1
            2
            ...
Semester 2 of 2008
      Trimester 4 of 2008
        ....
       Dec
            1
            2
            ...

And a calculated member as:

Sum(PeriodsToDate([Time].[Year - Short Month].[Year],[Time].[Year - Short Month].CurrentMember), [Measures].[Production])

As it's obvious it only works with the [Year - Short Month]...can't i rewrite is so it uses one or the other depending on which is present in the query (so the user can use it independently)?

Thanks!!

SSAS 2008 - Level All not visible in a Dimension

Wed, 25 Nov 2009 16:43:27 Z

Hi,

I am working on dimension which contains several hierarchy, and I want to display for each hierarchy the level All but it doesn't work.
I think that I did everythings properly but without succes.
For exemple : Dim Time : Year - Month - day
I specified for each attribut the true value in the IsAggretable case but the level All doesn't appear. The result is the first level on my dimension (Year)
Do I miss something important.
I need to display the All level which aggregate all of my descendant level.

thanks.

regards

Abdeslam

Help needed in Weekly reportings from AS

Wed, 25 Nov 2009 11:41:46 Z

Hi All,

I have a time dimension which is generated from AS 2005. By default in some cases the last week will fall in between two months. Now i need to create a report in which my WEEK is fixed. i.e from

Week1:1^st to 7^th
Week2:8^th to 15^th
Week3:16^th to 22^nd
Week4:23^rd to end.

Please let me know how will i achieve this kind of fixd week into my AS so that i can report from this.

Regards,
Ram

Add additional entries to generated Time Dimension

Fri, 27 Nov 2009 09:18:06 Z

Hi,

I have a generated Tme Dimension (with the dimension wizard) which was also generated as table in my DB. I generated the dimension from 1990 - 2020. Now I have the problem that I have also entries in my fact table lower than 1990. Now I want to add more entries to that dimension table. Is there a way to do that?

Replace NonEmptyCrossJoin

Thu, 26 Nov 2009 08:44:41 Z

Hi all,

I have a MDX query which performs very badly. I was able to rewrite the query using a NonEmptyCrossJoin and now it performs much better. However, I want to replace the deprecated function with the correct SSAS2005 alternatives.

Original query:

SELECT 
            (     
                  DESCENDANTS([LimitType].[LimitType],,SELF_AND_AFTER),
                  {     
                        [Measure].[Measure].[Limit],
                        [Measure].[Measure].[Position],
                        [Measure].[Measure].[Excess]
                  } 
            )
            PROPERTIES  [LimitType].[LimitType].[Alias],
                              [Measure].[Measure].[Alias]
            ON COLUMNS,
            NON EMPTY
            (
                  DESCENDANTS([Division].[Division].[Client Structured Products],,AFTER),
                  DESCENDANTS([Branch].[Branch].[(ALL)],,AFTER),
                  DESCENDANTS([BookCurrency].[BookCurrency].[(ALL)],,AFTER) 
            )
            PROPERTIES  [BookCurrency].[BookCurrency].[Alias],
                              [Division].[Division].[Alias],
                              [Branch].[Branch].[Alias]
            ON ROWS
FROM  [CRIS_LCS]
WHERE (
            [Measures].[Amount],
            [PositionDate].[PositionDate].[20090904]
            )

Rewritten query:

SELECT 
            (     
                  DESCENDANTS([LimitType].[LimitType],,SELF_AND_AFTER),
                  {     
                        [Measure].[Measure].[Limit],
                        [Measure].[Measure].[Position],
                        [Measure].[Measure].[Excess]
                  } 
            )
            PROPERTIES  [LimitType].[LimitType].[Alias],
                              [Measure].[Measure].[Alias]
            ON COLUMNS,
				NonEmptyCrossJoin(
					DESCENDANTS([Division].[Division].[Client Structured Products],,AFTER),
					DESCENDANTS([Branch].[Branch].[(ALL)],,AFTER),
					DESCENDANTS([BookCurrency].[BookCurrency].[(ALL)],,AFTER))
	
            DIMENSION PROPERTIES  [BookCurrency].[BookCurrency].[Alias],
                              [Division].[Division].[Alias],
                              [Branch].[Branch].[Alias]
            ON ROWS
FROM  [CRIS_LCS]
WHERE (
            [Measures].[Amount],
            [PositionDate].[PositionDate].[20090904]
            )

I just cannot figure out how to replace the NECJ without loosing performance. Can anyone give me a push in the correct direction?

SSAS writeback on a partition that contains aggregate functions other than SUM

Thu, 26 Nov 2009 22:23:34 Z

Dear all,

I have a cube that contains inventory levels by week. Most of the measures use the 'Last non empty' aggregate function as the total picture of what we have in stock is based on the last set of records. However we need to make adjustments to these based on stock takes and inventory counting.

What I'd hoped to do was enable writeback and use Excel 2007 pivot tables, but I see this is not possible with aggregate functions other than Sum. Can anyone suggest any alteratives to this?

I need to allow certain end users to make these changes, so it has to be reasonably simple, auditable and quick.

We are using SQL 2008 - Enterprise Edition.

Many thanks

Matt

Calculated Member in AdomdCommand

Wed, 25 Nov 2009 15:50:17 Z

Hi,

I am trying to execute a MDX query with calculated members using adomdCommand, it throws an exception below are the details.

MDX query :

WITH
SET orderedemployees AS
Order
(
[Employee].[Employee].[Employee].MEMBERS,[Measures].[Reseller Sales Amount]
,bdesc)
MEMBER Measures.[Employee Rank] AS
Rank
(
[Employee].[Employee].CurrentMember
,orderedemployees
)
SELECT
Measures.[Employee Rank] ON 0
FROM [Adventure Works]
WHERE
[Employee].[Employee].&[46]

C# Snippet:

string mdxQuery = "..."; // MDX query
AdomdCommand adomdCommand = new AdomdCommand(mdxQuery, adomdConnectionObj);
CellSet = adomdCommand.ExecuteCellSet();

Error Message :

>>>>>>

"The '[Measures].[Employee Rank]' object was not found. Parameter name: uniqueName"

>>>>>>

Can anybody please point me out how this can be resolved..??

Thanks,

Thameem

Filter function in where clasue

Wed, 25 Nov 2009 11:18:14 Z

Hi All,

How to use filter function in where clause using MDX?

in usage in mdx

Wed, 25 Nov 2009 10:59:52 Z

Hi All,

I need to create a member in mdx based on condition If <Customer Base No> InList ("0000071682" ,"0000056247" ,"0000055932" ) Then "EM" Else

"External"

How to create it?

MDX Prior Year to Date (YTD) using Shell Dimension

Thu, 26 Nov 2009 07:45:52 Z

Good morning,

Ok, so this has been quite a long journey so far to try and get the relative time calculations to work correctly. That said, the last hurdle we need to cross is to get the Prior Year to Date (YTD) calculations to work. My MDX Skill level: Complete Novice.

Let me list what I have already tried so that hopefully I don't get redirected to the same posts again:

* Marco Russo's "Date Tool Dimension" Solution (MDX currently beyond my understanding)
* A different approach to Time Calculations by David Shroyer
* Quite a few online blogs and posts

Ok, so let's get down to the nitty gritty:

I have a simple Time Dimension set up with 3 Levels: Year, Quarter, Month. The Time Dimension only goes down to a month level, and thus we do not have daily Fact data available. Each of the levels mentioned above have been set up correctly in terms of Time Properties (Type). So the Year level has been defined as "Years", the Quarter Level has been defined as "Quarters", and the month has been defined as "Months".

Below is a snippet of the data in the source table for our Time Dimension:

Month	Month_Name	Quarter	Quarter_Name	Year	Year_Name
2006-01-01	2006-01	2006-07-03	Qtr 3	2006-07-01	2006
2006-02-01	2006-02	2006-07-03	Qtr 3	2006-07-01	2006
2006-03-01	2006-03	2006-07-03	Qtr 3	2006-07-01	2006
2006-04-01	2006-04	2006-07-04	Qtr 4	2006-07-01	2006
2006-05-01	2006-05	2006-07-04	Qtr 4	2006-07-01	2006
2006-06-01	2006-06	2006-07-04	Qtr 4	2006-07-01	2006
2006-07-01	2006-07	2007-07-01	Qtr 1	2007-07-01	2007
2006-08-01	2006-08	2007-07-01	Qtr 1	2007-07-01	2007
2006-09-01	2006-09	2007-07-01	Qtr 1	2007-07-01	2007
2006-10-01	2006-10	2007-07-02	Qtr 2	2007-07-01	2007

So the year starts in July, and ends in June the following year i.e. 2010 would start in 2009-07-01 and end in 2010-06-01. NOTE: The Month, Quarter and Year columns are defined as DATETIME however I removed the time portion of the information in this post due do space limitations.

My cube currently contains 187 measures in it & counting. Due to the large amount of measures, it would be impractical to create time calculations such as YTD, Prior YTD, QTD, and Prior QTD for each measure because then we would end up with 187 * 4 calculated measures...

To get around this issue we implemented a Shell Dimension for these time calculations called "Frequency Dimension". What it table does is it contains the actual MDX code for the time calculations, and it is also a Parent Child Dimension. Let me post a snippet of the code so that perhaps it makes more sense:

I am going to post the rest of my question as a reply to this one due to character limitations per post...

DistinctCount based on results of another Measure Value

Thu, 26 Nov 2009 22:01:22 Z

I created a DistinctCount measure on column "dossiernummer". The result is not correct beause it counts also dossiers where the sum of the revenue is 0 (depending of the slice and dice you do) How can i define a DistinctCount on the "dossiernummer" column where the sum of revenue>0 ??? Can somebody help ???

I wrote an sql statement directly on my star schema to give an example of what kind of calculation i want:

select

   vfenummer,
   dossierreputatiecode,
   jaar,
   count(distinct dossiernummer) as distinctdossier
from
(
select
   t1.vfenummer,
   t1.dossiernummer,
   t2.dossierreputatiecode,
   datepart(year,t1.declaratiedatum) as jaar,
   sum(t1.bedragomzet) as bedragomzet
from
   _TBL_OMS_Feiten_Omzet t1
   LEFT JOIN _TBL_OMS_Dimensie_Dossier t2 ON (t1.dossiernummer=t2.dossiernummer)
group by
   t1.vfenummer,
   t1.dossiernummer,
   t2.dossierreputatiecode,
   datepart(year,t1.declaratiedatum)
)s1
where
   not bedragomzet=0 and vfenummer='102' and jaar between 2006 and 2009
group by
   vfenummer,
   dossierreputatiecode,
   jaar
order by
   vfenummer,
   dossierreputatiecode,
   jaar

To get the Count as a total in a report

Mon, 23 Nov 2009 09:41:55 Z

hi all,

I have a scenario as follows. I am calculatind the absolute deifference of a measure as follows
if abs(M1-M2) is positive then 1else if Abss(M1-M2) is negative then 2 else 0 . so in my report if i drag my time and geography dimenion i get the following

                    Day1 Day 2     Day3 ................................................................ Day 30           Total Count
East              0         0          1                                                                              1                         2
West             0          1          2                                                                             2                          2
North             0           0         0                                                                             0                          0
South             0         1            0                                                                            0                          1

I need to get the counts of 1 and 2 for the region across al the days for the month selected and need to display in the report.
Please let me know how can i achieve it.

regards
ram

Caching SSAS stored procedure output

Thu, 12 Nov 2009 09:00:24 Z

Hi, I am using a custom DLL function (SSAS stored procedure) for the Cell Security in SSAS role. This function returns a MDX Expression like below for each logged in user ( ( ( ( Ancestor([Geography].[Geography].CurrentMember, [Geography].[Geography].[Country]) IS [Geography].[Geography].[Country].&[Australia] OR Ancestor([Geography].[Geography].CurrentMember, [Geography].[Geography].[Country]) IS [Geography].[Geography].[Country].&[Canada] ) ) AND ( ( [Promotion].[Promotion Category].currentmember IS [Promotion].[Promotion Category].&[No Discount] ) ) AND ( ( [Measures].CurrentMember IS [Measures].[Internet Gross Profit] ) ) ) ) When I am using Excel 2007 to browse the cube I am getting performance hit on each click (it takes 20 to 60 seconds to return results). The cube and excel are on different servers. However if I put the hardcoded MDX expression (like above ) in Cell Security then I get results within 5 - 10 sencods. My question is - Is there any way that I can cache this function output for a user? Any help would be appreciated.

Filter condition in Where clause using MDX

Wed, 25 Nov 2009 10:50:53 Z

Hi All,

Am trying to use FILTER condition in where clause in MDX.

Ex: FILTER([Operating Client].[By Marketing Client].[Operating Client].MEMBERS
//        ,LEFT
//                (
//                 --[Operating Client].[KeyMarketingClient].CurrentMember.Member_key
//                    [Operating Client].[By Marketing Client].CurrentMember.Properties( "Marketing Client" )
//                 , 5
//                ) = "UNDEF")
//
Please guide me how can i use this in MDX.

This above query i want to place in Where clause.

Summing two tuples

Mon, 23 Nov 2009 10:32:17 Z

Hello all,

I'm afraid I'm stuck in newbie-land with this SUM thing. I need to SUM two tuples, should be simple but I can't for the life of me get it to work. I tried a variety of things, but end up with "two sets cannot have different dimensionality" error.

I have this query:

SELECT
{ AnnualTotalCount, crossjoin({ [Date].[Calendar Year].[Calendar Year].[CY 2001]}, {[Geography].[City].[City].[Arlington], [Geography].[City].[City].[Atlanta] }) } ON COLUMNS,
NON EMPTY {[Measures].[Reseller Sales Amount]} ON ROWS
FROM [Adventure Works]
WHERE
(
{[Geography].[Country].[Country].[United States]}
);

... which gives me this table:

	CY 2001	CY 2001
	Arlington	Atlanta
Reseller Sales Amount	$37,781.64	$603.40

What I'm after is this:

	CY 2001	CY 2001 CY2001
	Arlington	Atlanta Arlington+Atlanta sum
Reseller Sales Amount	$37,781.64	$603.40 $38385,04

How on earth...?

Many, many thanks in advance,

Morten

Problem with Measure visualization in Analysis Services

Wed, 25 Nov 2009 06:40:53 Z

Hi Everyone, Im facing a weird problem and I hope you can help me figure it out.

I have a named query as a table in Analysis Services, I have a measure that is a double... from the sql table comes like 75.00000 or 0.0000000
but when I see it in SASS browser or Proclarity it shows like 76 or 1 ...

Does anyone know what is causing this.

Thank you very much

Adriana

Implimenting security for non-domain users

Tue, 24 Nov 2009 03:53:44 Z

Hi Team,

We have a requirement for providing access of dashboard to non-active directory users. The users will be accessing the dashboard througha web portal, there they enter the username and password. Please provide some information on this.

We have table which is storing the users information, is there any possibilities of providing securities at the report level. If anybody has any information please do share.

Thanks in advance. Have a nice day :)

Regards,
Ram

Cumulated Sum BDESC ordered Set

Mon, 23 Nov 2009 16:31:25 Z

Hi,

I need to calculate a cumulated sum over customers sorted by their overall revenue in a given time period. So using [customer].currentmember + [customer].Prevmember wont work since it is not adding the sorted customers but the hirarchy members.
What it should be is something like this:

Cust#	Revenue	Cumulated Sum

453	100	100
277	80	180
234	5	182

My starting point was using the order by function but as stated before the current - Prev addition doesn't work of course. All examples i found are based on the time hirarchy and not on an ordering by something different like revenue except this one http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/thread/46608bf0-a3ba-49bf-882c-382ac7ebb0d6/.

I tried to adapt it, but the calculated member produces an error.

I need to have the cumulated sum to build a chart in SSRS and the x-axis or grouping won't allow me to use the runningvalue function so i need to provide this value from the mdx query. The whole thing is an ABC Analysis for the costomers for different time periods.

Any help or different solution is apreciated.

Thanks in advance

Andre

This is the query:

WITH 
SET [KundenSortiert] 
AS(ORDER( [Kunden].[Nummer].Siblings,[Measures].[Eigener Umsatz Gesamt] , BDESC))

Member [Measures].[UmsatzKumuliert] 
AS Sum( Head([KundenSortiert] ) , (Rank([Kunden].[Nummer].CurrentMember, [KundenSortiert]),[Measures].[Eigener Umsatz Gesamt]))

SELECT 

NON EMPTY {
[Measures].[Eigener Umsatz Gesamt],
[Measures].[UmsatzKumuliert]
} ON 0,

NON EMPTY {
ORDER({[Kunden].[Nummer].[Nummer].AllMembers},[Measures].[Eigener Umsatz Gesamt] , BDESC)
* [Kunden].[Kunden Name].[Kunden Name] 
* [Kunden].[Ort].[Ort]
} ON 1

FROM ( SELECT ( { [Vermietungen].[Vertragsart].&[M] } ) ON COLUMNS 
FROM ( SELECT ( 
StrToMember("[Kalender].[Jahr-Monat-Tag].[Tag].&[" + Format(vba!dateadd( "yyyy", -2, vba![Date]()),"yyyy") + "-01-01T00:00:00]" )
: 
StrToMember("[Kalender].[Jahr-Monat-Tag].[Tag].&[" + Format(vba!dateadd("d", -1, vba![Date]()),"yyyy-MM-dd") + "T00:00:00]" ) )ON COLUMNS 
FROM [Vermietung]))

Partition processing in SSAS - increasing processing performance

Fri, 27 Nov 2009 04:53:36 Z

Hi,

I am intrested in knowing about the improvement we will be getting with creating partitions in cube.

I assume once we can create the partitions,

1. We can process them parallelly to load at the same time. As all the partitions queries will be running on the top of DB parallelly and fetch and load the data faster,

2. also once the data is fetched the index and aggregation creation is also fast(in parallel). I am not sure about this, can anybody confirm the same. Along with this i want to know any other advantages of partitions.

3. The cube will be online while the partitions are getting processed without taking it offline.

Please clarify the same. It will helpfull.

Hierarchy data visible in dimension browser but not in cube browser.

Wed, 25 Nov 2009 12:03:30 Z

I have created a user hierarchy using some attributes in a dimension. I processed the dimesion and then the cube successfully. When viewed in the dimesion browser, the hierechy levels pan out well. But whe nI drag the hierarchy in the cube browser, I am unable to see any data. In the drop-down of that attribute (in the hierarchy), I am able to see the various attribute members. But they are not visible in the browser. Same is the case when I try dragging a single attribute in to the cube browser. Even though there is data for that attribute in the DB, I cannot see it in the browser.

Need to restrict Slicing and dicing in cube

Thu, 26 Nov 2009 09:41:23 Z

Hi All,

My requirement is as follows. I have a month attribute and a week attribute. There is a measre M1 which needs to show month value only even when the week parameter is selected in the filters. How can i achieve this. Please let me know if there are any solutions.

the measure M1 is calculated as

(

Sum (ClosingPeriod ([Time].[Month Hierarchy].[Month],[Time].[Month Hierarchy].currentmember).Lag (03)

ClosingPeriod ([Time].[Month Hierarchy].[Month],[Time].[Month Hierarchy].currentmember).Lag (01),[Measures].[M1]))

Thanks and regards,
Ram