Analysis :: Linking Multiple Fact Table At Different Granularity To Same Dimensions
Jul 15, 2015
I am modelling two fact tables of Actuals and Budget which are at different granularity, Actuals are at day, customer and product sub category level. Budgets are at month, Region and Product category level.
Month, Region and Product Category is present in Date, Region and Product Category dimension respectively. I have only three dimensions as Customer, Product and Date. Linking those dimensions to Actual Fact table is not an issue, what is the best way and options are there to link budget fact table to those three dimensions.
View 2 Replies
ADVERTISEMENT
May 22, 2008
Hi,
I have 2 fact tables and 2 dimensions (one of them is time).
fact1 ---- pk-fk ------ fact1-details-table
(date) |
| Dimension.
|
TimeDim
Is there a way to define measure group on fact1-details-table using TimeDim. Date info is only in fact1 table and not in details table.
Is namedquery joining the 2 fact tables the best solution in this case? There is so much redundancy using one fact table, so underlying sqldb uses 2 tables.
Thanks,
Chandra.
View 1 Replies
View Related
Jul 6, 2015
I built my first tabular model and see that my fact tables are also appearing as dimensions. In Multi dimensional mode i could choose which are the dimensions. How do i do that in tabular model.
View 2 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
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
Jun 13, 2015
I'm new to MDX, and most of the time I customize existing queries rather than writing new ones. I currently have a MDX query like this
SELECTÂ
[Measures].[Fees Billed]
         on 0,
except([Age].[Day Buckets].members, {[Age].[Day Buckets].[All], [Age].[Day Buckets].&[Unknown]})
 on 1
FROMÂ
   MyCube
WHERE ([Fiscal Period].[Fiscal Year].&[2015], [Customer].[City].&[Auckland] )
Which brings the fees billed by age buckets where the customer's city is Auckland. I also have another dimension called [Sales Agent] with a member [City] in it, and there is a member in [Customer] called [Customer].[Sales Agent]
I am trying to retrieve the same information where the customer's sales agent's city is Auckland rather than the customer's city.
If it is SQL, I will join Customer and SalesAgent on Customer.SalesAgentUno = SalesAgent.SalesAgentUno and bring in the desired data. Any way in MDX to do this?
View 4 Replies
View Related
Oct 26, 2015
Say you have a fact table with a few columns that all reference the same key column in a dimension table, you want to write a view to return the information for those keys?
USE MyTestDB;
GO
SET NOCOUNT ON;
IF OBJECT_ID ('dbo.FactTemp' ,'U') IS NOT NULL
DROP TABLE dbo.FactTemp;
[Code] ....
I'm using very small data at the moment, and the query plan and statistics don't really say which way.
View 2 Replies
View Related
Aug 16, 2007
Hi,
I use lookups to map surrogate of level 1 dimensions to my fact tables in SSIS.
But how to handle a level 2 dimension with a ValidFrom and a ValidUntil date field?
I do not use an IsCurrent column, because this could problem with late arriving facts.
- In dts I used an SQL statement like this:
update SA
SET SA.DimProdRef = Dim.RecordID
FROM SAWarenEingang SA, DimProd Dim
where SA.ProduktNumber = Dim.ProduktNumber
and SA.ArtikelkontoBewegungsdatum between Dim.ValidFrom and Dim.ValidUntil
Now in SSIS I want to handle the whole thing in the data flow without using a staging table:
- Using Lookups: I would have to pass the date column for each inside the fact table into the lookup. That does not work.
- Using Execute SQL in the data flow: would be very slow, because the statement will be executed for any line in the dataflow
Any ideas?
Best regards,
Stefoon
View 10 Replies
View Related
Jul 5, 2015
I have one dimension and one measure group. I deployed and processed the cube. Now I am able to browse the data. Now I added one more dimension. I deployed and reprocessed again the Cube. Now I am not able to see any values. Â I am getting like below.
View 6 Replies
View Related
Oct 28, 2015
I am modelling cube in SSAS. Cube has around 20 dimensions and 6 fact tables. Some of the dimensions are common among the fact tables. e.g. Time dimension. Fact_PNL has 3 date columns for those we have 3 role playing dimensions in the dimension usages.
Another fact table has 5 date columns for them as well we have separate role playing dimensions in dimension usage tab. We have a common dimension Company which is foreign key in all fact tables. We might need to combine the data from multiple facts to get final output.
Should i create 6 role playing dimension for each of the fact table or use the same dimension for all fact tables?
Role playing dimensions should be created when we have multiple columns pointing to the same dimension ?
View 2 Replies
View Related
Nov 18, 2015
I'm currently setting up a Tabular Model to do some research between several fact tables. Â In this example i have two fact tables (table 1 and table 2)Â which I've created a 1 to 1 relationship on phone number. Â Typically I create a relationship between these tables to find common data between the two. Â However, in this case I am trying to figure out the best way to model the data so that I can easily surface data from one table that does not exist in the other. Â I would liken this to a LEFT JOIN or a WHERE NOT EXISTS in SQL.
Table 1 has all of the data and Table 2 Only has a subset of the data from Table 1. Â What I'm trying to do here is display what attributes in Table 1 may play a part in records not existing in Table 2. What is the best way to model this?
View 3 Replies
View Related
May 17, 2007
Hi,
I am relatively new to SSIS/SSAS. I have searched the forums but cannot find an answer to my question.
I created a cube in SSAS and have deployed it. Now I am trying to use SSIS to populate the cube. I have setup a DS that points to the SSAS instance - it uses OLEDB Provider for Analysis services 9.0.
When I try to use a data flow task OLE DB source to truncate the dimension/cubes I do not see the DS in the list to select?
I am finding it hard to get into the SSIS way of organizing the processing.
Any Ideas?
View 3 Replies
View Related
Nov 17, 2004
i face a problem to create a new cube with the fact table without numeric field as a measure. can i use others data type of field as a measures?
anyone can provide me a solutions to solve it? thank you ..
View 1 Replies
View Related
Nov 22, 2015
Is it correct to say that for each cube you can have only one Fact Table? I am having a funny dispute just now.Â
According my experience I never built cubes with more than one fact table, if I want take data from more than one table I write a view and I use it as Fact table...but a cube with two or three fact tables? Never tried..
View 3 Replies
View Related
Jan 17, 2005
I am building a health care application that marries transaction-level data (health care services provided) with person-level characteristics that have a time-dimension. The person-level characteristics are diseases that the person has (these disease all have a start and some have an end date). The diseases are stored in a table in which the foreign keys are a person-identifier, a time identifier (month/year) and a surrogate for the disease. Persons can have more than one disease at a time (the diseases are NOT mutually-exclusive). There are no measures in this table. The transaction table has a foreign key for person and time (month/day/year), a procedure code (the type of service rendered) and money (the cost of the services).
How do I answer the following questions:
What is the total cost of care (the sum of all service costs) last year for persons with "disease A"?
What is the total cost of care last year for persons with "disease A" AND "disease B"?
What is the total cost of care last year for persons with "disease A" OR "disease B"?
I've tried a factless fact table but can't get it to work. If anyone has the right solution and can communicate to me before I slit my wrists, I would be greatly appreciative!!!
View 1 Replies
View Related
Oct 6, 2004
I have a big table with several types of transsactions: PO (Puchase Orders, SO,( Sales orders), INV (Invoices) ....
I want to create cubes with only one type of transactions (1 cube for PO,...)
Where and how can I filter the rows I want to use in my cube ?
Thanks
View 2 Replies
View Related
Jul 10, 2007
Hello Everybody,
Can anybody please let me know the procedure for loading data into Dimensions & Facts? And what is the sequence of loading?
Thanks in Advance
Rajesh
View 1 Replies
View Related
May 8, 2015
I am using a WriteBack Partition to receive data from various inputs and appends any new data that I add to the WB partition.Â
I am able to read the data immediately in the WB partition through a Fact partition query. This is working at this point as desired.
Eventually I want to move the data from the WB partition into Fact Partition. How can I do this, manually and through automation.Â
View 5 Replies
View Related
May 6, 2015
I wanted to know the meaning of Granlatiy of Fact with some example.
View 3 Replies
View Related
May 30, 2007
What are the general guildlines for choosing these settings, like paralle vs. sequential, different error configurations? The default selection for processing multiple dimensions is paralle and use default error configuration. But the default for processing multiple partitions is sequential. I cannot find anything helpful other than the definitions from the online help. TIA.
View 9 Replies
View Related
Apr 29, 2015
I have  a large fact table about 500 million rows, and I am using 2008 r2, thus I am having the file system error, I have browsed online and tried all the fix , but I am still having the error . I tried taking only about year data (which was still around 200 million records) and  I was still having the error.
View 11 Replies
View Related
Nov 3, 2015
We have a sales fact table that has sales by customers, products, date etc. and dimension tables such as customers, products etc.
We built a multi-dimensional cube on top of these tables. how do we support queries like "show me top 10 products by sales amount and then show customers that didn't buy these products"?
View 4 Replies
View Related
May 23, 2008
I have a fact table with one column storing string values. I need to use this column as a measure, but as different measure. Here's a snapshot of the fact ( with 4 columns)
#1 #2 #3 #4(value)
1 1 2007-12-20 Male
2 1 2007-12-20 French
1 2 2007-12-20 Female
2 2 2007-12-20 English
I have a dimension which is like
id name
1 Gender
2 Language
Is there a way to get measures from column 4 in fact table - so that I can give reports like how gender graph during a time period or language usage during a time period. The design will have more than 2 dimension members, so fact column can hold more data like location, etc...
Can you please suggest a way to handle such kind of fact table?
When I try to create a measure based on that column (and 'no aggregation') - I get error like - "Error 1 Errors in the metadata manager. The data type of the 'value' measure is not valid because the data type of the measure is a string type."
Thanks,
Chandra
View 4 Replies
View Related
Jan 19, 2006
Greetings,
Iam new to SQLl2005. Iam using DTS to transfer data from my source to the warehouse. I have a couple of tables in my source whein I have to join these to tables fields and insert the same in teh warehouse fact table. I have used a Join query in my Oledb source component, What other component needs to be used to insert the data into the fact table.
I also need to extract same data with aggregation and insert the same into an another Fact table.
Kindly help.
View 21 Replies
View Related
Oct 23, 2002
In Analysis Services is there a way you can change a private dimension into a shared dimension without having to recreate it
View 1 Replies
View Related
Mar 3, 2006
I have transaction table where the rows entered into the transactioncan come a result of changes that take place if four different tables.So the situation is as follows:Transaction Table-TranId-Calc AmountTable 1 (the amount is inserted into the transaction table)- Tb1Id- Tb1AmtTable 2 (an amount is calculated based on the percentage and insertedinto the transaction table)-Tbl2Id-Tb2PercentageTable 3 (the amount is inserted into the transaction table)-Tbl3Id-Tbl3AmutTable 4 (an amount is calculated based on the percentage and insertedinto the transaction table. )-Tbl2Id-Tb2PercentageHow do I create referential integrity between the Transaction table andthe rest of the tables. When I make changes to the values in Table 1 -4, I need to be able to reflect this in the Transaction table.Thanks.
View 6 Replies
View Related
Aug 13, 2004
I have a problem where I have 3 three measures in a virtual cube:
"Actual", "Budget" and "Full Year Budget".
The dimensions I have are:
- Account No_ / Name
- Cost Code
- Sub Cost Code
- Time/Dates
- Budget Name
Both "Actual" & "Budget" measures need to be filtered/dimensioned by:
- Account No_ / Name
- Cost Code
- Sub Cost Code
- Time/Dates (exclusive to "Actual", "Budget")
Thus have put these in one cube
AND "Full Year Budget" needs to be filtered/dimensioned by:
- Account No_ / Name
- Cost Code
- Sub Cost Code
- Budget Name (exclusive to "Full Year Budget")
THUS have put this as one cube…
I then created a virtual cube, with the 2 cubes thinking that the dimensions I created in the original cubes would only filter the measures of the original cube measures in the virtual cube. ...BUT all dimension filters in the virtual cube filter all measures in the virtual cube, irrespective of which dimensions were created with the original cubes.
please help!
View 1 Replies
View Related
Jun 25, 2015
I need to show the dimensions of my model like columns in the result. I have this query
withÂ
member [Measures].[Customer] as [Customers].[Customer].CURRENTMEMBER.Name
member [Measures].[UCs] as [UCs].[UC].CURRENTMEMBER.Name
member [Measures].[Order Type] as [Order Types].[Order Type].CURRENTMEMBER.Name
member [Measures].[UC Dates] as [UC Dates].[UC Date].CURRENTMEMBER.Name
[Code] .....
View 14 Replies
View Related
May 4, 2015
Actually I want to do distinct sum on a measure group, please find the below table as sample
XL Measure group
LKÂ Â Â Â OKÂ Â Â Â Â Amount
1Â Â Â Â Â Â Â 10Â Â Â Â Â Â Â Â 100
1Â Â Â Â Â Â Â Â 11Â Â Â Â Â Â Â Â 100
3Â Â Â Â Â Â Â Â Â 30Â Â Â Â Â Â Â 250
3Â Â Â Â Â Â Â Â Â 31Â Â Â Â Â Â Â 250
3Â Â Â Â Â Â Â Â Â 32Â Â Â Â Â Â Â 250
For the above measure group two dimensions have relationships, One is L dimension which is having relationship with XL on LK and One is O dimension which is having relationship with XL on OK. If I drag L dimension attributes  it should show results as below
LK LName Amount
1Â Â Â Â AÂ Â Â Â Â Â Â Â 100
3Â Â Â Â CÂ Â Â Â Â Â Â Â Â 250
But above results are coming as below
LK LName Amount
1Â Â Â Â AÂ Â Â Â Â Â Â Â 200
3Â Â Â Â CÂ Â Â Â Â Â Â Â Â 750
If I drag O dimension attributes along with L dimension, it should show results as below.
LK  LName  OK     OKName  Amount
1Â Â Â Â Â Â Â Â AÂ Â Â Â Â Â Â Â 10Â Â Â Â Â Â XYZÂ Â Â Â Â Â Â Â 100
1Â Â Â Â Â Â Â Â AÂ Â Â Â Â Â Â Â 11Â Â Â Â Â Â UVWÂ Â Â Â Â Â Â 100
3Â Â Â Â Â Â Â Â CÂ Â Â Â Â Â Â Â 30Â Â Â Â Â Â PQRÂ Â Â Â Â Â Â Â Â 250
3Â Â Â Â Â Â Â Â CÂ Â Â Â Â Â Â Â Â 31Â Â Â Â Â KLMÂ Â Â Â Â Â Â Â 250
3Â Â Â Â Â Â Â Â CÂ Â Â Â Â Â Â Â Â 32Â Â Â Â Â TUVÂ Â Â Â Â Â Â Â 250
I used formula Measures.Amount/Measures.Count, this formula is not showing correct results when I don't drag any dimensions, it is showing results for All member as 425, but it should show as 350.
So I made a same change ([L].[LK].Currentmember, Measures.Amount)/([L].[LK].Currentmember,Measures.Count), this worked fine but performance is very low and so stopped working on this.
Atlast I did the measure group like this
LK    OK     LAmount  OAmount
1Â Â Â Â Â Â Â 10Â Â Â Â Â Â Â Â 100Â Â Â Â Â Â Â 100
1Â Â Â Â Â Â Â Â 11Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â 100
3Â Â Â Â Â Â Â Â Â 30Â Â Â Â Â Â Â 300Â Â Â Â Â Â Â 300
3Â Â Â Â Â Â Â Â Â 31Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â 300
I want to show Measures.LAmount when only L dimension is querying and want to show OAmount when both L dimension and O dimension are querying. Is this possible ?
View 3 Replies
View Related
Jul 24, 2015
SSAS 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.
View 4 Replies
View Related
Aug 4, 2015
I have make a calculated member for previous period of an given date range. The previous period is the same date range from the previous year, and I have managed to achieve that with the calculated member:
Create member currentcube.[Measures].[PrevPeriod] as
(ParallelPeriod( [Start Date].[Cal Hierarchy].[Year], 1, [Start Date].[CAL Hierarchy].CurrentMember), [Measures].[Count]);
This member returns the correct result as long as my query uses the time dimension, which makes sense... but I also need to show results sliced by other dimensions in bar charts that do not display the time dimension. For example, I have a dimension with only 3 members called [Region].[Area].[AreaName].
The result set for the bar chart needs to look like this:
[AreaName]Â | [Count]Â |Â [PrevPeriod]
East           |   43     |      56
West          |   53     |      95
But the [PrevPeriod] only returns values if I include the time dimension. I essentially need to sum the results of the time dimension/AreaName/[PrevPeriod] tuple down to just Areaname/[PrevPeriod] for whatever date range may be involved.
I don't know if this is significant to the issue, but the client tool that generates the bar charts builds the query with the date range as a subcube in the FROM statement. If the [PrevPeriod] is outside of the subcube that is still OK, as long as the time dimension is included in an Axis on the final select statement, so at least I know I am not suffering from the members inside the subcube. I've also found in SSMS that it makes no difference if I make the query a subcube, or put the date range in a where clause instead; I still get NULL for [PrevPeriod] without the dates.
I can't imagine that this is an unusual situation, so I hope I've explained it adequately! What is the recommended technique for summarizing a Parallelperiod by dimensions without displaying the time/dates ?
View 6 Replies
View Related
Sep 22, 2015
I have a cube with 2 many-to-many dimensions where a special mdx query needs about 5 seconds. When I resolve the many to many relationships by multiplying the data in the fact table the query needs 21 seconds.
In general do many-to-many dimensions slow down query performance of a cube?
Without the many-to-many dimensions of course the fact table has much more rows. Could this be the reason for the performance loss?
how to tweak query performance of a cube in general?
View 3 Replies
View Related
Aug 31, 2004
Hi,
I have a Time Dimension which allows me to select a specific YEAR, or YEAR & QUARTER or YEAR & QUARTER & MONTH, or YEAR & QUARTER & MONTH & DAY.
Is there any way that I can have a range of dates?
Is it possible to have 2 time Dimensions for example which did the following:
a start: Year|Month for example
(>= Year|Month )
.......and......
an end: Year|Month
(<= Year|Month )
Thus I would be able to select a range of dates/months.
Do you know if this is possible to write this inot the dimension?
Thanks,
David
View 3 Replies
View Related
