Analysis :: 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
ADVERTISEMENT
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.
Analysis :: 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 :: 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 :: 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.
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
Measure Group Attribute Key Column Does Not Match Source Attribute
May 16, 2008
HI,
I had to change the key columns of a dimension attribute to fix an error. I did this in BIDS. The change was from a single key column to a composite key column. Now I am getting these error when I process the cube:
Measure group attribute key column x does not match source attribute ..
I looked at the cube XMLA definition under mesaure groups and it still shows a single key column with inherited binding. However, the BIDS does not give me an option correct this in any way. I have had to do this once before and the only option seems to be removing the dimension from the cube and add it back in. But that is very error prone since I lose any specific settings at the cube dimension level not to mention aggregations no longer include the dimension, etc.
Not seeing an alternative, I went through each measure group (I have 7) and changed the key columns manually in the XMLA and saved the cube. This worked, but I don't understand why BIDS automatically doesn't do it.
Is this a flaw in the BIDS or I should be missing something.
thanks
MJ
Fetch Data From XML Column To Table
Aug 29, 2007I do an insert of the xml into the table and that works fine, but how do I split the tags to different tables. I have tried SSIS and a XML Source to an OLEDB Source, but since the xml file contains different groups that do not work.
The xml is created by Infopath and it seems like the groups are created if the components belongs to different sections.
Table1
id int,
xmltag xml
I am starting to be desperate, I really need some help solving this one way or another.
Analysis :: 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 :: 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 :: 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 :: 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 :: 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 :: 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 :: 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 :: 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 :: Proactive Caching On Rolap Dimension - Constant Change Notification On Source Table
Aug 20, 2011I have SSAS 2008 Enterprise sp2 (not R2) running on a production server. One of the dimensions is defined as real-time Rolap - set to clear the cache whenever the table changes.
I placed triggers on that table to be sure, so I know no changes occur on that table sometimes for 10 or 15 minutes at a time - and yet I get 100's of change notifications (I noticed them on a profiler session). It seems as though the notifications come as often as the dimension gets queried.
I think this is causing one of my longer running queries to fail because of locking issues. I assume this is because the queries are getting killed when they're in middle of using an outdated cache.
The strange thing is that I cannot reproduce this on the dev servers. The main difference between the dev and production servers is that the production servers are behinfd a cluster. I don't know if that could make any difference.
I really need to know why I'm getting so many notifications.
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 RelatedAnalysis :: Cube Measure Does Not Show Any Data When Drag And Drop In Browser
Aug 26, 2015I created SSAS cube in VS 2008 and have been able to deploy it successfully to the server. While creating the cube I was able to browse dimensions and all underlying tables just to make sure it has data. After deploying successfully when I drag and drop any measure group to browser it does not display anything.
The only thing I did different from straightforward cube building process was that when I created those measure groups the partitions that were created by default were giving me some unknown errors so I had to delete them in order for cube to process successfully.
Did that made any difference because I thought partitions are for improving query performance and has nothing to do with cube processing errors.
Analysis :: Browsing Measure Via Fact Dimension Showing Incorrect Data?
Sep 6, 2011I have a Fact table that contains several degenerate string values that I have pulled into a Fact Dimension.
When I browse the cube and cut one of the measures by an attribute from the Fact Dimension, I am getting incorrect data.
In other words, when I query the fact table directly via SQL and apply the same filters, I see the data I am expecting to see. But cube browse with same filters yields different results.
How can this happen since the fact dimension has a 1:1 relationship with the fact table.
I do have the Dimension Usage configured properly.
Is this an aggregation thing? Attribute key thing? What am I missing?