Third Dimension, Or Something Instead?
Jan 29, 2008I have table with many rows, and I want to change the structure of base. It is possible to create 3-dimensions table?
View 14 Replies
ADVERTISEMENT
Analysis :: Add Dimension To Cube Dimension Without Any Relation In Dimension Usage
Oct 26, 2015When 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 RelatedFiltering Out 1 Time Dimension Based On The Selection In Another Time Dimension
May 19, 2008Hi!
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
Dimension
Apr 13, 2005I ask the question again more clear!
I need to have the same calculeted member for diferentes dimensions.. can i do one calculated member only, that include the diferent dimensions.
Thanks
Paul
[Dimension].[All Hierarchies].[All] ?
Feb 6, 2005As promised, question #2... ;)
As I mentioned in my previous post, my "real" case is a cube with 79 dimensions, most of which virtual, have been added for convenience.
Think for instance about a time dimension... Wouldn't it be nice to get a matrix with years horizontally, months vertically and displaying say the number of order you had for each cell in the resulting grid.
Ok, maybe you can do this with MDX but not in Excel, unless you create virtual dimensions for the Year and Month levels.
That's all good, but as it is, in my real case, I end up with four date dimensions for which I have to provide:
YQMD (Year, Quarter, Month & Day) hirarchized dimension
YWD (Year, Week, Day)
YQMD (Fiscal calendar)
Year
Quarter
Month
Week
Day (as 1, .. 31)
Week Day (for periodicity analysis over a week's time)
Date (as individual day - this is the backbone for the virtual dimensions)
It turns out this makes 40 dimensions by itself. For the sake of it, I grouped them by 4 hierarchies, although I've seen no specific functionality off of this in the data browser or Excel, so it really seems to be only for "show".
Now in my previous post I explained how I "spread" my session count to calculate a conversion rate. Given the number of dimensions I have (very high segmentation at the order level, very limited segmentation at the session/visit level), this means my calculated cell formula looks like this (hold your breath, it's ugly):
SubCube: {[Measures].[Session Count]}, DESCENDANTS([Business Unit].[All]), DESCENDANTS([Channel Source].[All]), DESCENDANTS([Currency].[All]), DESCENDANTS([FO Product Segment].[All]), DESCENDANTS([FO Quarter].[All]), DESCENDANTS([FO Value Segment].[All]), DESCENDANTS([Is Cancelled].[All]), DESCENDANTS([Is Customer Care].[All]), DESCENDANTS([Is Design Services].[All]), DESCENDANTS([Is Direct].[All]), DESCENDANTS([Is Estimated].[All]), DESCENDANTS([Is Holiday Order].[All]), DESCENDANTS([Is New].[All]), DESCENDANTS([Is Sales Services].[All]), DESCENDANTS([Language].[All]), DESCENDANTS([Payment].[All]), DESCENDANTS([Product Segment].[All]), DESCENDANTS([Value Segment].[All]), DESCENDANTS([FO Channel].[All]), DESCENDANTS([Reg Channel].[All]), DESCENDANTS([FCD].[Date].[All]), DESCENDANTS([FCD].[Day].[All]), DESCENDANTS([FCD].[Mth].[All]), DESCENDANTS([FCD].[Qtr].[All]), DESCENDANTS([FCD].[Wk].[All]), DESCENDANTS([FCD].[WkDay].[All]), DESCENDANTS([FCD].[Year].[All]), DESCENDANTS([FCD].[Fiscal].[All]), DESCENDANTS([FCD].[YMQD].[All]), DESCENDANTS([FCD].[YWD].[All]), DESCENDANTS([FRD].[Date].[All]), DESCENDANTS([FRD].[Day].[All]), DESCENDANTS([FRD].[Mth].[All]), DESCENDANTS([FRD].[Qtr].[All]), DESCENDANTS([FRD].[Wk].[All]), DESCENDANTS([FRD].[WkDay].[All]), DESCENDANTS([FRD].[Year].[All]), DESCENDANTS([FRD].[Fiscal].[All]), DESCENDANTS([FRD].[YQMD].[All]), DESCENDANTS([FRD].[YWD].[All]), DESCENDANTS([FSD].[Date].[All]), DESCENDANTS([FSD].[Day].[All]), DESCENDANTS([FSD].[Mth].[All]), DESCENDANTS([FSD].[Qtr].[All]), DESCENDANTS([FSD].[Wk].[All]), DESCENDANTS([FSD].[WkDay].[All]), DESCENDANTS([FSD].[Year].[All]), DESCENDANTS([FSD].[Fiscal].[All]), DESCENDANTS([FSD].[YQMD].[All]), DESCENDANTS([FSD].[YWD].[All])
Caculation Value: CalculationPassValue(([Business Unit].[All],[Channel Source].[All],[Currency].[All],[FO Product Segment].[All],[FO Quarter].[All],[FO Value Segment].[All],[Is Cancelled].[All],[Is Customer Care].[All],[Is Design Services].[All],[Is Direct].[All],[Is Estimated].[All],[Is Holiday Order].[All],[Is New].[All],[Is Sales Services].[All],[Language].[All],[Payment].[All],[Product Segment].[All],[Value Segment].[All],[FO Channel].[All],[Reg Channel].[All],[FCD].[Date].[All],[FCD].[Day].[All],[FCD].[Mth].[All],[FCD].[Qtr].[All],[FCD].[Wk].[All],[FCD].[WkDay].[All],[FCD].[Year].[All],[FCD].[Fiscal].[All],[FCD].[YMQD].[All],[FCD].[YWD].[All],[FRD].[Date].[All],[FRD].[Day].[All],[FRD].[Mth].[All],[FRD].[Qtr].[All],[FRD].[Wk].[All],[FRD].[WkDay].[All],[FRD].[Year].[All],[FRD].[Fiscal].[All],[FRD].[YQMD].[All],[FRD].[YWD].[All],[FSD].[Date].[All],[FSD].[Day].[All],[FSD].[Mth].[All],[FSD].[Qtr].[All],[FSD].[Wk].[All],[FSD].[WkDay].[All],[FSD].[Year].[All],[FSD].[Fiscal].[All],[FSD].[YQMD].[All],[FSD].[YWD].[All],[Measures].[Session Count]), 0)
If you read all this, you can see already the cryptic dimension names like "FRD", "FSD" and so forth... that's because with the real names ("First Refund Date", "First Ship Date", the query processor errored out... visibly there is a limit in the size of the formulas you can post!
Is there no other way to achieve this result? Basically I mean to say: if the session count is not defined at your level along this dimension, go to the root of the dimension to get the value there, this along a slew of dimensions, many of which are inherently dependent because of the usage of virtual dimensions (therefore if I wish to go to the root of my "First Refund Date" for instance, I wish to do so along all sub-dimensions... Heck, as far as I'm concerned this is conceptually only ONE dimension, just with various views upon it...
Using hierachies I was sort of hoping for the ability to have something like:
[FRD].[All Hierachies].[All]
Am I just asking for too much or do I just not know (quite probable) the magic keyword that can do this?
Where this is becoming quite critical is that I actually have a calculated cell that goes as follows (abridged):
SubCube: similar as before
Calculation: [Measures].[Order Count]*CalculationPassValue((...set of all un-tied dimension roots..., [Measures].[Distributed Marketing Cost])/CalculationPassValue((...set of all un-tied dimension roots..., [Measures].[Order Count])
Now the purpose of this is to distribute external costs at the order level. In short, say that you know you spent $10,000 globally promoting a specific group of websites in commission money (you pay $1 for each order). This formula allows me to get that a specific website, with 20 orders incurred an additional marketing cost of $20. That's actually the object of my next thread's question (spreading a multiplication through the aggregations)
For the purpose of this thread I am just concerned about the size of my formulas. Renaming the dimensions seemed to "buy" me some margin and I was actually quite surprised to find that the formula still fitted in, and works, but it is only a matter of time until I have to add more dimensions and the whole thing blows up in my face. Adittionally this is obviously not pleasant to look at and maintain.
Any better solution?
Thanks,
Seb
Can Not Browse Dimension
Jan 13, 2006hello all,
I'm learning SSAS2005 I've installed and currently are running throught he
tutorials.
I've run into a problem where i'm able to process the cube with no errors.
i'm able to browse the cube with no probems.
however when I open a dimension and try to browse the dimension I get the
following error.
"Input String was not in a correct format"
I was able to save the project to another users machine and run without
problems. So we know it has to do with a set up on my machine.
any ideas are appreciated.
thanks
Time Dimension
Sep 4, 2007Hello,
I am trying to make a time dimension in analysis services. Is it possible to include the hours of a day in the dimension ? Is necessarily the day level the lowest of the hierarchy?
Thank you
ST
Company Dimension
Jan 25, 2007We deal with multiple vendors who provide us information via text/xml files. Vendor A may provide financial data, vendor b provides litigation data, vendor c provides ratings data. Our current structure has databases for each vendor with its own company table which basically makes all this data disconnected. Of course each vendor has its own proprietary company id to make records unique.
All of the data is based on companies so the grain of data would be at a company level. I would like to be able to link this information together by creating a dimensional model that has a single company table (DimCompany) and has facts populated based on the type of data we receive. Would this be the right sequence of events?
1. My initial load (historical) would have to look at all these data sources and create one company record in my DimCompany table. This table would then link to all other fact tables to provide a single view of company info. I would imagine this would have to be a fuzzy lookup since one company will be in all sources.
2. On subsequent loads (incremental) I would probably have to do a lookup of companies in the dimension via the proprietary code and add if the company wasn't there.
Any advice on tackling this issue would be greatly appreciated especially if SSIS was used in the process.
Dimension Security
Jul 24, 2007Hi,
I have Created Dimension Security by restricting the user to see only his information whoever login to the system by creating a new role. It is working fine by checking the cubes by changing the role through change user option but when the cube was integrate it to any Analysis services reporting tool(Excel, Proclarity) inspite of the roles mentioned it displays the records of all the user.
Can anyone please help me out in this situation???
Clustering Dimension
Nov 20, 2006I am new to data mining and have a question about OLAP dimensions built from models. Do you know if you can use a dimension that was created by the mining model wizard in the same cube that is being used as the source for mining model it self? I keep getting an error about a dependency loop and just want to make sure that I am not trying to do something impossible.
If this is illegal, do you know of a way of doing this without essentially having one cube dependant on another via a mining model and dimension? I tried to use the wizard to create a mining off of a table but it did not give me the option of creating a dimension from it.
Join Dimension On Itself?
May 29, 2008how do i join a dimension on itself? i'm pretty sure this isnt really how MDX is supposed to work, but I've run into a case where i'm hoping this can be done in MDX. i'll try to simplify as much as possible. if my Customer dimension contains these attributes:
[Customer]:
- [FullName]
- [ID]
- [ParentID]
Is it possible to have a query return a customer's FullName, ID, and FullName of the ParentID.
Example with values:
FullName, ID, ParentID
Jim, 1, 1
Jane, 2, 1
A query on ID=2 getting (FullName, ID, FullName of ParentID) should return: (Jane, 2, Jim)
Thanks in advance
Time Dimension
Aug 14, 2007(This is the Twilight Zone......not so much)
What are the pros and cons to using SSAS to create time dimensions based on a date field in the fact table as opposed to a stand alone time dimension table.
I can see many problems with loading a time dimension table. The date is from the same table as most of my fact data. I have a column in my OLTP that sets last change date so I can tell if my fact data is an insert or update but it wouldn't tell me if the date column had been changed, just some column in the table. I'm going to have several thousand sales on any given day so I'll be reading a lot of rows just to put one row into the dimension.
From a SSIS point of view I'd think leaving the date in the fact table would be better.
Sorting Dimension First
Apr 4, 2007Hi!
How do I first load data into dimension tables and then Fact?
Can I Use Dimension In SQL SERVER 2005?
Feb 12, 2006I hope to pass a dimension such as int A[5] to a stored procedure
A[0]=10
A[1]=20
...
Can I do that in sql server 2005? if so , please give me a sample, thanks!
Calculation From Measure && Dimension, Please Help !!
Dec 16, 2005Hello
I am a newbye with Analysis Services and am desperately trying to find a way to include a calculation between one of my measures (Teus), divided by the vessel capacity, where vessel is one of my dimensions (and is therefore not depended on other dimensions...)
Any ideas how I could implement that ?? This would help a lot, thanks for your help,
Aurore Bui.
Help Using Multiple Dimension Mdx Query
Mar 16, 2006Hi
I am new to Analysis services. and I have figure out the following things:
I have a cube with multiple dimensions. I want to display all the options in cube along with the count of the rows for that combination. I am unable to understand how to count the dimensions and apply to column.
For example, I have say dim1, dim2, dim3 dim4, name as dimensions and cnt as count of the rows. Now I want to write an mdx expression that returns me dim1, dim2, dim3, dim4 with count(name fields) for above combination so the output will be as follows:
name field1 name field2
dim1 dim2 dim3 dim4 200 100
how do I do it?
Also how do we accept input parameters for mdx expression. for example in the above thing, if I have to accept an input for dim1 and display the output values for dim1 how do I do it?
Any help is appreciated. Thank you.
Create Dimension Without Member
Apr 7, 2006Hi
Analysis manager won’t let me create dimension unless fact table filed has value init. Other words, how do I create MT dimension(dimension without member)
thanks
Need Help To Create My Time Dimension
Mar 30, 2004Hi,
I have a fact table which contains the transaction date, ProductID, QTySold, TotalSaleAmount, etc...
Since I am new to OLAP therefore I need help to now create the table for TIME on which I will be basing my time dimension... I have read a few articles and have gathered that at the end of the exercise my fact table should have a 'timeid' column which will be linked to the same column in the table being used for the time dimension...
I have gone through the tutorial of MS-Analysis Service and FoodMart example have some idea about what he structure of this table will be.
My questions are:
1. I need guidance on how to create the table for time. One option is to just copy the table used in the Foodmart example but thought that might work but my concept will not be clear
2. The structure of the table to be used for time dimension is quite clear (i think this part is easy). What I want to understand is that how do i POPULATE this table which data? Can some one provide me with scripts, SPs, or whatever to do this.... This is the area where I am lost...
3. How will I enter the "TheDate" column in my fact table and link it with my table for time dimension...
Looking forward to someone's help.
BTW, I would like to share a very good article which i recently found in one of the newsgroups. Some of you might like appreciate it too:
http://www.sqljunkies.com/Article/D1E44392-592C-40DB-B80D-F20D60951395.scuk
Understanding Dimension And Fact
Sep 12, 2007A few questions:
1) We have numerous fact tables with surrogate keys which reference just one dimensional surrogate key. How does this work?
2) Are the ‘facts’ feeding data TO the ‘dimensions’ (back end warehousing)? Or are the ‘Dimensions’ feeding facts to the ‘facts’ tables for lookups!?
Nb: Im very inexperienced at database design.
Im really also using this thread to get contacts for future harder questions!
Thanks kindly
AS2005 - Dimension Problem
Oct 8, 2007How do I create dimensions that exists in multiple cubes with different facttables. The dimensions must have the same name.
In AS2000 you separated them as shared dimensions and private dimensions.
How do I manage this in AS2005?
Creating A 4-4-5 Time Dimension
Aug 29, 2007Hello:
Very soon my company will be moving to a 4-4-5 reporting schedule. Basically, what this means is that the first month of the quarter will have 4 weeks, the second will have 4 weeks, and the third will have 5 weeks. Therefore, for the 2007 the dates for Jan, Feb and Mar will be as follows:
Jan - 1 - 27
Feb - 28 - 24
Mar - 25 - 31
Currently, I have an SSIS package creating a record for each day in the Time Dimension. Is there any script out there that will help me build a Fiscal calendar such as the one described above?
I realize that this is not a direct SSIS question but I figured that some of you might have encountered this situation and hence my post.
Thank you!
Dimension Table With Just 1 Column?
Dec 14, 2007
Hi
I am working as datawarehous architect with a large concern and i designed a datamart witha fact table surrounded by 5 dimension tables. My PM who do not know datwarehousing has abruptly changed my design and instead of 5 dimension tables has increased to 17 tables which just 1 column each in them.
This is hell of a design because if it is a single column dimension then there can not be any hierarchy in the dimension table , better will be to push this column as a fact.
What should I do now. Moreover he has asked me to not to use SSIS and code everything in stored procedure.
Please guide.
Jigjan
Different Reportparameters For Same Dimension In Different Cube
May 24, 2007Hi,
I have 1 report with 2 charts, both charts have their own dataset. The two datasets are mdx queries on 2 different cubes, but some dimensions have the same name.
Now I want to have 2 differenent selectable parameters for the [dim time] dimension. One for the first query in the first cube and the second for the other query in the other cube .
So I check in the mdx query builder, both dimensions as parameter, but because both dimensions have the same name, i have only one selectable [dim time] -parameter in my report.
How can i solve this?
Thanks,
Dennis
Help On Populating Dimension Table
Dec 30, 2007
While populating dimsnion table the requirement is that the 1st record in all dimension should be " Not Available"
and if the fact table has a null value for that dimension it shouldbe tied to " Not Available" record in dimension
Please help hwo to do both the things.
JJ
Analysis :: Edit Dimension DSV
Jun 10, 2015I am unable to see my DSV when I try to edit my dimension. I have added some new fields in my dimension table and now want to use them in my cube dimension.
View 5 Replies View RelatedHow To Create Top 10 In The Dimension Or Drilldown
May 26, 2008Hi,
some of our Drill down has many values and we would like to display only top 10 ...
I know how to create CALCULATEDMEMBER for the cube ..but that becomes a measure and will NOT be in the drill down so,
how to create Top 10 for Raw Url
Drill Down
Groups
Top 10 Urls
( this has 10000 values€¦€¦.. can we just display top 10 here? Right now the report never comes back because of the count€¦ or if there is a better way let me know
thanks
Order By Dimension Members In MDX
May 22, 2007Hello!
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!
SSIS Dimension Loading Help.
Dec 5, 2007Hi,
Can anyone provide me with some sample examples on how to load dimension tables in SSIS. Any help is highly appreciated!
Thanks in advance!
Regards,
Vivek.
Dimension Loading Problem
May 27, 2008
I have a dimension table that uses slow chaning type 1 and 2€¦
Source Table
CD Category Object_key
-1 Not Provided NULL
1 CADA 1002
2 DFG 1889
3 GHJ 456
4 CACA 54635
I am using ObjecT_key as a business key
Destination ( Dimenstion)
Key Category business_key
-1 Not Provided NULL
1 CADA 1002
2 DFG 1889
3 GHJ 456
4 CACA 54635
5 Not provided NULL
Problem is When a business_key is NULL , slow changing dimension wizard doesn€™t recorgize whether it€™s a new or existing value, so it keep trying to add a new row..
How can I fix this issue?
Slow Changing Dimension Help
Apr 5, 2006Hi there
I have a dimension table with effective dating; I'm loading historical transactional data and want to associate the correct surrogate key from the dimension with the fact table transaction. My dimension table has a start date but no end date, the end date is assumed by the start date of the next record with the same id. So I want the surrogate key which is the most current but whose date is not before the transaction date. I know I need a subquery but not too sure how to write it.
Thanks in advance.
Derek
General: Audit Dimension
Jun 28, 2007What is the best practice for setting up audit dimensions in 2005? I know that we have the audit transform available, but should we be capturing extra information beyond this?
I had seen one of the design tip articles by kimball on what should be added, however, this article was from some time ago... (http://kimballgroup.com/html/designtipsPDF/DesignTips2001/KimballDT26AddingAnAudit.pdf)
Also, I try to capture the audit guid in the record and then, later, capture the rest of the audit information... I notice that the guid's are different between the audit transforms... is there a way to capture all of the information but not write it all out to each record? i.e. only capture the guid to the record and the rest of the information to the audit table.
Analysis :: How To Get Dimension Key Attribute In MDX
Apr 30, 2015Many dimensions don't have unique members. Instead, the dimension source data has duplicates at the leaf level: it's left up to SSAS to aggregate up to the actual leaf level used in hierarchies.
Every cube I've worked on in the past, a dimension is clearly defined in the source data, with uniqueness already present there: we don't make a dimension out of duplicated, sort of facty data. This kind of design seems as weird to me as an unnormalised SQL database.
Here's an example to illustrate what I mean; I'll use that Adventureworks database.
We have a Geography dimension with a Geography hierarchy. Levels go like this from top to bottom:
Country
State-Province
City
Postcode
The Geography dimension has a key attribute called Geography Key. It's there in the cube design as a dimension attribute, but it's not in any of the hierarchies, so I can't query it in MDX. But that's fine: it has the same cardinality as the lowest level (Postal Code), because the dimension has some kind of normal design.
In the cube I'm dealing with, it's all messed up. Using the AdventureWorks example above as a parallel, someone made a Geography dimension with source data keyed on [PostalCode, ExactAddress], but only wanted the dimension granularity to be PostalCode.
This makes it very hard to debug why the data in this dimension is incorrect. I can't match up the dimension members in the cube to the source data, because the dimension doesn't actually go down to the real leaf level!
So I have a dimension attribute called ExactAddressKey, but I can't query on it in MDX, because it's not part of any dimension hierarchy. Unfortunately changing any part of this cube design is not possible, so I can't even experiment with settings and see what happens.
How I could get to the leaf level of the data imported? Something like
Geography.Geography.TheInvisibleLeafLevel.Members.Properties('Key')
Or does this kind of dimension design result in SSAS discarding all the data that's more granular than the most granular attribute defined in any hierarchy - so that the data actually isn't there to be queried?
About Slowly Changing Dimension
Oct 25, 2007I have one question regarding Slowly Changing Dimension component in SSIS. Does SCD also delete records in warehouse if they does not exist in source anymore, or does SCD only insert new and update existing records? Can someone explain me a little bit more about inferred members? Thanks.
View 1 Replies View Related