Analysis :: How To Show All Level Of SSAS Dimensions In Excel

Oct 28, 2010

The all-level of dimensions doesn't show up in the PivotTable Field List? I have reports where I want to show one member of a dimensions compared to the total of the dimension (and not the total of the members shown). But I can't select the ALL-level. Is there any way to do this?

View 5 Replies


ADVERTISEMENT

Analysis :: Show Dimensions Like Columns In MDX

Jun 25, 2015

I need to show the dimensions of my model like columns in the result. I have this query

with 
member [Measures].[Customer] as [Customers].[Customer].CURRENTMEMBER.Name
member [Measures].[UCs] as [UCs].[UC].CURRENTMEMBER.Name
member [Measures].[Order Type] as [Order Types].[Order Type].CURRENTMEMBER.Name
member [Measures].[UC Dates] as [UC Dates].[UC Date].CURRENTMEMBER.Name

[Code] .....

View 14 Replies View Related

Analysis :: How To Show Measures Dynamically For Different Dimensions

May 4, 2015

Actually I want to do distinct sum on a measure group, please find the below table as sample

XL Measure group
LK     OK      Amount
1        10         100
1         11        100
3          30       250
3          31       250
3          32       250

For the above measure group two dimensions have relationships, One is L dimension which is having relationship with XL on LK and One is O dimension which is having relationship with XL on OK. If I drag L dimension attributes  it should show results as below

LK LName  Amount
1     A         100
3     C         250

But above results are coming as below

LK LName  Amount
1     A         200
3     C         750

If I drag O dimension attributes along with L dimension, it should show results as below.

LK  LName   OK     OKName   Amount
1        A         10      XYZ         100
1        A         11      UVW        100
3        C         30      PQR         250
3        C          31     KLM         250
3        C          32     TUV         250

I used formula Measures.Amount/Measures.Count, this formula is not showing correct results when I don't drag any dimensions, it is showing results for All member  as 425, but it should show as 350.

So I made a same change ([L].[LK].Currentmember, Measures.Amount)/([L].[LK].Currentmember,Measures.Count), this worked fine but performance is very low and so stopped working on this.

Atlast I did the measure group like this

LK     OK      LAmount   OAmount
1        10         100        100
1         11        0            100
3          30        300        300
3          31        0            300

I want to show Measures.LAmount when only L dimension is querying and want to show OAmount when both L dimension and O dimension are querying. Is this possible ?

View 3 Replies View Related

Analysis :: Show Grand Total When Using SSAS Tabular Role Security Permission

Oct 30, 2015

If user want to see the grand total for a measure with include all members, even though the user has limited access for that member, so how we can do using DAX?For example, let’s say the total revenue for all the divisions in a cube is $15,000. You create a role called “Division A”, and set it up so members of that role can only see the revenue for Division A, which totals $3,000. If you use a front-end tool like Excel to access the cube and use the division hierarchy to see the total revenue, you will see the revenue of $3000 for Division A, but also want to see the Grand Total for the revenue as $15,000How we can achieve above scenerio in tabular model (DAX).

View 3 Replies View Related

Analysis :: Excel Not Connecting To SSAS

Sep 21, 2015

I have created SSAS package successfully.

When I try to connect from excel , to SSAS Getting error message like

A Connection attempt failed because the connected party did not properly respond after a period of time or established connection failed because connected host has failed to respond.

If I connect to SSIS , I'm able to connect correctly.

Why I'm getting this error and how to overcome this?

View 4 Replies View Related

Analysis :: SSAS Tabular Stand Alone Table In Excel

Jul 16, 2015

I am working with SSAS Tabular. I have a stand alone table with 60 columns and contains 120K records. Table size is 250MB. And trying to build a tabular report out of it and it is taking longer and throwing exception, screenshot attached.

It might be cross-join issues, as workaround created a dummy measure and using in report. But it working for 10-20 k records and beyond throwing same exception. I have 8 GB RAM and 100 GB free disk space.

Customer wanted it in SSAS Tabular...

View 3 Replies View Related

Analysis :: Cannot Import Excel Work Book To SSAS

Jul 24, 2015

Im trying to pull a workbook which has a power pivot into SSAS using a remote tabular instance through visual studio 2013. However Im getting an error saying, " We cannot import the workbook XXXXX.xlsx. Try placing the work book on a server that the service account of DBNAME/Tabular has permission to read and that can be reached with a UNC path (//<server>/<shared>/<file>)". Here DBNAME is the server name and Tabular is the instance name.

I tried changing the logon name in SQL server analysis services(Tabular) in Services.msc Microsoft console.Also tried changing the logon to local system. Still the error persists.

how to overcome this error?

View 5 Replies View Related

Analysis :: SSAS Tabular - Grouping In Excel Grayed Out?

Nov 10, 2015

I have a Tabular data model and I'm returning a measure that counts employees (each row is an employee) and then a calculated column in the model that gets SeniorityInMonths.

So if an employee was hired exactly 1 year ago, they would have 12 in this column.

I want to group these into bins, but the Group option is grayed out.

View 6 Replies View Related

Analysis :: Creating Cubes In SSAS Using Various Tables In Excel

Jun 29, 2015

I'm exploring creating local cube files, .cub from an excel sheet with tables.Would SSAS be able to create one cube from taking data from a 1 way table (A, B, C), a 2 way table (AxB, BxC, AxC) and a 3 way table (AxBxC)?

View 4 Replies View Related

Analysis :: How To Display 1/1/1753 Value In Excel While Browsing SSAS Cube

Jul 8, 2015

I have connected to  SQL SERVICES ANALYSIS SERVICE database through excel and when I observed that value of the date attribute is displayed as ######## in the excel for 1/1/1753.

I am able to see the value 1/1/1753 in the Cube browser but not able to see the vale in the excel.

how to replace this value with blank in the  excel.

View 3 Replies View Related

Analysis :: Tracking Information About No Of Connections Made To SSAS Cubes Through Excel Files

Sep 29, 2015

Is there any way to track the information about the  connections to SSAS cubes through local  Excel files (BI usage).

OPreviously, we are tracing the information about the BI usage through the BI SharePoint site. Now we want track the users who are using through local excel files .

View 9 Replies View Related

Analysis :: SSAS Tabular Small Database Fails In Excel Pivot Table Because Of Memory Shortage?

May 7, 2015

I have a very small SSAS database with around 35 Mb. I opened it on Excel 32 bits and started dragging fields to a pivot table and it started failing with memory errors. The behavior on the SSAS server was that memory started growing very fast until 8 GB (vm memory total) and then the error is reported in excel.

What might be the issue in such a small database? I would understand in a big database, but not on this one.

View 13 Replies View Related

Analysis :: Hidden Cube Measure Groups Are Shown In Excel Pivot (Show Fields Related To) Drop Down

Jun 18, 2015

We have hidden few measure groups in cube for time being, where Users can browse the cube with Excel pivot. But, All these measures can be seen from Excel pivot in 'Show fields related to' drop down.

Need to remove the hidden measure groups from showing in Excel pivot and to remove 'All' option in 'Show Fields related to', So that users may not get confused by seeing all the measures. Can we achieve this.

View 3 Replies View Related

Analysis Services - Dimensions

Oct 23, 2002

In Analysis Services is there a way you can change a private dimension into a shared dimension without having to recreate it

View 1 Replies View Related

Analysis Services - Dimensions && Measures

Aug 13, 2004

I have a problem where I have 3 three measures in a virtual cube:
"Actual", "Budget" and "Full Year Budget".

The dimensions I have are:
- Account No_ / Name
- Cost Code
- Sub Cost Code
- Time/Dates
- Budget Name

Both "Actual" & "Budget" measures need to be filtered/dimensioned by:
- Account No_ / Name
- Cost Code
- Sub Cost Code
- Time/Dates (exclusive to "Actual", "Budget")

Thus have put these in one cube


AND "Full Year Budget" needs to be filtered/dimensioned by:
- Account No_ / Name
- Cost Code
- Sub Cost Code
- Budget Name (exclusive to "Full Year Budget")

THUS have put this as one cube

I then created a virtual cube, with the 2 cubes thinking that the dimensions I created in the original cubes would only filter the measures of the original cube measures in the virtual cube. ...BUT all dimension filters in the virtual cube filter all measures in the virtual cube, irrespective of which dimensions were created with the original cubes.

please help!

View 1 Replies View Related

Analysis :: Joining Multiple Dimensions In MDX

Jun 13, 2015

I'm new to MDX, and most of the time I customize existing queries rather than writing new ones. I currently have a MDX query like this

SELECT 
[Measures].[Fees Billed]
                  on 0,
except([Age].[Day Buckets].members, {[Age].[Day Buckets].[All], [Age].[Day Buckets].&[Unknown]})
  on 1
FROM 
     MyCube
WHERE ([Fiscal Period].[Fiscal Year].&[2015], [Customer].[City].&[Auckland] )

Which brings the fees billed by age buckets where the customer's city is Auckland. I also have another dimension called [Sales Agent] with a member [City] in it, and there is a member in [Customer] called [Customer].[Sales Agent]

I am trying to retrieve the same information where the customer's sales agent's city is Auckland rather than the customer's city.

If it is SQL, I will join Customer and SalesAgent on Customer.SalesAgentUno = SalesAgent.SalesAgentUno and bring in the desired data. Any way in MDX to do this?

View 4 Replies View Related

Analysis :: Filter Measure On Intersection Of Dimensions

Jul 24, 2015

SSAS 2008 R2

Is it possible to filter out a measure only at the intersection of Two dimension members? I have a date dimension,  a Hospital dimension and a wait time measure.

For Example, is it possible to filter out Wait time for Bayside Hospital for the Month of June 2015?

I want Wait time to continue to be displayed for all other months and roll up into the totals without the filtered value.

View 4 Replies View Related

Analysis :: Parallel Period Sliced By Other Dimensions

Aug 4, 2015

I have make a calculated member for previous period of an given date range.  The previous period is the same date range from the previous year, and I have managed to achieve that with the calculated member:

Create member currentcube.[Measures].[PrevPeriod] as
(ParallelPeriod( [Start Date].[Cal Hierarchy].[Year], 1, [Start Date].[CAL Hierarchy].CurrentMember), [Measures].[Count]);

This member returns the correct result as long as my query uses the time dimension, which makes sense... but I also need to show results sliced by other dimensions in bar charts that do not display the time dimension.  For example, I have a dimension with only 3 members called [Region].[Area].[AreaName].

The result set for the bar chart needs to look like this:

[AreaName] | [Count] | [PrevPeriod]
East            |    43      |       56
West           |    53      |       95

But the [PrevPeriod] only returns values if I include the time dimension.  I essentially need to sum the results of the time dimension/AreaName/[PrevPeriod] tuple down to just Areaname/[PrevPeriod] for whatever date range may be involved.

I don't know if this is significant to the issue, but the client tool that generates the bar charts builds the query with the date range as a subcube in the FROM statement.  If the [PrevPeriod] is outside of the subcube that is still OK, as long as the time dimension is included in an Axis on the final select statement, so at least I know I am not suffering from the members inside the subcube.  I've also found in SSMS that it makes no difference if I make the query a subcube, or put the date range in a where clause instead;  I still get NULL for [PrevPeriod] without the dates.

I can't imagine that this is an unusual situation, so I hope I've explained it adequately!  What is the recommended technique for summarizing a Parallelperiod by dimensions without displaying the time/dates ?

View 6 Replies View Related

Analysis :: Cube Performance Versus Many-to-Many Dimensions

Sep 22, 2015

I have a cube with 2 many-to-many dimensions where a special mdx query needs about 5 seconds. When I resolve the many to many relationships by multiplying the data in the fact table the query needs 21 seconds.

In general do many-to-many dimensions slow down query performance of a cube?

Without the many-to-many dimensions of course the fact table has much more rows. Could this be the reason for the performance loss?

how to tweak query performance of a cube in general?

View 3 Replies View Related

Analysis Services: MDX - Date Ranges With 2 Time Dimensions

Aug 31, 2004

Hi,

I have a Time Dimension which allows me to select a specific YEAR, or YEAR & QUARTER or YEAR & QUARTER & MONTH, or YEAR & QUARTER & MONTH & DAY.

Is there any way that I can have a range of dates?

Is it possible to have 2 time Dimensions for example which did the following:

a start: Year|Month for example
(>= Year|Month )
.......and......
an end: Year|Month
(<= Year|Month )

Thus I would be able to select a range of dates/months.

Do you know if this is possible to write this inot the dimension?

Thanks,

David

View 3 Replies View Related

Analysis :: Add Descriptions To Attributes In Role Playing Dimensions

Jul 28, 2015

I have a requirement to set Description values for our cube dimensions and attributes. 

I've done this for regular dimensions but I cant seem to find a way for role playing dimensions. I can set the base dimension descriptions but not the 'clones'. Is this possible? 

View 2 Replies View Related

Analysis :: Ignore Unrelated Dimensions In Tabular Model

Jul 11, 2013

how to set 'Ignore Unrelated Dimensions' property in Tabular Model.

View 3 Replies View Related

Mapping Surrogate Keys Of Level 2 Dimensions To Fact Table In SSIS Data Flow

Aug 16, 2007



Hi,
I use lookups to map surrogate of level 1 dimensions to my fact tables in SSIS.
But how to handle a level 2 dimension with a ValidFrom and a ValidUntil date field?
I do not use an IsCurrent column, because this could problem with late arriving facts.


- In dts I used an SQL statement like this:

update SA
SET SA.DimProdRef = Dim.RecordID
FROM SAWarenEingang SA, DimProd Dim
where SA.ProduktNumber = Dim.ProduktNumber
and SA.ArtikelkontoBewegungsdatum between Dim.ValidFrom and Dim.ValidUntil


Now in SSIS I want to handle the whole thing in the data flow without using a staging table:
- Using Lookups: I would have to pass the date column for each inside the fact table into the lookup. That does not work.
- Using Execute SQL in the data flow: would be very slow, because the statement will be executed for any line in the dataflow


Any ideas?


Best regards,
Stefoon

View 10 Replies View Related

Analysis :: Unable To Browse Data When Adding Multiple Dimensions?

Jul 5, 2015

I have one dimension and one measure group. I deployed and processed the cube. Now I am able to browse the data. Now I added one more dimension. I deployed and reprocessed again the Cube. Now I am not able to see any values.  I am getting like below.

View 6 Replies View Related

Analysis :: How To Give Permission To Read Dimensions Members In Hierarchy

Jun 9, 2015

I am using a "Client" dimension that includes a "Holding - Client" hierarchy. I have to make sure, that only the appropriate roles may access appropriate members from this dimension, but I only have the information which role may access which ClientID - I do not have the information which HoldingID should be accessible. Also, "Client" is used as the key column of the dimension with "ClientID" and "HoldingID" as key columns. The hierarchy is strict, no client may belong to multiple holdings.

I cannot seem to find the right MDX for the allowed member set. My MDX expression would need to look like this:

[Client].[Holding - Client].[Client].&[*]&[123]

In this example I want to give access for client &123, no matter the holding, so &1&123 and &2&123 would be allowed.

Is this doable?

View 4 Replies View Related

Analysis :: Linking Multiple Fact Table At Different Granularity To Same Dimensions

Jul 15, 2015

I am modelling two fact tables of Actuals and Budget which are at different granularity, Actuals are at day, customer and product sub category level. Budgets are at month, Region and Product category level.

Month, Region and Product Category is present in Date, Region and Product Category dimension respectively. I have only three dimensions as Customer, Product and Date. Linking those dimensions to Actual Fact table is not an issue, what is the best way and options are there to link budget fact table to those three dimensions.

View 2 Replies View Related

Analysis :: Fact Tables Appearing As Dimensions In Tabular Model

Jul 6, 2015

I built my first tabular model and see that my fact tables are also appearing as dimensions. In Multi dimensional mode i could choose which are the dimensions. How do i do that in tabular model.

View 2 Replies View Related

Reporting Services :: Show Empty Column Values In SSAS / SSRS?

May 6, 2014

In My cube I have :

A Fact table <<Company Revenue>>
2 Dimension <<Month.Dim>> , <<Company>>

I have 4 company say (A,B,C,D) but i have record for only company A & D that too only for December month .

 When I create a SSRS report out of the cube i want a matrix where all the company names to be displayed in the rows & all the months in the columns and company's revenue in data

What my report is displaying :

Company  |   December
-------------------------------------------------------
A                 |   20000
-------------------------------------------------------
D                 |   3900  
-------------------------------------------------------

what is needed :

Company  |   January  |  February | March |  ........... | December
-----------------------------------------------------------------------------------
A                 |                    |                  |              |     |  20000
----------------------------------------------------------------------------------
B                |                    |                  |              |     |  
----------------------------------------------------------------------------------
C                 |                    |                  |              |     | 
----------------------------------------------------------------------------------
D                 |                    |                  |              |          |   3900  
-----------------------------------------------------------------------------------
 
How do i achieve this either in SSAS or SSAS.

View 5 Replies View Related

Analysis :: Using Calculations In SSAS Cube?

May 21, 2015

how can use this mdx script in the calculation part of a cube, will i simply dump it in the script form by starting with the 'create member current cube.

[measures].[test]'
select 
[measures].[abc] on 0,
[xyz].[xyz].(&0):[xyz].[xyz].(&60) on 1
from
(
select
(tail([month].[month].[month].members,6))on 0
from
[cube])

View 3 Replies View Related

Analysis :: Ssas And Ssrs On Same Server?

Sep 2, 2015

I've see twice this year two different companies either already or planning on having ssas and ssrs running on the same server. 

View 7 Replies View Related

Analysis :: Threshold File For SSAS - PAL

Aug 7, 2015

I am doing workload analysis on SSAS - Tabular (2012), I have perfmon logs captured and want to run through PAL. I am looking out for threshold file for SSAS tabular 2012/2014. 

View 2 Replies View Related

Analysis :: SSAS Calculated Member - MDX

Jul 1, 2015

I have a Calculated Member in SSAS that I need to adjust based what the current member is. 

The code is below

CASE WHEN [Measures].[End LIS] = 0 AND "HELP" THEN
CASE WHEN [Measures].[Beginning LIS] = 0 OR [Measures].[Beginning LIS] + [Measures].[Beginning LIS] + [Measures].[NETACTIVATIONS] = 0 THEN NULL ELSE
 ROUND([Measures].[Disconnects]/(([Measures].[Beginning LIS] + [Measures].[Beginning LIS] + [Measures].[NETACTIVATIONS])/2) * 100 ,2) END
ELSE ROUND(([Measures].[Disconnects] / [AVERAGELIS] * 100) ,2)
END

In English - i need this to translate to - of End LIS is 0 "AND the current member is the current month and current year" THEN carry on 

Else ROUND(([Measures].[Disconnects] / [AVERAGELIS] * 100) ,2).

I came up with 

CASE WHEN [Measures].[End LIS] = 0 
AND [Dim Date].[FSCL_YM].[Month Nm].currentmember.membervalue = Format(now(), "yyyy")+"]&["+Format(now(), "M")
THEN
CASE WHEN [Measures].[Beginning LIS] = 0 OR [Measures].[Beginning LIS] + [Measures].[Beginning LIS] + [Measures].[NETACTIVATIONS] = 0 THEN NULL ELSE
 ROUND([Measures].[Disconnects]/(([Measures].[Beginning LIS] + [Measures].[Beginning LIS] + [Measures].[NETACTIVATIONS])/2) * 100 ,2) END
ELSE ROUND(([Measures].[Disconnects] / [AVERAGELIS] * 100) ,2)
END

But to no avail. 

View 4 Replies View Related

Analysis :: Any Way To See Content Of SSAS Cache?

May 7, 2015

I have cubes which are large and query response time exceeds 1 minute. 

So, I like advice of pre-warming cache. Any way to see if cache is already/still available for the cube?

non related - if there is some setting timeout for SSAS cache?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved