Analysis :: SSAS MDX Running Total Of Items In Current State?
May 22, 2015
Let's have a table to show what I mean ...
As you can see I have added only one contract (for the sake of simplicity). At any given date I want to calculate the running total for a contract (all contracts), but the aggregation must take the contract state into consideration.
You can see my expected result for queries 1 - 3. That means, with the actual state ('B') I want to aggregate all values regardless of their state in the past.
For query 2 the state is 'A' ... I hope you can follow.
Now my aim is to aggregate all contract states for any given date with the right value. The past states of each contract are not relevant. And of course future ones (with respect to the query date) shall also be irrelevant.
Maybe the solution is a combination of 'LastNonEmpty' and SUM/PERIODSTODATE ...
I have a requirement where I need to show the maximum value in grand totals but for the dimension members the same measure has to sum.
For ex: lets say I have a measure called Test and this is a base measure. The aggregation type set to this is SUM.
For this same measure the grand totals should not show the sum instead it should show the maximum value of the dimension members which is being analyzed across.
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).
We are having blocking issues on our server. Recently, I noticed that we no longer have any thing under Current Activity. When I click the plus sign beside it, it shows no items - that is, no process info, locks/process ID, or the locks/object. Does anybody know why and how I can reset it?? Thanks for your help.
We sell & ship packages that contain multiple items within them. The actual package (we call it the "parent item") is in the same table as the items within it ("child items"). If the record is a child item within a package, its "ParentId" field will contain the ItemId of the package.
So some sample records of a complete package would look like this:
ItemId's 2 & 3 are items contained within the ItemId 1 package.
Now however, the client wants us to build a report showing all packages (all items where ParentId is NULL) however, they want to see the QtyAvailable of not only the package but the items as well (a total of 15 when using the example above), all grouped into a single line. So a sample report line would look like this:
Name | Available Qty -------------------------- Package A | 15 Package B | 100
How can I do a SELECT statement that SUMS the "QtyAvailable" of both the parent & child items and displays them along with the package name?
I have the following record set; for each group where the code of OP=MTL summarize Cost; that is group 10-10= 300, group 20-20=300, group 30-30=600. Then I need to total groups 10-10 and 30-30.
I used ReportItems fields for each break as;
=SUM(IIF(Fields!OprSeq.Value=10 and Fields!RelatedOperation.Value = 10, Fields!Cost.Value,0), "Related Operation") etc,
But obviously when the result is false the ReportItem returns zero, and now I lost the value calculated previously.
I received this error when I tried to change the Authentication mode from windows to windows and sql server. a window popped for sa password and i put my password and the error pops. im using sql server 2005.can help?
What i need to find is items and datediff from current date to created date with respect to wait area. You will get more clear with example
For Eg. For items 1 the wait area changes from A1 to A2 at 2011-10-16 and remains A2 so i need to find the date differeence from today date to 2011-10-16
For item 2 that wait area remains same for entire period that is A2 so datediff from today date to 2011-10-19(last created date of that item)
For item 3 that wait area A2 then it becomes A3 then becomes A1 so need datediff from today to 2012-01-20
For item 4 that wait area A2 remains for the entire period so need datediff from today-2012-01-21
The expected output wanted items---daycount 1-----datediff(day,checks.datecreated,getdate()--here checks.datecreated will be 2011-10-16 2-----datediff(day,checks.datecreated,getdate()--here checks.datecreated will be 2011-10-19 3-----datediff(day,checks.datecreated,getdate()--here checks.datecreated will be 2012-01-20 4-----datediff(day,checks.datecreated,getdate()--here checks.datecreated will be 2012-01-21
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.
I have created a very simple report that works correctly when in Report Designer Preview mode. When I deploy to the report server it reports that deployment was successful. When I log into Report Manager the report is visible, but when I click on it I end up at an error page.
The error that I get is: Operation is not valid due to the current state of the object.
We are close to exceeding the Maximum Row size of 8K per row and the CIO asked me "how close are we to reaching the 8K row limit?"
I began by looking at byte requirements for the data types we are using from the systypes table then I wondered, do the indecie, Foreign Keys and Primary Keys use bytes which must be included in my Calculation for current number of bytes used per Row.
Question: How do I calculate Current total number of bytes used per row.
I have a On_hand_Quantity report which has a parameter drop down to select about 200 stores.
When i run this report in BI studio , selecting all the 200 stores for the parameter it works fine.
But when i upload the same report to report manager and try selecting all the store_numbers it errors out saying 'operation cannot run due to current state of the object ' This report was running fine since the past 2 years, on 30th dec we did some .NET updates (KB2656356 and KB2656351) after which this report stopped working.
I tried troubleshooting all ways but it errors out only in report manager.
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])
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?
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.
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
I have two ssas databases with same number of cube in them. Cube names are also same. I want to merge/combine these two as one so that my reporting application may see a single database having measures/dimensions from both databases.Is it possible?I am doing this to achieve loose coupling between base and regional development work. if above is not possible, any way to achieve minimum dependency between a base and a regional project.
I am facing issue with partition processing. I am having a SSAS cube which is having 5 partitions. These partitions are processed through a sql server job using SSIS packages. In packages I used SSAS process task to do this.Now problem is, job is running successfully and showing that the step which is having partition process also fine.But data is not updating in the partition. While checking the partition properties, it is not updated with recent date and time.
When I try to manually process the partition, it is getting succeeded and recent data is getting reflected with recent date and time.Package configuration is done in job itself.
I got this error from a sql agent driven cube process yesterday and am wondering where the log of errors is created...
Executed as user: xservername$. <return xmlns="urn:schemas-microsoft-com:xml-analysis"><results xmlns="<root">http://schemas.microsoft.com/analysisservices/2003/xmla-multipleresults"><root xmlns="urn:schemas-microsoft-com:xml-analysis:empty"><Messages xmlns="urn:schemas-microsoft-com:xml-analysis:exception"><Warning WarningCode="1092354050" Description="Server: Operation completed with 1042 problems logged." Source="Microsoft SQL Server 2008 R2 Analysis Services" HelpFile="" /></Messages></root></results></return>.
The step failed.if its a property I can see related to the server in ssms, which property as I see lots of stuff with the word log in the property name.
Is it possible to have more than one cube under one SSAS database? For example I have a database "Test" and in this the cube exist is "TestCube", is iit possible to deploy another cube "TestCube2" under the Test databse?
If yes then what is the process to do that, the reason I am asing is there are some common dimensions used n both the cubes and I am not sure what is the best way so that I can use the shared dimension?
I've been working with SSAS for a good few years now but I keep bumping into this problem - my users are trying to build a measure that is based on a calculated attribute and finding it difficult to work out how to write the MDX to do so. Intuitively, they thought a Calculated Member would work, but I don't think a Calculated Member is quite the same thing from my understanding.
So, here's the scenario.
We have a Product Dimension. We have a Measure that is the Number of days the Product took to make, e.g. 5 days. We also have a Product Count measure that counts the number of Products.
The user would like to write a calculated measure that works out the number of products that took <5 days, 5-10 days, 10-15 days etc.It would be easy to write a set of calculated measures for each of these bandings, but the user wants effectively a single dynamic attribute to use in the calculation in order to automatically distribute these values across the columns in their pivot table.
Is this even possible? I was thinking I could build an attribute on the Product Dimension in the ETL to do this quite easily, but the user wants to be able to change the bandings on the fly by changing the MDX for the attribute, rather than go back to the developer every time.
I need to find the percentile using cube so i am using the below formula :
((n-1) * p /100) -1
n= count of number of array records p= percentile
I am using below MDX query:
WITH MEMBER [Measures].[PV] AS 25 Member [Measures].[CntCT] as Count(NonEmpty([Tb City].[City Name].&[DC], [Measures].[CPT1])) Member [Measures].[PVInt25] as Int(((([Measures].[CntCT] - 1)* [Measures].[PV])/100) - 1) Member [Measures].[PVC] as ([Measures].[CPT1],Order(NonEmpty([Tb City].[City Name].&[DC],[Measures].[CPT1]), [Measures].[CPT1],ASC).Item([Measures].[PVInt25]))
Select [Measures].[PVC] on columns, {[Tb City].[City Name]} on rows from test;
The line 2: Member [Measures].[CntCT]
In that i need to find the n count of rows where city is DC (City is my Dimension) in Measures CPT1
But currently it is giving the result 1 instead in actual in my test cube there is the city DC exists with 23 CPT1 rows count.
I tried the below query:
SELECT NON EMPTY {[Tb City 1].[City Name].[City Name].&[DC] } ON COLUMNS, NON EMPTY { ( [Measures].[Tb Main Count] ) } ON ROWS FROM [test]
Above query gives me the correct count i.e. 23 but i need to get the result of above query in line 2 of MDX query:
I want to implement population data in sales cube.
Fact table has customer code which is foreign key of Customer master dimension which in turn is linked to census data dimension. Census data dimension have city wise population data having foreign keys of zone and state.
I was trying to update an SSAS database on a test server and got the following error when trying to deploy from Visual Stuido. I get a similar error in SMS. I tried to delete the database before deploying but I get an error when trying to delete. I rebooted the server.
(SQL Server 2005 R2, Windows Server 2008R2.)
Error 1 File system error: The following file is corrupted: Physical file:
?D:Program FilesMicrosoft SQL ServerMSSQL.2OLAPDataHeidtman Sales Cube.0.dbDim Age.0.dim5.Dim Age.Dim Age.dstore. Logical file . Errors in the metadata manager. An error occurred when loading the Age dimension, from the file, '?D:Program FilesMicrosoft SQL ServerMSSQL.2OLAPDataHeidtman Sales Cube.0.dbDim Age.5.dim.xml'. Errors in the metadata manager. An error occurred when loading the Heidtman DW cube, from the file, '?D:Program FilesMicrosoft SQL ServerMSSQL.2OLAPDataHeidtman Sales Cube.0.dbHeidtman DW.7.cub.xml'. 0 0
I need to limit the sessions to access to SSAS cube to one per user. For example, if a customer uses Excel to check a cube, two or more users cannot use the same session or account.
how to move the dimension attributes from currency to geography and vice versa(i.e need to change their positions) in SQL Server 2012. i need currency to be placed in top of geography or geography below currency.
