Analysis :: How To Change The Member Names Of Time Dimension
Oct 19, 2015
<SQL Server 2008 R2>
I created a Time table using BIS. I found that the default naming of time members is too long and redundant.
For example, the wizard generated "Fiscal Calendar 2015", "Fiscal Quarter 1, 2015", etc. However, shorter expression like "FY2015", "FQ1 2015", etc would be enough for me.
Is it possible to change the default naming rule, or does SSAS works correctly if I update the Time table values using SQL?
View 2 Replies
ADVERTISEMENT
Jul 7, 2015
I Create a measure group and two dimensions using [AdventureWorksDW2012], I try to change one dimension's storage mode with setting property proactive caching as Real-Time ROlap. There is no any warning message when deploying and processing, but error occurs when I query in sql server analysis services, see below for the error messages and the screen capture.
Error occurred retrieving child nodes: the current operation was cancelled because another operation in the transaction failed.
View 2 Replies
View Related
May 29, 2015
I am Using following MDX Query to get the data for max date in the Dimension. But every time I am passing date key to dimension to get data. How to automatically get the Max available Level member for that dimension.In this case I am passing 20150429 to every line instead i need to get that automatically.
SELECT
(
{
[DimDate].[HierYQMD].[Date Key].&[20150429],
{[DimDate].[HierYQMD].[Date Key].&[20150429].PARENT.PARENT.PARENT.LAG(4):
[DimDate].[HierYQMD].[Date Key].&[20150429].PARENT.PARENT.PARENT},
[code]....
View 4 Replies
View Related
Sep 10, 2015
Have a need to let the user select the year and month to use for filtering results from an MDX query that populates a report.I think all I should need is a list of years and a Month name / number.What would the filter look like if say I had a Date dimension that included the year number in a 4-digit and month in a 2-digit number? In TSQL it would be something like ... where Month = @month AND Year = @year...How do I get a distinct list of member values for the month numbers from the Date dimension?
View 6 Replies
View Related
Feb 24, 2011
I am using sql server 2005 enterprise edition.
How to list all the dimension and fact column names with mdx or tsql query...
View 9 Replies
View Related
Oct 28, 2015
I am facing an issue in MDX Query. I have a custom MDX query, When I run the query I am getting results too. Now my requirement is to show the dimension names in the query result, So that I can get those header names in the cell set itself. Please see the below image.
In this image I need to show 'Fiscal Year' and 'Fiscal Quarter' in that highlighted area. Is there any custom query for this?
View 3 Replies
View Related
Sep 8, 2015
I have 2 dimensions that pull their Facility Name from the same Location Dimension. The business users want to change Facility Name in the Material Facilities dimension to “Material Facility Name”, but keep Facilities dimension attribute the same. What is a good way to go about completing this task.
View 2 Replies
View Related
Jul 27, 2015
I have a monthly time period dimension representing average number of students for each month. At the yearly aggregate level I don't want it to sum up the avg number of students from every month because that number is incorrect. I would like it to use the number of students from the most recent month as a roll up. Is that possible to configure in SSAS?
View 2 Replies
View Related
May 21, 2015
I have a parent child dimension. a time dimension i have year.
Assume,
ParentID ChildID sales Year
171 171 10 2014
171 172 200 2014
171 173 300 2014
171 172 44 2015
if I pass 2014 and 2015 in sub select 171 data is not coming in result. i i pass only 2014 in sub select i get value of only 2014. if I pass 2015 in sub select i didn't get any value.
View 2 Replies
View Related
Aug 20, 2011
I 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.
View 2 Replies
View Related
Oct 26, 2015
When i add a dimension to the cube dimension without any relation in my dimension usage to any measure group my units are going down.However when i remove the dimension from the cube am getting the correct values.
View 4 Replies
View Related
May 25, 2015
below data,
Countery
parentid
CustomerSkId
sales
A
29097
29097
10
A
29465
29465
30
A
30492
30492
40
[code]....
Output
Countery
parentCount
A
8
B
3
c
3
in my count function,my code look like,
set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales")
set saleset(buyerset)
set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4}
set finalset as exists(salest,custdimensionfilter,"Sales")
Set ProdIP as dimproduct.dimproduct.prod1
set Othersset as (cyears,ProdIP)
(exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count
it will take 12 to 15 min to execute.
View 3 Replies
View Related
May 19, 2008
Hi!
Need some help building a query that does the following :
I have 2 Time Dimensions ; Time (Transdate) and ClosedDate (ClosedDate)
In my report/query, if [Time].CurrentMember = [Time].[YMD].[YMD].[2006].[200610].[20061031] I want to FILTER out all ClosedDate < [ClosedDate].[YMD].[YMD].[2006].[200610].[20061031]
Both Time Dimensions are Year -> Month -> Day and have the same Members.
I have every option available, using calculated Members and/or Measures to do this.
The report I'm creating is Aging of Receivables : Balance / 30 days / 60 days / etc.. But for the Aging, I need to filter like explained above.
Appreciate all help!
Regards,
Stian Bakke
View 3 Replies
View Related
Apr 7, 2006
Hi
Analysis manager wont let me create dimension unless fact table filed has value init. Other words, how do I create MT dimension(dimension without member)
thanks
View 4 Replies
View Related
Aug 15, 2007
I'm putting together a Kimball method SSIS package. My factSales table has an OrderRep key. If a match isn't found in the dimRep table I am inserting a dummy dimRep row and going on. That seems to be working.
My question is what do I do when the OLTP sales row has NULL for the OrderRep. This is possible; every sale does not have to have an order rep. My package is seeing that as a non match and trying to create a dummy row in the dimRep table for every NULL. I really don't want to do this. I can trap for the NULL rep and convert it to "unknown" or something but then the program would still create a single row in the dimRep table for unknown. Is that the best way to handle this? Or is there a way to trap for NULL and ignore the entire lookup process? A conditional split before every key lookup?
I have about 5 or 6 other dimension tables that will have the same NULL possibility.
Thanks.
View 5 Replies
View Related
May 21, 2008
hi all,
I was wondering if it is possible in SSAS 2005 that a calculated member is based off of an (integer) dimension attribute and another (integer) measure (let's say a multiplication operation) ?
If there a trick on doing so? other than stuffing the (integer) dimension attribute back in the fact table, as an measure?
thx much,
Cos
View 6 Replies
View Related
May 20, 2008
I want an MDX calculated dimension member, (with no measure specified in expression, so that any related measure can be used in a query/browse), and that accomplishes either one of the following:
Ideal: To calculate Median values on every level of a user hierarchy
If "Ideal" is not possible, then "Acceptable" is: To calculates Median values on one or more levels OTHER than the highest level.
View 3 Replies
View Related
Aug 10, 2015
i have a model contains fact of account revenue , a time dimension and scd of account.
the scd (type 2) is changing when an account get a new color.
when i queries the model by PowerPivot i sometimes get wrong color for an account in a date priod , for example , I would expect to see color 3 for account 1 at 04-06-2010 , But instead I see color 1 - as you can see in the picture bellow.
FACT , SCD :
Result
i process all , deploy , mark dim_time as date dim , and i still cant find the error.
View 3 Replies
View Related
May 28, 2008
Hi
A very basic MDX question. Using the following from AdventureWorks as an example:
Code Snippet
WITH MEMBER [Measures].[Big Cities]
AS
(
[Geography].[City].&[New York]&[NY]
, [Measures].[Internet Order Count]
)
SELECT
[Measures].[Big Cities] ON COLUMNS
, [Customer].[Total Children].Children ON ROWS
FROM [Adventure Works]
How would I re-write the [Measures].[Big Cities] calculated member so it included both [Geography].[City].&[New York]&[NY] and [Geography].[City].&[Los Angeles]&[CA]? Assuming there is no logical connection between these members, and that they are non-contiguous. Also, in my real example, I cannot move [Measures].[Internet Order Count] to the WHERE clause, it must remain within the calculated member.
Thanks,
Julia.
View 9 Replies
View Related
Jul 1, 2015
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
Else ROUND(([Measures].[Disconnects] / [AVERAGELIS] * 100) ,2).
I came up with
CASE WHEN [Measures].[End LIS] = 0
AND [Dim Date].[FSCL_YM].[Month Nm].currentmember.membervalue = Format(now(), "yyyy")+"]&["+Format(now(), "M")
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
But to no avail.
View 4 Replies
View Related
May 28, 2015
[DIMCustomerBuyer].[CustId].[CustId] is working.
[DIMCustomerBuyer].[CustId].
All members is not working any hint why this is not working.
View 2 Replies
View Related
Nov 21, 2008
Uknown Member is set to "Uknown" in the cube . This cannot be changed because it needs to be visible in other applications. However, in my particular MDX I want to create a custom set that excludes this member.
WITHSET setNoUnknowns as EXCLUDE( [Dim].[Hierarchy].CHILDREN, { [Dim].[Hierarchy].UNKNOWNMEMBER } )SELECT{[Measure].[MeasureName]} on COLUMNS,setNoUnknowns on ROWSFROM [Cube]
With the above MDX, I still get a return for member "Unknown". I've confirmed that there isn't an explicit member name of "Unknown".Excluding [Dim].[Hierarchy].[Unknown] does not work either.
View 5 Replies
View Related
Aug 23, 2015
I’m trying to build a calculated member (see below script) using “Except” function but I get an error result:
#Error The function expects a string or numeric expression for the argument. A tuple set expression was used.
My idea is to take a measure and exclude 2 members from the dimension.
I tried using “Aggregate” but got the error:
#Error Query (3, 1) Aggregate functions cannot be used on calculated members in the Measures dimension.
Please note that my measure is ACD that is already calculated average in olap and I can’t use AVG function instead of Aggregate. What can I do?
With
member [Measures].[AppOrig All Roaming]
as
(
Except(
[Source IP Location].[Country Name].[All].children,{
[Source IP Location].[Country Name].&[Colombia]
[Code] ....
View 6 Replies
View Related
May 16, 2008
Hi,
I need to calculate the average of an existing measure of my cube.
The measure is PA_Salaire from a column of my fact table.
I would like to calculate an average i could use with all the dimensions of my cube but i think i can only create one linked to a single dimension. Is that true ?
I dont really understand the way i should write my request. Here is what i did:
(Manager is one of my dimensions, it has a parent child relation... is that a problem? does it change the way i should do?)
Avg(Descendants([Manager].CurrentMember, [Manager].[Name]), [Measures].[PA Salaire]
But that doesnt work at all i get the following result: " #VALUE! " for every manager
Thx for your help
Francois
View 5 Replies
View Related
May 27, 2015
I have a requirement to show the parent value which is passed and also all the leaf member details in MDX. For example in Adventure works, if I pass Bikes as the value in MDX, the results should display Bikes along with All the child member names as a measure value in SSAS MDX. I only have one hierarchy with Product Category-> Subcategory-> Product.
I am trying to use the following MDX but I am getting all the members. I need to get only Bikes in my report.
WITH MEMBER [Prod_Passed] AS SETTOSTR(AXIS(1))
MEMBER [Prod_Child] AS DESCENDANTS ([Dim Product].[Hierarchy].CURRENTMEMBER,,LEAVES).item(0).NAME
MEMBER [Child_Value] AS ([Dim Product].[Hierarchy].CURRENTMEMBER,[Measures].[Sales Amount])
SELECT [Dim Product].[Hierarchy].[Product Category].&[1] * [Dim Product].[Product Key].[Product Key].MEMBERS ON ROWS,
{[Measures].[Sales Amount],[Prod_Passed],[Prod_Child],[Child_Value]} ON COLUMNS FROM
[AdvWorks]
View 10 Replies
View Related
Jul 20, 2015
I have an issue which I cannot seem to resolve, what I need to do is to create a new member in an existing dimension which lets me filter the results of the last two weeks (this should start from last week, so ignoring the current week). I've tried a few approaches which seemed to have worked when browsing via excel, but when I tried using this in PowerView things were not right. I need a graph which shows the day on the Axis and then a single measure, with the filter being set to the last two weeks.
In management studio I got the following to work and show me the week, date and the measure:
select
(
LastPeriods(2,[Date].[Week Calendar].[Year].&[2015].&[30]&[2015].lag(1))
,[Date].[Calendar].[Date].allmembers
) on rows,
[Measures].[On Schedule %] on columns
from [Flight Movement]
where {([Flight Leg].[Direction].&[Arrival]),([Flight Leg].[Direction].&[Departure])}
This filters the results and only shows the relevant weeks and dates. When I try to put the LastPeriods logic into a new member, it returns all the dates in the dimension. This is what I am trying:
with member [Date Calculations].[Date Calculations].[Test]
AS
(LastPeriods(2,[Date].[Week Calendar].[Year].&[2015].&[30]&[2015].lag(1)))
select
(
[Date Calculations].[Date Calculations].[Test],
[Date].[Calendar].[Date].allmembers
) on rows,
[Measures].[On Schedule %] on columns
from [Flight Movement]
where {([Flight Leg].[Direction].&[Arrival]),([Flight Leg].[Direction].&[Departure])}
View 8 Replies
View Related
Oct 28, 2015
Is there any option in SSAS cube to hide the member properties from the user by default in the cube itself. We know we have a option in the excel pivot 'Show properties in tooltips check box where we can hide or display the member properties. But I need an option in the cube to hide the member properties by default and display in the excel depends on demand.
View 2 Replies
View Related
May 12, 2015
I am setting up some security requirements - the requirement is that team leaders get to view all teams within their division. What I would like it to do when they log onto their dashboard/ssrs report is that it defaults to the team leaders default team (they can still select from other teams if they require).If I can avoid I don't really want another parameter in the report for this.
In my cube I have an attribute under the people dimension (where teams and division also reside) called Fee_Earner_Effective_Flag and where this is set to Y this is their current team.
Under the Role properties security under the Fee_Earner_Effective_Flag attribute I have set the Default member to
[People Primary FeeEarner].[Fee_Earner_Effective_Flag].[Fee Earner Effective Flag].&[Y]
When I then logon as the user and try to access the cube I get the following
Errors in the metadata manager. The '[People Primary Feeearner].[Fee_Earner_Effective_Flag].[Y]' security default member from the '{' attribute returns a result from a different hierarchy.
I have also tried setting the default member in the cube solution and then processing but then the cube failed.
View 3 Replies
View Related
Jun 3, 2015
In one of the business requirement, we need to disable a particular drill down on a calculated member, Is it possible?
View 3 Replies
View Related
Feb 1, 2006
Hello,
I have a calculated member that composed of dividing two other calculated members. The calculation is incorrect and is ignoring the decimal places in both of the source measures of the equation.
Example:
Calculated Member 1 = 300.01
Calculated Member 2 = 10.25
Calculated Member 3 = (CM1 / CM2 ) = 30 INCORRECT!!!
Answer should be = 29.269
The MDX that I am using is as follows:
Calculated Member 1 (Booked Amount YTD):
Sum(PeriodsToDate([Fiscal].[Date].[Fiscal Year]),[OH Booked Amount])
Calculated Member 2 (Units YTD)
Sum(PeriodsToDate([Fiscal].[Date].[Fiscal Year]),[OH Units])
Calculated Member 3 (Booked Amount / Unit)
Measures.[Booked Amount YTD] / Measures.[Units YTD]
This seems like a fairly common and straight forward type of calculation, taking the YTD amount / YTD units to come up with YTD amount per unit. When I create a calculated member with the hardcoded values of 30.01 / 10.25 I get the correct answer of 29.269. Am I missing something with formatting? Is this a bug in Analysis Services?
Any help would be appreciated.
Thanks in advance.
Brian
View 1 Replies
View Related
Oct 27, 2015
I cannot understand why Total of calculated member is displayed incorrect. How should I change calculated member for it to work correctly?
Calculated Member:
CREATE MEMBER CURRENTCUBE.[Measures].AverageScore
AS IIF([Measures].[Distance]<2001,0,[Measures].[avgscore]/[Measures].[Date Count]),
VISIBLE = 1;
It seems that Total is calculated without checking value in "AverageScore" for that month.
View 3 Replies
View Related
Jun 2, 2015
I am trying to create a calculated member for Cumulative sum using Fiscal Calendar using the below code:
WITH MEMBER [Measures].[No of Accounts Cumulative]
AS 'Sum(PeriodsToDate([DimFinancialDate].[FiscalMonth].[(all)],[DimFinancialDate].[FiscalMonth].CURRENTMEMBER), [Measures].[No of Accounts])'
SELECT
{[Measures].[No of Accounts],[Measures].[No of Accounts Cumulative]} ON COLUMNS,
[DimFinancialDate].[FiscalMonth].Members ON ROWS
FROM [Acquisition]
where [DimFinancialDate].[Year - Quarter - Month - Date].[FiscalYear].&[2014-04-01T00:00:00];
Getting below OUTPUT:
No of Accounts No of Accounts Cumulative
All 1763 1763
Apr 14 116 353
May 14 30 383
Jun 14 284 667
Jul 14 112 779
Aug 14 38 817
[code]....
Basically, It is taking all the members of the Fiscal date dimension to compute the output, But what i am expecting is to compute only for Fiscal calendar that is from April 2014 - March 2015.
View 2 Replies
View Related
Sep 2, 2015
getting 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?
View 4 Replies
View Related
