Analysis :: SSAS - Measure Creation On NVARCHAR Column?
May 26, 2015I would like to know the possibility of creating Measure based on the below data.
I don't need to show any aggregate value. I need to show the value as it is.
ADVERTISEMENT
Analysis :: Calculated Measure Under Folder In SSAS
Sep 2, 2015How to put these calculated measures under the folder "sales line". i want to see it under the drop down...
View 5 Replies View RelatedAnalysis :: How To Update Linked Measure Groups In SSAS
Jul 2, 2015we are having an existing cube in that we need to update with new measures . The Measure groups are added to the cube as linked object. so when we are updating the measure group it is throwing the exceptions as follows..“Errors in the OLAP storage engine: The metadata for the statically linked measure group, with the name of 'SalesActual', cannot be verified against the source object.”
View 5 Replies View RelatedAnalysis :: SSAS Design Tip To Measure Across Date Range
Jul 22, 2015Developing a Retail cube using SSAS 2012. One of the dimension is DimCustomer with SCD type II. Each Customer can be a member or a non-member over a period of time. We have StartDt and EndDt to reflect the membership status.
eg:
Joe is a member between 06-01-2014 and 31-08-2014
Joe is a non-member between 09-01-2014 and 01-31-2015
Joe is a member between 02-01-2015 and 04-30-2015
Joe is a non-member between 05-01-2015 and 12-31-9999
Without adding fact row of Joe for each day to reflect the membership status, I want to provide the ability to measure "Active Customers Count" on a given date. There are 2 million customers in the DimCustomer Table.
Analysis :: Rename SSAS Measure Created From Dimension
Aug 25, 2015I have created SSAS Tabular model project.
I have dimensions like XYZID, Names, Zone etc.
When I create a measure using count function for XYZID. The name of the measure is Count of XYZID.
Its the same which gets displayed in Excel while I connect the model.
How to rename the "Count of XYZID" to ABC ??
Analysis :: SSAS - Display Measure Value Based On Other Attribute Member?
Sep 2, 2015getting Correct Measure based on Member Present in Other Attribute .
I am working on SSAS 2012 and have cube build and ready ..
I have Two Measure in Cube
[MEASURES].[Actual] and [MEASURES].[Target] and I need to create One more Calculate Measure
I have dimension DimProduct
I want to Display [MEASURES].[Actual] if Color "Purple" is present for PRODUCT1 Else Display [MEASURES].[Target].
MDX to create Calculate Measure for this logic?
Analysis :: Are Measure Expressions Supported In SSAS 2014 Standard Edition
Nov 12, 2014Are Measure Expressions Supported in SSAS 2014 Standard Edition?In 2005 SSAS, I remember that Measure Expressions were not supported in the Standard Edition, only Enterprise Edition.
View 2 Replies View RelatedAnalysis :: MDX To Fetch Measure Source Table And Column ID
Jun 12, 2015MDX query to get the source table and column for a measure from ssas database. i want below highlighted using mdx.
View 4 Replies View RelatedAnalysis :: Calculated Measure To Check Measure Value Updates By Month?
May 13, 2015In my cube there are two measures which are used in different calculations.Now I'm need to show in report if there any months in data when both or even another one of the measures is not updated (value = 0 or NULL).
how should I create the calculated measure for that?
I have tried in mgmt studio to plan this but I'm in a loop of errors.
Analysis :: MDX Filter A Measure Based On Another Measure
May 3, 2015I want to filter a measure based on another measure (both are measures on the same FACT table).Distinct Number of Users HAVING.User Cost above 0.
I tried doing having but because it’s two measures and not a tuple with a dimension it writes an eror.My query is something like:
SELECT
[Measures].[Distinct Number of Users ]
having [Measures].[User Cost] >0
ON
COLUMNS,
[code]....
Please note, that I want it in a MDX query not needing to change the cube or DWH table.
Analysis :: SSAS Tabular Missing Data Column Doesn't Deploy
Aug 2, 2015I have created a tabular model with VS 2013 Ultimate. I deployed the model several times and everything works great with Power Pivot.
One of the dimension tables, "Age", has an integer field that is null. I updated that column in SSMS to have values, processed the table in VS and the "Age" column now has integer values.
I deployed the model again, connected with Power Pivot, and the "Age" column still has not data. I refreshed the Excel spreadsheet several times, started a new one, deployed several times and there is still not data in the "Age" column even though it clearly exists in VS.
I opened SSMS on the Analysis Service and queried the table in question and it also shows no data in the Age column even though it is in the data table and the VS model.
I can't find anything different about this column than others that contain data. How can this be happening?
How To Change The Column Measure Into Row Measure In Reporting Services
Sep 25, 2007
Hi,
I am wondering how to create a matrix that contains 1 dimension for Top Label (Column), let's say "Year-Month"
and then 2 Measure to be in the row format rather than columnar format.
Example as below :
Year-Month on the column, and the measure is on the row :
2007-04
2007-05
2007-06
Amount Sales
1000
2000
3000
Unit Sales
10
20
30
Total
1010
2020
3030
Please share with me if you have this solution in Reporting services as it works in excel, hyperion brio, bo, cognos but somehow cannot see that function in Reporting Services.
Thanks
best regards,
Tanipar
Integration Services :: How To Change Measure Names In SSAS
Aug 28, 2015how to change measure names in ssas. i need to change it from Total measure to Total GM.
how to change Dimension names in ssas. i need to change it from ID to Master ID.
SQL 2012 :: SSAS Cube - Show Dimension Attributes For Only One Specific Fact Measure?
Aug 3, 2015I have built a fact table and few dimension views in Datamart with the aim of creating a Cube.
On the Fact table I have added a CASE Statement with the following threshold for Premium due amounts:
CASE WHEN....
'Due_0-1_Month'
'Due_1-2_Month'
'Due_2-3_Month'
'Due_Over_3_Months'
'Overdue_0-1_Month'
'Overdue_1-3_Month'
'Overdue_3-6_Month'
'Overdue_Over_6_Months'
...END
I then created a Dimension to link this to:
CREATE VIEW...
Select 'Due_0-1_Month' as Ageing_Threshold
union all
Select 'Due_1-2_Month'
union all
Select 'Due_2-3_Month'
[Code] ....
I was successful in processing the cube, however the problem is everytime I drag the dimension on the columns field in Pivot tables the Thresholds start to break up the other amounts that I have on display like Acquisition Costs, Tax amounts. I am only interested in showing the breakdown of Premium amount measure by the Threshold dimension.
somehow 'Hide' or 'prevent' the Threshold dimension from breaking down the other measures on the Pivot and only breakdown the amounts for Premium?
how I should structure my tables in SQL or any MDX queries to resolve this.
Integration Services :: Cube Creation In SSAS
Aug 24, 2015I need to create 3 fact table in a cube with dimensions ?? is it possible . What are the steps to do.
View 3 Replies View RelatedAnalysis :: Get Measure Value By Different Condition
Jul 17, 2015I am very new to MDX. I have the sales amount measure and date dimension.
I need two measure sales value for the below condition. I am going to display this result in SSRS.
1. For between from date and todate
2. For last week range
Analysis :: MDX - More Than 1 Measure On Output
Nov 12, 2015Any example on how to output more than one measure in an MDX query. Lets say I want to display stdev(x) and average(x) (or any other two measures)?
View 7 Replies View RelatedAnalysis :: How To Get Unique Values For Each Measure
Sep 2, 2015I am getting same value all over how to get unique values for each measures.
how to resolve it.
Analysis :: Measure Value Needs To Be Manipulated In Cube
May 18, 2015The data attached below is from a Fact table. When this data is browsed in the Cube the end user is only interested in value of Measure 1 when it is not equal to zero. Measure 1 is a base measure .how to suppress the value 0 for Measure 1 in the Cube.
DimesionKey1
DimensionKey2
DimensionKey3
Measure 1
Measure 2
Measure 3
[code]...
Analysis :: Filter A Set Based On Same Measure?
Jun 10, 2013I'm trying to create a percentile rank function based on the standard WIKI version:
I've seen Brian Knight's article here, but that only deals with percentile.
Where I'm struggling is getting the count of members in a set using a measure, in the current context on the same hierarchy, as the filter expression. I'm using the comparative set as in belonging to the same geographical location, and therefore associating by another attribute.
So, cl as below:
MEMBER [Measures].[RegionPercentileCount] AS
Count(
Filter(
NonEmpty(
descendants(Ancestor(
[Supplier].[NameMap].CurrentMember,
[Supplier].[NameMap].[Region]),
[Supplier].[NameMap].[Supplier Id]),
[Measures].[ActiveMeasure])
, [Measures].[ActiveMeasure] <
([Supplier].[NameMap].CurrentMember,
[Measures].[ActiveMeasure])))
Using the same measure and context hierarchy is always going to be equal, and therefore the count is always zero. Its almost as if I need a nested context for the FILTER which allows me to use enumerate the set on the same hierarchy whilst maintaining the external reference.
I'm thinking that perhaps I'm going to have to create another hierarchy and use that as the filter set and reference through StrToMember or similar.
Analysis :: Dynamic Calculated Measure In MDX
May 8, 2015 I have a dimension called as DIM1 which has list of all measures and has an attribute called as ATTRMDX Formula. The formula will be like
([DIM1].[ATTR Measure Code].&[M1],[Measures].[ATTR MEASURE VALUE])+([DIM1].[ATTR Measure Code].&[M2],[Measures].[ATTR MEASURE VALUE]).
I want to pass this formula to a calculated measure as given below -
MEMBER [Measures].[FormMeasure] as ([Measure].[ATTRMDX Formula].currentmember.MEMBERVALUE)
but I get the string value itself as output, but when I put the formula as a string in the calculated measure I obtain the value.
Analysis :: MDX - Count A Set As Calculated Measure
Sep 9, 2015I am trying to count a set as a calculated measure, when this set is called directly in the row , it returns fast, but when i try to count the set as calculated measure(so i can slice with another dimension) the query keeps running forever.
The queries are below
select
{} on 0,
nonempty
(
{([Transaction].[RPC Count].&[1],[Transaction].[Account ID].[Account ID])}
,
{([Account].[PAYMENTSTATUS].&[0],[Account].[Account ID].[Account ID])}
) on 1
[Code] .....
Analysis :: Split Wide Measure Group?
May 14, 2015I have a wide fact table that I'm feeding to an SSAS cube. I was advised that splitting the measure group into two will improve performance when querying the cube.
I cannot find any documentation that supports this, in fact I get a blue curved line suggesting that I merge the measure groups since they have the same dimensionality and granularity.
I guess the best practice is what the blue line states, but without knowing the internals of SSAS I can undestand that a smaller measure group may be easier to handle, or create more specific aggregations for.
Analysis :: Filter Measure On Intersection Of Dimensions
Jul 24, 2015SSAS 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.
Analysis :: Drillthrough With Measures From Two Measure Groups
Nov 4, 2014I'm trying to show measures from 2 measures groups in a drillthrough. Obviously, it's not possible with a standard drillthrough action, but I still hope that I can somehow achieve this with the ASSP GetCustomDrillthroughMDX function.
Speaking in AdventureWorks2008R2 terms: Imagine I have a pivot table with Product Categories in filter (say, filtered on Gloves) and "Internet Sales Amount" as measure. From context menu in Excel I can call the drillthrough action which shows me the individual sales records. I would like to show in drillthrough additionally "End of Day Rate" measure from "Exchange Rates" measure group.
One option would be to join FactInternetSales with FactCurrencyRate and make EndOfDayRate a physical measure in the "Internet Sales" measure group. This is a pretty huge overhead for my scenario and I'd like to avoid this.
Another one would be to call something completely external for a drillthrough (for example, a SRRS Report).
Analysis :: Measure Group Execution Order
Sep 21, 2015I am executing a CUBE which is having 9 measure groups, i need to know order of the execution of the measure groups.??
ex:
1. fsales
2.ftranssales
3.fitem
4.fstores..............etc...
I know, the dimension are executed first, but not aware of the order of execution of measure group.
Analysis :: Where Clause On Measure Group In MDX Query
Nov 11, 2015When I am executing below MDX query, it's giving correct result with out any issue
SELECT
NON EMPTY
{
[Measures].[Daystorecieve] ,
[Measures].[PO Recieved],
[Measures].[Post Award Milestone PO Analysis Count],
[Measures].[Powith80pct Received]
[Code] ....
After the successful execution of the above query, I am trying to filter on my measure group [Measures].
[Daystorecieve] values not equal to "0". With minimum number of the dimension selection my query executing fine.
Please find the below query.
SELECT
NON EMPTY
{
[Measures].[Daystorecieve] ,
[Measures].[PO Recieved],
[Measures].[Post Award Milestone PO Analysis Count],
[Measures].[Powith80pct Received]
} ON COLUMNS,
[Code] ....
But, when I am trying to execute with total number of dimensions. It's running long time and giving out of memory exception. Is there any way to apply where condition on my measure group like where [Measures].[Daystorecieve]<>0.
Analysis :: Dynamic Measure Value Based Upon Role / Department
Sep 28, 2015I have an issue related to SSAS security. We have an SSAS multidimensional cube which needs 3 types of security:
- Access to the entire cube => OK, based upon a role
- Restricted access to one department (= dimension) => OK, based upon a role
- Access to the entire cube, but with dynamic security on 2 measures.
Let's say, we have 2 departments (food and non-food). Users within food are allowed to see sales and pieces from the food department, but not from the non-food department.
It is not an option to restrict access to the non-food department because there are other measure which they have access to. I tried cell security, but this is very slow and generates multiple empty rows on my selections.
Analysis :: Check To See If A Measure Is Of Certain Data Type In MDX Query
May 8, 2015I am using the following approach to enable dynamic date calculations: [URL] ...
However, some measures I work with are percentages (not numeric values) and demand a different formula.
See example snippet bellow (first IIF statement)
/*Calendar*/
[Date Calculation].[Calculation].[Calendar Actual versus PY WK %] =
IIF
(
[measures].Name = "HSP2 %", -- < ThIs is where I am trying to check if the measure is a percetgae type
0, -- < When this is the case then do something different
[Code] ....
I am trying to access the metadata. I am aware of $system.mdschema_measures but I am unable to utulize this for my case.
Analysis :: How To Get Last Processed Date For Each TABLE And Display In Measure
May 11, 2015I can pull a last processed date for the entire model: URL...That works great for showing the last time anything in the entire model was processed. It is not table specific. Updating / processing table A also changes the timestamp on tables B and C.However, if I want to look at just a specific table in the model (build a column for each fact table and a measure to go with it) I find that doing any process operation updates all the =Now() columns in all the fact tables.If I have a model with 3 fact tables and I do a process table using ProcessFull on one of them, all three tables calculated columns "LastProcessed" =NOW() are updated. URL...
Type>ProcessFull</Type>
<
Object>
<
DatabaseID>MyModel</DatabaseID>
<
DimensionID>TableA</DimensionID>
</
[code]...
Is there a way to setup a measure in the model on a per table level to show last processed date for each individual table? LastDataRefresh:= MAX ('TableA'[LastProcessed])
For example: Our sales table may update three times a day, where as our warehouse inventory table is only updated nightly.I wanted to let end users see by adding a measure for each table when the last process event was for a given table in the model.
Analysis :: Tabular - Filter / Search For A Specific Measure Value?
May 14, 2015One of my models has order data, cost per order/invoice ID and then dimensions on Fiscal Year, category, etc...the usual.
A user wanted to search it for an exact order amount. (They knew for example that one of our accounts was not balancing by single order worth $746.13 and assumed it must be an order that was placed but never marked shipped that slipped through the cracks).
Now, in the model I have "order amount" as a field and then a measure that sums that.
I could expose that "order amount" field as a label and let them filter on it in Excel (and that works).
However, I haven't had any luck filtering on the actual measure "Total Order Amount". Such as OrderID-> View Filter -> "Total Order Amount" equals 746.13.
I assume this is due to a few things:
Measure calculates at different levels so filtering on a measure is difficult as you would have to place all the "slicers" and set them first before the measure would "exist" at a level where it could be $746.13. Orders by year would have $746.13 as part of it's year sum, but wouldn't exist as a stand alone line item orders by year 2015 might be 2 million.
Orders by category might exist at 500,000, 8,000, 15,146.36, etc... but not $746.13.
So I would need OrderID on there as a column so the measure could return at the value of $746.13 for one row for it to match the filter?
Basically:
1. Why it can't really filter on a measure?
2. Is there a better way to accomplish this other than exposing the actual column in the fact table "order amount" as it feels like that could cause all kinds of confusion if other users try to slice/filter on that not realizing exactly what it is meant to be?
Analysis :: How To Apply A Filter To Decide Which Formula Is Used In A Calculated Measure
May 8, 2015The user wants to be able, using excel, to apply a filter to all measures in every measure group. I though that I can create a dimension with a single level with two members, let´s say "on" and "off" and depending on the selected member and using an IIF statement decide which formula applies to the calculated measures.
I have serious doubts about the performance and for this technique because I am thinking as a .Net developer and not as a cube developer. Maybe it is better to resolve it scoping the measures but I cannot figure it out.
Analysis :: How To Make Calculated Measure Behave Based On Dropped Dimension
Jun 25, 2015i want to create a new measure that will behave based on the dimension dropped,ex. if i added the employee dimension only it will aggregate data from the #Calls Count but if i added the product dimension it should display # Product Calls at the product level and #Calls Count at the employee level as shown in the screen shot.
View 7 Replies View Related