Analysis :: How To Use EXCEPT In A Slicer

Nov 11, 2015

I tried to use the Except function like this:

SELECT NON EMPTY ORDER({[Bi Employee].[Employee Key].CHILDREN}, [Measures].[Bi Measure Count], DESC) ON AXIS(0),
       [Measures].[Bi Measure Count] ON AXIS(1)
FROM  [mycube]
WHERE  ([Bi Rank In Trip].[Rank Key].&[1],
        [Bi Date].[Year].&[2015],
        EXCEPT({[Bi Route].[Route Key].CHILDREN}, {[Bi Route].[Route Key].&[ 1005]}))

Because I want all Route_key except ' 1005'. But based on the results (which are not good), its clear to me that this function do not act the way I think. How can I exclude Route Key ' 1005' otherwise?

View 4 Replies


ADVERTISEMENT

Analysis :: Limiting Sum Function To Only Evaluate The Data In Slicer

May 22, 2015

I am having trouble limiting a sum function to only evaluate the data in the slicer. I need a sum of data calculated per employee and when there is a slicer of one employee my measure still evaluates all employee. For example:

WITH
MEMBER [Measures].[TotalEmpHours] AS
SUM([Employee].[Employee].members, [Measures].[Actual Ovt Hours])
select
{
[Measures].[TotalEmpHours],

[Code] ....

The above reports the overtime for one employee in [Measures].[Actual Ovt Hours], I expected [Measures].

[TotalEmpHours] to be the same as there is only one employee in the slicer select. The real measure is the minimum of two values for each employee.

This is a simplified example but I think it should work.Is there another construct to iterate the employee set? I have no control over the query as it is from a pivot grid. If the filter (employee) is leftmost in the row area, then it works because the employee ends up in the row part of the query.

View 7 Replies View Related

Power Pivot :: Select Dimension By Slicer

Aug 18, 2015

I would like to provide users a way to dynamically select the dimension by which they analyse data in a pivot table. In a simple example, my fact table has 3 columns: product type, colour, volume. The pivot table consolidates the volume, and a slicer allows the user to display the sum of volume either by product type or by colour.

The solution I have come to so far involves creating a table with a single column that contains all the single values of product type and colour. There are 2 relationships between the fact table and this lookup table: one active on the product type column, one inactive on the colour column.

A 3rd disconnected table provides a way to select which of the 2 relationships is the active one for the consolidation metric. The example is available here.

View 2 Replies View Related

Power Pivot :: Insert Hierarchy Slicer In Excel 2010?

Sep 23, 2015

We want to implement cascading slicers in PowerPivot 2010. Does Excel 2010 have 'Insert Slicer Hierarchy' feature in PowerPivot?

View 5 Replies View Related

Power Pivot :: Can Measure Be Effected By Cross-filtered But Unselected Slicer Item?

Aug 17, 2015

My actual data is a bit more complicated, and uses fact and dimension tables, but I simplified it here.

I'm trying to build a chart that will compare sales of a single store to regional and national sales. The measures look like this:

Sales = SUM(FactTable[Net Sales])
Regional Sales = CALCULATE([Sales], ALL(FactTable[Store Number]))
National Sales = CALCULATE([Sales], ALL(FactTable))

And it ends up looking like this:

Note that the Store Number is selected, but the Store Region is not, it's just the result of cross-filtering. Regional Sales incorrectly matches National Sales. If I then select the Region, the measures work:

I'm actually using VBA to change the Store Number slicer, as the end users don't want to select the region, then scroll through a list of store numbers. They just want to enter a store number and hit enter. I've tried a few things in DAX and VBA, and failed.

View 2 Replies View Related

Power Pivot :: One Slicer To Control Two Pivot Tables That Have Different Source Data And Common Key

Jul 8, 2015

I have two data tables:

1) Production data with column headers: Key, Facility, Line, Time, Output
2) Costs data with column headers: Key, Site, Cost Center, Time, Cost

The tables have a common key named obviously as Key. The data looks like this:

Key
Facility
Line
Time
Output
Alpha

I would like to have two pivot tables which I can filter with ONE slicer based on the column Key. The first pivot table shows row labels Facility, Line and column labels Time. Value field is Output. The second pivot table shows row labels Site, Cost Center, and column lables Time. Value field is Cost.How can I do this with Power Pivot? I tried by linking both tables above to a table with unique Keys in PowerPivot and then creating a PivotTable where I would have used the Key from the Keys table.

View 5 Replies View Related

Power Pivot :: Slicer And Pivot Table Value Order

Oct 9, 2015

How to get a list of values to actually display in correct order in either a slicer or when on an axis on a pivot table?

I currently have the below list and have tried to add a preceding numeric (ex. "1. <=0") or preceding blank space, neither of which is visually great. Is there another way?

<= 0
1 - 6
7 - 12
13 - 18
19 - 24
25 - 30
31 - 36
37 - 42
43 - 48
49 - 54
55 - 60
61 - 66
67 - 72
73 - 78
79 - 84
85 - 90
91 - 96
97 - 102
> 102

View 8 Replies View Related

Analysis :: Power BI Analysis Services Connector - Remote Server Returned Error

Mar 5, 2015

I have, a SSAS 2012 tabular instance with SP2, there is a database on the instance with a read role with everyone assigned permissions. When configuring the Power BI analysis services connector, at the point where you enter Friendly Name, Description and Friendly error message, when you click next I receive the error "The remote server returned an error (403)." I've tested connecting to the database from Excel on a desktop and connect fine.I don't use a "onmicrosoft" account so don't have that problem to deal with.

We use Power BI Pro with our Office 365. As far as I can tell that part is working ok as I pass that stage of the configuration with a message saying connected to Power BI.The connector is installed on the same server as tabular services, its a Win2012 Standard server. The tabular instance is running a domain account that is the admin account for the instance (this is a dev environment) that account is what I've used in the connector configuration. It's also a local admin account. There is no gateway installed on the server.

View 10 Replies View Related

Analysis :: Cube Needs To Be Deployed From VS After SSIS Analysis Services Processing Task Completes?

May 13, 2014

I have a cube that we are processing nightly via an Analysis Service Processing Task in SSIS.  In order to increase the performance of the processing time, we elected to use a lot of rigid dimension attributes, and do a full process of everything in the SSIS task.  The issue that I am having is that after that task completes, I need to go into Visual Studio to deploy the cube becuase we are unable to browse or use the cube.  This issue seemed to start once we changed the SSIS Analysis Service Processing Task to do a full process on the dimensions, rather than an incremental.

I would expect that once development is done, and it is processed and deployed, that is it.  My thinking is that the SSIS task should just update the already deployed cube,

View 2 Replies View Related

Analysis :: How To Right Choose Key Column In Mining Structure For Microsoft Analysis Services

Jun 12, 2015

How to right choose key column in"Mining Structure" for Microsoft Analysis Services?
 
I have table:

"Incoming goods"

Create table Income (         
ID int not null identity(1, 1)            
[Date] datetime not null,             
GoodID int not null,               
PriceDeliver decimal(18, 2) not null,               
PriceSalse decimal(18, 2) not null,               
CONSTRAINT PK_ Income PRIMARY KEY CLUSTERED (ID),             
CONSTRAINT FK_IncomeGood foreign key (GoodID)  references dbo.Goods ( ID )            
)

I'm trying to build a relationship(regression) between “Price Sale” from Good and “Price Deliver”.But I do not know what column better choose as “key column”: ID or GoodID ?

View 2 Replies View Related

Analysis :: Create Analysis Services Project In Visual Studio 2012 Data Tools?

Feb 18, 2013

It is possible to create Analysis Services project (*.dwproj) in Visual Studio 2012 Data Tools?

View 5 Replies View Related

Analysis :: Excel 2013 Pivot-table / Grouping On Date That Comes From Analysis Service 2008?

Nov 24, 2015

I am using

SSAS: version 2008
Excel: version 2013

I am connecting to SSAS cube from Excel and I have date dimension with 4 fields (I have others but I don't use it for this case). I created 4 fields in order to test all possible scenarios that I could think of:

DateKey:
    - Type: System.Integer
    - Value: yyyyMMdd
Date:
    - Type: System.DateTime
DateStr0:
    - Type: System.String
    - Value: dd/MM/yyyy (note: I am not using US culture)
    - Example: 01/11/2015  
DateStr1:
    - Type: System.String
    - Value: %d/%M/yyyy (note: I am not using US culture) 
    - Example: 1/11/2015  

Filtering on date is working fine:

Initially, in excel, filtering on date was not working. But after changing dimensional type to time, and setting  DataType to Date, as mentioned in [URL] filter is working fine as you can see in the picture.Grouping on date is not working:

I have hierarchy in my Date dimension and I can group based on hierarchy, no problem. But user is used to pre-build grouping function of excel, and he wants to use that. Pre-build functions of Excel, Group and ungroup seems to be available as you can see in following picture:

But when user clicks 'Group', excel groups it as if it is a string, and that is the problem. User wants to group using pre-build grouping function available in Pivot table. I also find out that Power Pivot Table does not support this excel grouping functionality. And if I understood well, this pre-build grouping functionality of excel, needs to do calculation at run time, and that is not viable solution if you have millions of rows. So Power pivot table does not support pre-build grouping functionality of excel and hence we need to use dimension hierarchy to do the grouping. But I am not using Power Pivot table, I am using simple Pivot Table. So I expect grouping functionality to be working fine. Then I tried to do simple test. I created a simple data source in excel itself. And use it as source of my Pivot table. Then grouping is working fine. The only difference that I can see is (When double click the Measure value in Excel),For date values of my simple test, excel consider them as 'Date'.

For date values of my data coming from cube, excel consider them as 'General'

    2.1. But value here is same as it was in simple test.

    2.2. 'Date Filter' works just fine.

    2.3. If I just select this cell and unselect it, then excel change type to 'Date' though for that cell. 

    2.4. I have created 4 different types of fields in my date dimension thinking that values of attribute of my dimension might be the problem, but excel consider 'General' for all of them.

    2.5 This value (that can be seen when double clicking on measure) comes from 'Name Column' of the attribute. And the DataType defined is WChar. And I thought that might be the reason of issue. And I changed it to 'Date'. But SSAS does not allow it to change to 'Date' giving error : The 'Date' data type is not allowed for the 'NameColumn' property; 'WChar' should be used.

So, I don't know, what is the puzzle piece that I am missing.

1. Date filter works, group does not work

2. Excel consider it as 'General' string.

3. SSAS does not allow to change 'NameColumn' to Date.

View 2 Replies View Related

Analysis :: Running Analysis Service In Terms Of Port Usage?

May 30, 2015

I would like to know the best practice for running analysis service in terms of port usage. Is it better to run on a specific port or have dynamic ports ? We have clustered servers that run default on 2383 but not sure with non clustered what's the best way to get performance.

View 2 Replies View Related

Analysis :: Creating Roles In Analysis Service Without AD Account

Aug 6, 2015

I want to find out if it is possible to create a role in a cube without an AD account, e.g. using a GMail email address.

View 3 Replies View Related

Use Cubes From Analysis Services 2005 To Analysis Services 2000

Oct 17, 2007

Hi,

I have some questions about SQL Servers 2000 and 2005 compatibility.
In my configuration I have to use both servers.
The cubes are stocked in 2005 server.
May I transfer from 2005 to 2000 Analysis Services the cubes?

If yes, what is the procedure? The result of migration is the same in the two different versions?


If not, how can I solve this problem?

Thanks in advance.

View 3 Replies View Related

IO Analysis

Mar 13, 2008

I'm looking to see if I have a issue with excessive IO activity caused by less-than-ideal access patterns.

So I've run this query to look at logical and physical reads for logical writes:

selectsql_handle, plan_handle,
total_physical_reads, last_physical_reads,
min_physical_reads, max_physical_reads,
total_logical_writes, last_logical_writes,
min_logical_writes, max_logical_writes,
total_logical_reads, last_logical_reads,
min_logical_reads, max_logical_reads
fromsys.dm_exec_query_stats

The question is, when can it be considered excessive?
Or can you just order the results by descending order and see if you can make improvements on the worst ones?

The same goes for this query for pageiolatch wait counters and times for indexes:
select database_id, object_id, index_id, partition_number, page_io_latch_wait_count, page_io_latch_wait_in_ms
from sys.dm_db_index_operational_stats (null,null,null,null)

View 2 Replies View Related

Analysis :: Using OR In MDX

May 12, 2015

I have SQL query, and I am trying to write MDX with similar logic. Logic has where clause:

Where (((CD_TYP_CAT_PARENT = 'LOAD' OR CD_TYP_CAT_PARENT = 'SPEND') AND (CD_TYP_CAT NOT IN('REVERSAL','REFUND','FEE')))  
  OR (CD_TYP_CAT_PARENT = 'OTHER' AND CD_TYP_CAT = 'SPEND'))

how do I write this in MDX? 

View 2 Replies View Related

Runtime Analysis

Jul 10, 2006

I just ran a testbed of 4 types of SQL queries:1. inline SQL with a StringBuilder2. managed sql3. SQL text processing (@SQL as varchar(5000); SET @SQL = 'SELECT ' + @var...)4. a regular sproc that has the columns and table name hard coded1,2, and 4 always end up at about the same time given the averages.3 is always at last 1.5 times slower, and usually closed to 2 times.1 and 2 both use StringBuilders, the code is a direct copy, and 3 is a copy as well.My managed SQL is:    [Microsoft.SqlServer.Server.SqlProcedure]    public static void usp_Items_Select_Managed(SqlString table, SqlString name,         SqlString value)    {        // sql          StringBuilder stringBuilder = new StringBuilder();        stringBuilder.AppendFormat(            "SELECT {0}.* FROM {0} WHERE {0}.{1} = {2}",            table,            col,            value            );        SqlConnection sqlConnection = new SqlConnection("context connection=true");        SqlCommand sqlCommand = new SqlCommand(stringBuilder.ToString(), sqlConnection);        sqlConnection.Open();        SqlContext.Pipe.Send(sqlCommand.ExecuteReader());        sqlConnection.Close();    }Is there anything wrong with my Managed SQL, or is this just the way that it is?Thanks

View 1 Replies View Related

Analysis Server . Pl. Help

Jul 23, 2002

Hello ,

I have a SQL 2000 with Analysis server installed on it . Its in a different domain . When i am trying to register the server remotely i.e from another domain ,from another server it gives me an error that :

'Cannot connect to the repository .
Analysis Server : <Server_name>

Error : Cannot open database requested in Login '<Database_name > Login fails . Do you wish to register this server ? '

Both the domains are trusted . Except that the OS where the Analysis server is running is Windows 2000 . I am trying to connect through SQL 2000 Server with Windows NT 4.0 Server .

We have another Analysis server which is running but on Windows NT 4.0 OS where i can easily register that server .
I tried providing access with all the rights to concerned domains , but in vain .
I am exhausted trying to figure out the problem .

Any assistance in this regard would be of great help to me .

Thanks
Ravi .

View 2 Replies View Related

Index Analysis

Aug 8, 2001

Hello,

I am tuning some queries and am using the Display Execution Plan option from Query Analyzer. What I am looking to for is somewhere which will explain the differences between the costs displayed when you put the mouse pointer over the object in the Execution plan output. BOL gives some general directions, but I need more specifics.

Any help on this would be appreciated

Thanks,

Bill

View 3 Replies View Related

Analysis Service Help

Dec 12, 2002

Hi all,

Is there any method by which I can use Stored procedures in Analysis services. I have some procedures which uses Temprorary tables. I wanna use those procedures's columns. Is it possible?

Thanks in advance!
Rahul

View 5 Replies View Related

New To Analysis Services

May 30, 2005

Hi,
Im pretty much new to AS and would like to ask some silly questions why I cant browse the data in my cube.

Version - SQL2K Developers Edition on W2K OS

Problem:
Im doing the tutorial which comes with Analysis Services. I have successfully followed their instructions and created and processed the 'Sales' cube.

However when i browse the cube, i get the error Unable to browse the cube 'Sales'. Unspecified error

Would anyone know why?
thanks in advance.

View 1 Replies View Related

New To Analysis Services

Jun 6, 2005

will anyone help me as iam new to AS and i created the cube using the sample tutorial.but when i try to browse the cube it is showing an error as "unable to browse the cube,unspecified error'.pls help me thanx in adv

View 3 Replies View Related

How To Use Analysis Services

Jan 25, 2008

hello everybody
I am new to bi project.my client want user activities to be captured into chart and cube.Into this context i just started reading analysis services.i read some document and implemented one simple analysis service project using adventureworksdw database.
My question is this like i have to work on analysis services using my own db.so directly can i use clint db(ie user table) or i need to write some script to change this db into a db which can produce cube and dimention.

Thanks in advance

View 2 Replies View Related

MS Analysis Services

Dec 10, 2004

I installed MS Analysis Services in my Windows XP (with SP1) desktop. When I try to expand the "+" sign beside the server, I got the following error message: "Unable to connect to the registry on the server, or you are not a member of the OLAP Administrators group on this server."

It is very weird that occasionally I can connect to the server without the above problem. But most of time, I cannot connect to the server and will get the above the error message.

Anyone knows how to fix this problem?

Thanks a lot!

View 6 Replies View Related

Analysis Services For DBA's.

Feb 2, 2007

A lot of job postings these days for SQL DBA's list the requirement of Analysis Services. Not having any experience with it I'm wondering what exactly is meant? Of course its going to vary from company to company, but I really have no clue what they mean in general? Is it cube design? MDX query writing? Simply backing up? To be clear, Im asking in regards specificically for a DBA, not a Developer.

TIA, ChrisR

View 10 Replies View Related

Analysis Services

Jan 23, 2008

I have been looking at Microsoft Business Intelligence tools. SQL Server 2005. I see great potential with Analysis server, and creating data cubes for reporting. This will offload much processing from my transactional servers.

I just haven't been able to identify the process for automatically updating my cubes over night. The documentation leaves me baffled.

Any assistance is greatly appreciated.

View 2 Replies View Related

Need Help Regarding Analysis Service

Dec 31, 2003

Hi,

I am running XP in the office and already have SQL's Enterprise Manager and Analysis services installed - which I can use to access the SQL databases on our office server.

Question:
I have installed the desktop version of SQL on my machine and am trying to register the server in the Analysis Service Manager but no luck.

Kindly help.
THanks

View 3 Replies View Related

Analysis Service

Feb 25, 2004

Hi

I was doing steps on page 15 / 16 of attached sheet


I was doing the microsoft example about cube to setup DSN SOURCE CONNECTION to ACCESS database (food mart) when i do a test it works

but after i do design storage and go to Process the cube
it gives me error

'test connection failed because of an error in initializing provide
(microsoft) ODBC DRIVER manager data source name not found and no default driver specified

one more error found
Microsoft ODBC DRIVER MANAGER driver sql connect attr failed: IMOO6

Thanks

View 2 Replies View Related

I Need Help With Analysis Services

Mar 15, 2004

Hi.
I have a problem with the caculation of the dimensions in the Analysis
Services and I need help.
In a certain dimension I make some calculations with the "personalized member formulas". Some cells are modified after this calculations. After this, in another dimension, other calculations are made using again the "personalized member formulas". With this operations some cells that were modified in the previous dimension should be modified again, but they aren't. Is there a way to fix the sequence of the calculation of the dimensions? Do they get calculated at the same time or some get done before others? Once one of this cells are modified using those formulas is it possible to modify them again? In the Hyperion's Essbase software ther is an option to calculate some dimensions before others. Does Analysis Services has that option or something similar?.
I hope you can help me. Thank you very much.

View 2 Replies View Related

Performance Analysis

Jun 12, 2008

Hi,

I've got a stored procedure and a view that do the same thing and i would like to figure out which one does what faster. I use SQL Server... what are some ways for me to figure out which is better in terms of performance?

View 5 Replies View Related

Analysis And Recovery?

Oct 17, 2005

I've been having SQL 2000 startup issues for a while now and upon checking the logs, noticed a bunch of messages about analysis of the database at % completion and recovery complete. Then I see messages about it shutting down and starting back up again. Is this what is causing the problem? What is going on?

View 2 Replies View Related

Analysis Services Vs SAS

May 18, 2007

Hi, all experts,

I am looking for some high level resource for comparing SQL server 2005 Analysis Services to SAS.
Some thing of a white paper or independent study nature will be grateful.

I hope some experts here have the expereices working with both and can speak of their expereices.

Thanks!

View 2 Replies View Related







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