.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SSAS 2008 - I cannot seem to have semi-additive and normal additive measures in the same measure gro

Posted By:      Posted Date: September 15, 2010    Points: 0   Category :Sql Server
I have a measure group with both conventionally aggregated measures (aggregation type SUM) and semi additive measures (LastNonEmpty). The cube processes them quite happily, and I can view and analyse (slice) them fine, but when I analyse them both together, then the conventionally aggregated measures (aggregation type SUM) always dissapears in favour of the semi additive measures. This is not the case if I analyse conventionally aggregated measures (aggregation type SUM) and semi additive measures (LastNonEmpty) from different measure groups.Why does this happen, and is there a way to overcome this by pref not creating seperate fact sources for the conventionally aggregated measures (aggregation type SUM) and semi additive measures (LastNonEmpty) currently in the same fact source.

View Complete Post

More Related Resource Links

MDX - ParallelPeriod using a semi additive measure

Hello, I'm having some trouble trying to get a figure for previous year when using a semi additive measure (LastChild). For a measure that uses Sum the process would normally be: SUM(PARALLELPERIOD([Time].[Fiscal].[Fiscal Year], -1, [Time].[Fiscal].CURRENTMEMBER), [Measures].[Whatever] )   But obviously if I do a sum against a semi-additive measure it gives me a massive amount.   To give a little background the data that is in the source table is already a summed amount so the table is: Date                    Category            Amount 01/04/2010           Blah                   3654   etc.   Is there any way I can achieve an equivalent of the above MDX using a LastChild member? The only other thing I can think of doing is to split the source so that (for the above) 01/04/2010 in the blah category would have 3654 rows and then do a sum of that in AS.   Any ideas??   Many thanks in advance :-) yayomayn

semi additive measure

Hi, I have have started working on SSAS recently. And was reading about semi - additive measure. And the most important point that struck me was all semi additive use the selected function for the aggregation with time dimensions and sum with all others. So i decided to test it by building a very small cube with two dimensions i.e. Period and Product.  Period dimension has single hierachy with two attributes i.e. year and month.  where as my product dimension contains only one attribute hierarchy i.e. product. I have defined a measure called closing stock with aggregate function being set to lastnonempty. Note: My period dimension is of Type Time I process the cube and start browsing it using cube browser. I first drag period hierarchy on my rows and measure in data area and it properly shows lastnonempty for that particular year. I remove period hierarchy and drag product attribute on rows but rather than summing it up it still shows lastnonempty for all products. Is there something i am missing? Please let me know. Regards  Niks  

semi additive measure in an other dimension that time




I would like to prevent aggregation of customer measure on product dimension. But customer is aggregeable on time dimension !. it is a semi additive measure but in another dimension that time dimension.

in this mdx example (Adventure works), how it is possible to prevent aggregation of customer measures (preferable in the MDX script of the cube) to force customer to be null when calculated members are evaluated.


In this example the result should be null for customer for [Product].[Product Model Lines].[R

Semi-Additive Annual Comp Measure Puzzle


Environment: SQL Server 2008 (not R2) Analysis Services; Panorama Novaview

I've created a factless fact table that records employee information for a payroll warehouse. Users can use dimension attributes such as Annual Compensation, Full Name, etc. to find out information about employees. There's currently only one measure called "Headcount" which is a distinct count of an employee ID (unique to the employee). There's a row for each day for each employee and the employees attributes describe how he/she looked on that day (using type 2 SCDs).

Users have requested that they see the sum of the annual compensation. This seems more like a semi-additive measure than anything else. For example, let's say a department has 3 employees for the year of 2009, each with annual comps of 20;30;40 (thousand) respectively. In the actual relational table, there will be a record for each employee for each day (point in time information). If annual compensation is added as a measure, then the measure would be = Annual Comp * No. of Days in 2009. That means, we'd have 20*30*40*365 ---- this is not what they want to see. They want to see a total of $90,000.

How could you do this?

Here's some more info about this:


SSAS 2008 Measure group Distinct count

Hi all, I have a data of as number of trasactions,DD, SO, BouncedDD, CancelledDD all are in count (number) while adding these measures manullay to a measure group I have selected usage as DistinctCount for one measure and for all the remaining measures as DiscinctCount.While deploying the cube it shows error as "Fact table canot have more than one distinct count"  

Measures for Additive vs Non-Additive Data. What is cube design best practice?


I'm looking more for SSAS 2008 best practice design advice, rather than for an answer to a specific question (although I have a specifc set of examples).

First issue:  Creating a non-additive measure group and an additive measure group.  We have some fact data in our current cube that is additive, and some fact data that is non-additive; all stored in the same fact table.  We do not currently have measures implemented that reflect this aggregation distinction.  Question:  Is it considered good practice to segregate additive and non-additive fact data into a) different fact tables and/or b) different measure groups?  My thought is that it would be an acceptable design approach, but am looking for feedback.

Second issue:  Non-additive fact data is only available at a non-leaf grain.  The example here is that we have non-additive fact data which is only available for the 4th or 5th levels of our 6-level geography dimension.  Our solution has been to create a custom geography branch, which now essentially serves as our 'aggregation treatment' for non-additive fact data.  I don't believe it's a good practice to have the geography dimension serve this function because we end up having to create a custom geo member for each non-additive fact data element.  Question:  What is considered best practice

Writeback for non-additive measures


I have a fact table with some measures that are additive, and some measures that are non-additive. In my application (planning/forecasting type), I would like to let the users modify the non-additive measures too , possibly at non-leaf levels, persist the changes and make them available to other users of the cube. The logic for spreading the non-leaf level changes is application-specific.

Given that the writeback ability exists only for additive measures, what are the recommendations for developing custom application enabling changes to non-additive measures  ? Is there a way of directly writing the changes at the leaf cells to the relational source and refresh the cube ?




DR Setup for SSAS 2008

Hello Guru's, I'm new to SSAS env. and want to set up its DR env. Can anybody guide me with any good strategis and implementation practices. We are using SSAS 2008 on 2 node Cluster Server. Thank you in Advance.- Nilesh

OLE DB Error: Network-Related or Instance-Specific while Deploying SSAS 2008 Cube

Hi, I encountered the below listed error while attempting to deploy SSAS 2008 DB: Error 1 OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Named Pipes Provider: Could not open a connection to SQL Server [5]. ; 08001.  0 0  I am running a standalone MSSQL 2008 named instance on Windows Server 2008 R2. Here are the steps I have taken but yet the issue is not resolve: 1) Enable Remote connections on the server 2) Enable TCP\IP 3) SQL Browser is Running 4) Set Firewall Inbound Exceptions for SQL Browser, Default Port 1433, UDP Port 1434 5) Using Management Studio I can successfully connect to the MSSQL named instance from the app server 6) I can ping I MSSQL box from the app server 7) Read and implemented all steps in the MSDN post below: http://blogs.msdn.com/b/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx Thanks for your help.

Shared dataset in SSRS 2008 R2 running on SSAS 2005

I am trying to create a report in SSRS 2008 R2 using SSAS 2005 as a datasource. My dataset has a date parameter. When I create an embedded dataset in my report everything works fine, but when I create a shared dataset with the exact same mdx query, I get the following error: An error occurred during local report processing. The definition of the report 'xxxx' is invalid. The Value expression for the report parameter 'DateCalendarHierarchy' contains an error: [BC30034] Bracketed identifier is missing closing ']'. Does anybody have any ideas? Is this a bug? Thanks, Louis

AdomdDataAdapter.Fill returns memory error when querying an SSAS Cube(2008)

For certain SQL's I will get the following error when calling the Fill method on an AdomdDataAdapter object. "Memory error: While attempting to store a string, a string was found that was larger than the page size selected. The operation cannot be completed."   Snippet of Code: DataSet ds1 = new DataSet(); AdomdCommand acmd1 = cn1.CreateCommand(); acmd1.CommandText = "SELECT ..."; AdomdDataAdapter ad1 = new AdomdDataAdapter(acmd1); ad1.Fill(ds1);   Now when we run this query in Ms Sql Studio we don't get any errors but the query returns 1 row with over 40,000 columns. I'm guessing the # of columns might be an issue but not sure.   When doing a Google search it appears that this problem was found in the 2005 and fixed in that version. Have any of you come across this problem?  

SSAS 2008 - Memory Error

During our unit and load testing of the SSAS database using VSTST 2010 Ultimate; we are getting a memory error with some of our MDX queries (below).  Futher analysis showed: The adomdDataAdapter "Fill" command is our error point The error is occuring when the result data set has getter than 16,384 Columns; actual returned column count is 41,339 columns - business requirement does not let us change this query at this time. The MDX query works fine in MS Excel 2007 The MDX Query works fine in MS SQL Studio 2008 How can we resolve the unit test data set with such a large column count? Memory error: While attempting to store a string, a string was found that was larger than the page size selected. The operation cannot be completed. In reference to thread: http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/4220adbb-438c-42e7-9612-56aebf8df435/ Thank you, Craig  

Excel 2007 Pivot table OLAP- SSAS 2008 Error

Hi, I do have a strange issue with Excel 2007 pivot table report; the data source is SSAS 2008 cube. When refresh the excel 2007 report data using the 'Refresh All' or 'Refresh' button available under the Data menu, the below error is thrown. "The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples." After some analysis, the report works well on the below workaround, 1. Remove all the calculated measures from report (3 measures in this scenario) 2. Remove one row label (dimension attribute) from report But the measures, attributes are valid one from cube and it is required fields in the report. So, understand that removing few items is not at all a solution. The below workaround also works. 1. Check the 'Defer Layout Update' option from the Pivot Table Field List window. 2. Click on the Update button After that Unchecked the 'Defer Layout Update' option and apply the filter, etc in the report. The report works very well. But every time, we need to use the above work around after open the excel report. I have NO clue on this issue. Please help to resolve this issue. Thanks, Jey

Cube Writeback SQL Server 2008 R2 (SSAS, write back)

CUBE WRITE BACK in SQL SERVER 2008 R2 Did anybody get the SSAS write back functionality to work against a decent sized datawarehouse? I'm not asking about a little demo but a significant sized footprint e.g. 14M Rows measures, 6-8 wired Dimensions with 8-10 attributes each. I setup a test server using a Dell XEON ( 2x4core) with 48 GB RAM hardware and the latest Sql Server 2008 R2 release. I used the What-If scenario in Excel 2010 and modified a higher level, hoping SSAS would push the values down. After about 15 minutes and after having used 48 GB memory (on average 5% CPU) Excel throw an error - short on memory - and stopped the action. I found the same behavior in Sql server 2005 and 2008 and was hoping this would work now but apparently not so. Here are my questions: Is anybody using this at all? how can one calculated (roughtly) how much memory is requiered? is there any paper that describes best practise? Thanks for any help in advance, Dirk  

SSAS 2008 Dimension root values changes after incrementally loading the dimension with 'process_up

The Dimension root  members (below 'ALL') changes after incrementally loading the dimension with 'process_update'.  How do I prevent that from happening ? There was no changes to data of the underlying table of the dimension.   

Can SSAS 2005 run with Sql Server 2008 database engine?

Are there any known issues of running SSAS 2005 with SQL Server 2008 database engine?

"Non-additive" target value in KPI. How to present it in a scorecard or Pivot Table.

Hi.. I would like to have some help in designing the following solution. It musn't be to hard, but i can't figure it out yet.. I have 2 tables: Group and Worker. Table Group has GroupID and GroupTarget. Table Worker has WorkerName, GroupID, WorkerTarget and Amount. The relation between tables is GroupID, as each worker belongs to a group. Now I want to take this to a pivotTable, to present KPI for each worker, and the value of the indicator is calculated comparing the Amount vs the WorkerTarget. Also I setup a single hierarchy in the PivotTable, where the parent is the GroupID, and when displayed, you can see all the Workers belonging to that group. Now the problem is, Everything is fine when I want to see the actual group total amount value, as I just have to SUM all the Amounts of each Worker, and I can see the GroupAmount. But.. ¿How can I display the GroupTarget in the PivotTable in the same column as the WorkerTarget? I don't mind a solution using SSAS or PerformancePoint, but my idea is in SSAS to put some kind of formula in the KPI target, so when I'm in a level of the hierarchy, the target is WorkerTarger, and when I'm in the parent, the target is GroupTarget. Is there some kind of formula like this??   If not, any other idea will be much apreciated.. Thanks a lot.. regards.. Canario O.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend