Matrix SSAS Dimension/hierarchy Order Problem
Oct 4, 2006
I have a have a relatively simple SSAS cube that I'm trying to display in a matrix but the order of the data is not correct. The rows of the matrix are a time hierarchy and the query designer produces the correct MDX and when I run the MDX in the data tab, the order is correct. However in the report preview tab, the order is incorrect. Specifically, the time hierarchy looks like this Year-Month-FullDate. The report is ordering the Months like this 1,10,11,12,2,3,4,5,6,7,8,9. I ran the MDX query in Sql Studio and the order is correct, and I used Excel to consume the same cube and it produces the correct order.
The report is somehow treating the Month like character data instead of numeric, how do I fix this?
View 4 Replies
ADVERTISEMENT
Jul 22, 2015
For Example: I have one dimension named as "Name", Under this I have "FirstName" and "LastName" Attributes are there.But when i drag "Name" dimension, By default "First Name" dragged. But i Want "Last Name" should drag.
View 6 Replies
View Related
Jul 22, 2015
I have a dimension like Districts, Under that 2 Attributes are there i.e,District ID and Districts. When i drag Dimension "Districts", in OLAP grid it come District ID first. But i want Districts to drag first. How can we sort Attributes(District ID and Districts) for a dimension.
View 6 Replies
View Related
Sep 6, 2007
Hello experts,
Last time I worked with SSAS and build a Cube. Because I€™m now very happy with the front-end excel 2003 or excel 2007 I thought I build my own Report with SSRS.
Now there is something I don€™t understand:
I build a Hierarchy in SSAS that a want to use in SSRS. Is there a chance to use it without any features? Have I to use parameters or something another?
In my opinion it makes any sense to build a hierarchy new, because it€™s already exists in SSAS.
Have a nice day
Alex
View 5 Replies
View Related
Jul 20, 2005
I am trying to use MDX to dynamically determine which hierarchy of thetime dimension is selected. I have a calculated member which is usingthe last non-empty descendant of the [Time].[Calendar] dimension, butI want it to work even if Fiscal is selected instead of Calendar.Here is the codeSUM(Tail(Filter(Descendants([Time].[Calendar].CurrentMember, [CalendarDate]), isEmpty([Measures].[Employee Count]) = False), 1),[Measures].[Employee Count])What can I put in place of [Time].[Calendar].CurrentMember to take thecurrent hierarchy of the time dimension instead of the Calendarhierarchy? Thanks!
View 1 Replies
View Related
Aug 8, 2007
I am in the process of develping a MSRS report using an MSAS 2005 OLAP cube as my data source. In the MSRS Query Builder, I am using an MDX query which successfully executes in Management Studio. Something like the following:
SELECT
NON EMPTY { [Measures].[Fact Count] }
ON COLUMNS,
NON EMPTY{ ( [Dim Attribute].[Hierarchy Not Visible].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
ON ROWS
FROM [MyCube]
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
The twist is that the AttributeHierarchyVisible property of the [Dim Attribute].[Hierarchy Not Visible] is set to False.
As mentioned previously, the query successfully executes in Management Studio. However when it is executed in the MSRS Query Builder, the following error message is displayed:
The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension..
Parameter name: mdx (MDXQueryGenerator)
Is there a way to successfully query dimension attributes whose hierarchies are not visible?
Thanks.
View 2 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
Jul 27, 2007
I'm loading a fact table that has several geographic attributes - some are at the state level, some are at the county level, and then some are drilled farther in that that. I understand the basic concept of the dimension with the ragged hierarchy, but unsure of how to load to the fact table using lookups based on these geographic units. For example, if my geographic dimension contains 200 records for the state of Wyoming, basically a record for each fine-grain place (i.e. city/town), then how do I go about doing a county lookup. Wyoming only has 23 counties, but because of the repetitive nature of the dimension attributes that are not at the finest grain, I'll get more records in the lookup than I need. This activity repeats of course while I move up the geographic scale to state, then country. How do I configure/fill my dimension to handle these differing scales of data?
View 2 Replies
View Related
May 4, 2007
Hi everybody,
I would like to know how to create a hierarchy in a SSAS project using a SSRS datasource?
All values are displayed when I drag the field "parent" in the MDX generator...
Thanks in advance for any idea...
View 1 Replies
View Related
Jun 8, 2015
I have a time dimension which has Date, Week, Month and Year. However, the hierarchy will have only Week, Month and Year. It works great for any Sales measure with AggregateFunction as SUM.
I have created a new measure with AggregateFunction = LastNonEmpty. Also in the backend, I have pushed all the inventory data to last date in every month as inventory is always looked on a monthly basis not on a weekly basis. This measure shows correct data for every last week of the month in the hierarchy. However, Months and Years are displayed as zeros.
what I am doing wrong.
View 8 Replies
View Related
May 19, 2008
Hello,
I've run into a problem building a new olap cube. It's taking 15 minutes or more when pulling on a dimension before I've even pulled on any measures. This happens in Excel as well as Management Studio. When it does display it seems to only show dimension members that it has data for. In addition, running profiler seems to indicate that it queries each of my partitions when it's doing this. I know that in my last cube if you pulled on a dimension, no members would show up yet and it would be fast. However I can't seem to find the property that's telling it not to query the partitions to determine what members to show. Any ideas?
~Grant
View 6 Replies
View Related
Jul 15, 2015
I have a disconnected utility dimension with the following data and a waterfall->subwaterfall hierarchy. I have ignore if same as parent set on sub water fall. The unary operator is assigned to the relevant level
WaterFall WaterFallUnary SubWaterFall SubWaterFallUnary
Opening ~ Opening ~
Price ~ Price ~
Total Composition ~ Composition L1 +
Total Composition ~ Composition L2 +
Total Composition ~ Composition L3 +
Total Composition ~ Composition L4 +
Total Composition ~ Composition L5 +
Closing ~ Closing ~
I'm using MDX to set values to the members
SCOPE(Measures.Waterfall,[Dim Waterfall].[Water Fall].&[Price]);
THIS=Measures.LowestPrice;
END SCOPE;
SCOPE(Measures.Waterfall,[Dim Waterfall].[Sub Water Fall].&[Composition L1]);
THIS=Measures.[Composition L1];
END SCOPE;
[Code] ....
But when i run a simple select query
SELECT
{[Measures].[Waterfall]} ON COLUMNS,
[Dim Waterfall].[Water Fall].[Water Fall] ON ROWS
FROM [GPA]
I only get a value for price. Total Composition is NULL. If I change my query to look at the subwaterfall level then I see values for composition L1->L5, so I know there are values there I was expecting the unary operator to aggregate my composition measures into Total Composition.
I should point out that every measure that's being assigned to my utility dim is calculated with in excess of a dozen or so steps and intermediate calcs, is this causing some kind of solve order issue?
I know that as a workaround i could probably do something like
SCOPE(Measures.Waterfall,[Dim Waterfall].[Water Fall].&[Total Composition]);Â Â Â Â
   THIS=SUM([Dim Waterfall].[Hierarchy].[Water Fall].&[Total Composition].Children,Measures.Waterfall);   Â
END SCOPE;
But that defeats the purpose of the unary operators
This is sql server 2014 multidimensional
View 5 Replies
View Related
Apr 8, 2015
Include children and exclude children in a single hierarchy in parent child dimension in mdx
*12-parent
 **20-parent
 - 9-parent
      --250-child1
      --210-child2
    --240-child3
aggregation of 12-parent only
aggregation of 20-parent only
aggregation of 9 with children
View 3 Replies
View Related
Apr 30, 2014
If you've ever worked with CI (UPC barcodes), certain commercially distributed products can be "packaged" in certain ways as defined by suppliers. For example, buying a six-pack of Coca-Cola, each one of the cans have a barcode UPC that indicates that the particular can is to only be distributed as part of a "pack" - they can't be distributed or sold individually.
Certain products can be distributed and sold in more than one way. For example, that same six pack can be distributed and sold as a case bundle, or it can be distributed as a single six pack. Each packaging method has a certain hierarchy.you can't represent the CI model using anything less than an adjacency matrix.Now, this can be represented in SQL pretty easily - there's tons of examples online that go through the basics of how to do it and how to write the recursive CTE function to represent and return data in this format. My problem is with MDS.
In Master Data Services (MDS) you have several hierarchy types to choose from. The hierarchy type that makes the most sense to me is to use the recursive derived hierarchy. It makes sense because I want to define an entity that contains all the "acceptable" adjacencies for particular items, such that when a user looks at the data in MDS or tries to update its value, it will have certain domain-based enforcement (and thusly lookups).I think that I've crossed a bridge too far with MDS - but, perhaps one of you knows something I don't know. It looks like any hierarchy in MDS is only based in a parent - child relationship, so that's not quite what the doctor ordered here.
View 0 Replies
View Related
Sep 10, 2015
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.
View 6 Replies
View Related
Dec 15, 2007
I am trying to perform an incremental (ProcessAdd) load on a dimension using SSIS and the Dimension Processing data flow component. Whenever I try the load it fails with the following error:
The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4
OnError,*******,Package,{62F748F0-EFC6-4D7A-9873-B31144C54873},{6AA40F9D-7E89-4D68-B2CD-31A43EA6B80D},12/14/2007 7:44:44 PM,12/14/2007 7:44:44 PM,-1055719414,0x,Parser: An error occurred during pipeline processing.
OnError,*******,Package,{62F748F0-EFC6-4D7A-9873-B31144C54873},{6AA40F9D-7E89-4D68-B2CD-31A43EA6B80D},12/14/2007 7:44:44 PM,12/14/2007 7:44:44 PM,-1055719414,0x,Internal error: The operation terminated unsuccessfully.
OnError,*******,Package,{62F748F0-EFC6-4D7A-9873-B31144C54873},{6AA40F9D-7E89-4D68-B2CD-31A43EA6B80D},12/14/2007 7:44:44 PM,12/14/2007 7:44:44 PM,-1055719414,0x,Errors in the OLAP storage engine: An error occurred while the 'ORDER LINE STATUS' attribute of the 'DIM ORDER LINE' dimension from the 'ACRDvel' database was being processed.
OnError,*******,Package,{62F748F0-EFC6-4D7A-9873-B31144C54873},{6AA40F9D-7E89-4D68-B2CD-31A43EA6B80D},12/14/2007 7:44:44 PM,12/14/2007 7:44:44 PM,-1055719414,0x,Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Q DIM ORDER LINE', Name of 'DIM ORDER LINE' was being processed.
OnError,*******,Package,{62F748F0-EFC6-4D7A-9873-B31144C54873},{6AA40F9D-7E89-4D68-B2CD-31A43EA6B80D},12/14/2007 7:44:44 PM,12/14/2007 7:44:44 PM,-1055719414,0x,Errors in the high-level relational engine. The data source view does not contain a definition for the 'Q_DIM_ORDER_LINE' table or view. The Source property may not have been set.
OnError,*******,Package,{62F748F0-EFC6-4D7A-9873-B31144C54873},{6AA40F9D-7E89-4D68-B2CD-31A43EA6B80D},12/14/2007 7:44:44 PM,12/14/2007 7:44:44 PM,-1055719414,0x,Internal error: The operation terminated unsuccessfully.
OnError,*******,Package,{62F748F0-EFC6-4D7A-9873-B31144C54873},{6AA40F9D-7E89-4D68-B2CD-31A43EA6B80D},12/14/2007 7:44:44 PM,12/14/2007 7:44:44 PM,-1073450974,0x,SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Dimension Processing" (112) failed with error code 0x80004005. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
OnError,*******,Package,{62F748F0-EFC6-4D7A-9873-B31144C54873},{6AA40F9D-7E89-4D68-B2CD-31A43EA6B80D},12/14/2007 7:44:44 PM,12/14/2007 7:44:44 PM,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0x80004005. There may be error messages posted before this with more information on why the thread has exited.
OnError,*******,Package,{62F748F0-EFC6-4D7A-9873-B31144C54873},{6AA40F9D-7E89-4D68-B2CD-31A43EA6B80D},12/14/2007 7:44:46 PM,12/14/2007 7:44:46 PM,-1071636284,0x,The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
OnError,*******,Package,{62F748F0-EFC6-4D7A-9873-B31144C54873},{6AA40F9D-7E89-4D68-B2CD-31A43EA6B80D},12/14/2007 7:44:46 PM,12/14/2007 7:44:46 PM,-1073450952,0x,SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
OnError,*******,Package,{62F748F0-EFC6-4D7A-9873-B31144C54873},{6AA40F9D-7E89-4D68-B2CD-31A43EA6B80D},12/14/2007 7:44:46 PM,12/14/2007 7:44:46 PM,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
OnInformation,*******,Package,{62F748F0-EFC6-4D7A-9873-B31144C54873},{6AA40F9D-7E89-4D68-B2CD-31A43EA6B80D},12/14/2007 7:44:46 PM,12/14/2007 7:44:46 PM,1074016264,0x,Post Execute phase is beginning.
OnInformation,*******,Package,{62F748F0-EFC6-4D7A-9873-B31144C54873},{6AA40F9D-7E89-4D68-B2CD-31A43EA6B80D},12/14/2007 7:44:46 PM,12/14/2007 7:44:46 PM,1074016265,0x,Cleanup phase is beginning.
OnInformation,*******,Package,{62F748F0-EFC6-4D7A-9873-B31144C54873},{6AA40F9D-7E89-4D68-B2CD-31A43EA6B80D},12/14/2007 7:44:46 PM,12/14/2007 7:44:46 PM,1074016267,0x,"component "Dimension Processing" (112)" wrote 756860 rows.
Apparently this translates to the error DTS_E_ADDROWTOBUFFERFAILED.
This incremental load is trying to add several hundred thousand rows and it appears to get to around 10K or so and then fail. I can't seem to find any KB articles related to this problem. Does anyone have a clue what may be happening here?
I do have an incremental load of the same type that is working but I am continually getting the following warning when the load is running:
"The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 6 buffers were considered and 6 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked."
My server has 8GB of memory and is running WS 2003 x64 and SSAS 2005 x64
TIA
View 4 Replies
View Related
Jun 30, 2015
I have a cube with a fact table and 3 dimensions. Â One of the dimensions is a type 2 and surrogate key is stored in fact table. Â If i query the database, the dimension attributes display correctly, however the cube is always displaying the latest dimension attribute and not preserving the history. Â
The measures are correct for the time period displayed, but the dimension attributes always show the latest values.
View 3 Replies
View Related
Apr 21, 2012
I have 5 cubes, and hierachy defined for all cubes. for example:geography database with 5 continents as cubes and contries as dimensions.Now when i am doing security restrictions on my dimension ex: In USA dimension if i want only to give access to texas region then i should be able to see only texas cities. But i cansee all the states under USA even after selecting only Texas region under Dimension data tab inside ROles section in SSMS.I have tried security at database ,cube level as well as dimension level.But still not working.is that because of some wrong design of cubes or something related to database design.? I am not able to undersand that except roles everything in my cubes or datawarehouse is working fine without and defect in data.
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
Jul 15, 2015
In my SSAS Cube I have created a dynamic named set "top 10 e-learnings by language" which consists out of a set of tuples. Each tuple has two attributes out of the same base dimension "training": attribute 1 is "sprache" (language) and attribute 2 is "training text".
CREATE DYNAMIC SET CURRENTCUBE.[Top 10 eTrainings pro Sprache]
AS Generate(
{ [Training].[Sprache].[Sprache].Members },
TopCount(
EXISTING { [Training].[Sprache].CurrentMember * [Training].[Training Text].[Training Text].Members },
10,
[Measures].[Teilnahmen eTraining]
)
), DISPLAY_FOLDER = 'Training' ;
Normally a named set would be automatically visible in Excel Pivot under the dimension you used to create the named set, but it seems that named sets with tuples which have more than one attribute are placed in a separate folder "Sets"Â in between the measures and dimensions.Additionally in the SSAS cube browser this named set is not visible at all.Is there any way to tell the named set in which dimension it should appear or any workaround?
View 2 Replies
View Related
Aug 25, 2015
I 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 ??
View 2 Replies
View Related
Apr 22, 2015
I create a Dimension Date using SSAS 2008 but when i execute the dimension and i go to see the result i have this result:the result is not sorted..what i need is having the result order by year i mean i have Calendrier 2020,Calendrier 2019 ...
View 6 Replies
View Related
May 8, 2015
I'm facing an issue while processing OLAP. I have enabled BitLocker for dirve encryption and OLAP services uses this drive for db storage. OLAP is executing though SSIS package and I'm getting below error in Package. When debugging the script, it says Drive is encrypted using BitLocker.Â
My client requires TDE for all databases, for OLAP we decided to use BitLocker: [URL] ....
SQL server is installed on C Drive & D Drive is the storage location for OLAP DB. When locking D Drive, OLAP processing failed. When I tried to restart SQL Server Analysis service in Services.msc it is not starting. Service restarted only when D Drive is unlocked. Is there any way we can process OLAP even when the drive is locked?
Error message is given below:
"The following system error occurred: Â This drive is locked by BitLocker Drive Encryption. You must unlock this drive from Control Panel. "
View 3 Replies
View Related
Jun 2, 2015
I have a dimension report with an attribute reporttype which have different member let say A,B,C,D....etc.
I want to set more than one default members for dimension report.
I've read all about how dimensions can have only one default member, but I need to set more than 1.
View 2 Replies
View Related
Mar 19, 2012
I am developing a BI solution on SQL Server 2008 R2 and how to handle multiple referances to the same dimension from a fact table!
Here is the scenario;
Fact_Contracts (# M)
ServiceProvider_CompanyID, Client_CompanyID, Amount_USDÂ
Dim_Company( hundreds)
ID, CityID, ProfessionID, CompanyName
Dim_City
ID, CityName
Dim_Profession
 ID, ProfessionName
As u can see there is two company references in my fact table, and the schema is in snowflake. My customer requirements state that the Contracts' amounts can be aggregated/filtered for/by, ServiceProviderCompany, its city/profession or ClientCompay, its city/profession.
First thing came in to my mind is to dublicate whole dimension structure (one for serviceproviders, one for clients), which i thought that there should be another way around?
View 5 Replies
View Related
Aug 3, 2015
I 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.
View 0 Replies
View Related
Jul 24, 2015
I have an SSIS and SSAS project in the same solution. Â I need to debug the SSIS package regardless if there is an error or two in the SSAS project. Â Is there a way to ignore the SSAS project while I debug the SSIS package?
View 2 Replies
View Related
Apr 10, 2006
When I created a Table control, I was able to read things like .Color, FormattedValue, etc. from SSAS. This was great, because I could control all existing and future reports' formatting via one change in the cube.
Now, I've decided that a Matrix control is superior for various reasons. However, I notice that I MUST apply an aggregate function to get a cell value...well, it appears that this means there's no longer any way for me to read my SSAS formatting.
I tried =First(Fields!DataValue.FormattedValue), =Max(...), etc.
I also tried it with the color property, etc. Nothing worked.
Are there any workarounds? I'd hate to embed the formatting into each of my 40 reports, because the user is likely to change his/her mind after the fact.
Thank you,
Bert
View 1 Replies
View Related
Jun 15, 2015
I am trying to implement data masking based on user login and not sure why this is not working. I have the dimensions DimBrand, DimProduct and DimUser. I should mask the BrandCode with 'XXXX' nothing but in the report all the BrandCode should appear but few of the code will be masked if the user is not belongs to that group. I have a fact table FactProduct in this. In the cube I created all these 3 dimensions and the fact table. I created a new dimension DimBrandMask and I separated the code over there with a relationship with the actual DimBrand dimension. In the cube a reference relationship is set up with the measure group. Created a role with read access.
In the dimension data tab of role I put the below MDX to allowed set.
NonEmpty([DimBrandMask].[Brand Code].Members, (StrToMember("[DimUser].[Login Name].[Login Name].[" + UserName() + "]") ,[Measures].[Dim User Count]))
And in Denied Member set i put the below MDX
IIF( (StrToMember("[DimUser].[Login Name].[Login Name].[" + UserName() + "]"), [DimUser].[Access Right].&[False]), NONEMPTY( [DimBrandMask].[Brand Code].Members,(StrToMember("[DimUser].[Login Name].[Login Name].["
+ UserName() + "]"), [DimUser].[Access Right].&[False], [Measures].[Dim User Count])),{})
Note I created one measure group from the DimUser table and the measure [Dim User Count] is used in the above query.
I am expecting some result like below
Brand     BrandCode          Count
Brand1Â Â Â Â Â b1 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 6
Brand2 Â Â Â XXXXX Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 5
Brand3 Â Â Â XXXXX Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 10
View 9 Replies
View Related
Aug 11, 2015
I am working on a model where I have a sales fact table. Each fact record has four different customer fields (ship- to, sold-to, payer, and bill-to customer). I have one customer dimension table that joins to the sales fact table four times (once for each of the customer fields above). When viewing the data in Excel, I would like to have four hierarchies (ship -to, sold-to, payer, and bill-to customer) within Customer.Â
Is there a way to build hierarchies within my Customer dimension based on the same Customer table? What I want is to view the data in Excel and see the Customer dimension. Within Customer, I want four hierarchies.Â
View 2 Replies
View Related
May 22, 2007
Hello!
I'm developing a report with Samsung products sales compared to other producers' sales.
I have the following dimensions:
Dim Period (date-time)
Dim Region (region where the products are sold)
Dim Product Group (notebooks, CRT,...)
Dim Producer (Samsung, LG, ..., Other producers)
Report should contain Samsung sales on the first row, other companies (LG, Dell,...) sales on subsequent rows and "Other producers" sales on the last row, i.e.:
Week 1 - SomeRegion - Notebooks - Samsung - 2350
Week 1 - SomeRegion - Notebooks - LG - 1100
Week 1 - SomeRegion - Notebooks - Dell - 3000
Week 1 - SomeRegion - Notebooks - Other Producers - 10000
How can I position Producer values in such way?
Please note, Dim Producers may be enalrged, so I can't just enumerate all Dim Producer members in the query.
I tried to use field ProducerType, which I set to 1 for Samsung, 2 for other companies (LG, Dell, ...) and 3 for "Other producers", after that I ordered by this field, however it didn't position producers as I expected.
Please see the query I used below:
Code Snippet
SELECT
NON EMPTY
{
[Measures].[Quantity]
} ON COLUMNS,
NON EMPTY
Order(
{
(
[Dim Period].[Period Week No].[Period Week No].ALLMEMBERS *
[Dim Region].[Region Name].[Region Name].ALLMEMBERS *
[Dim Product Group].[Product Group Name].[Product Group Name].ALLMEMBERS *
[Dim Producer].[Producer Name].[Producer Name].ALLMEMBERS
)
}, [Dim Producer].[Producer Type], ASC)
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [ShelfShare]
WHERE ( [Dim Period].[Period Year].&[2007] )
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Any help is greatly appreciated!
View 3 Replies
View Related
Oct 27, 2007
Good day community. I have a question about SQL Server 2005 Anywhere edition which i currently installed on my desktop. Does it fit my needs which stated on the subject? If no, what should i select for my projects?
View 1 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
