Analysis :: Use IIF For Multiple Conditions - MDX
Jun 2, 2015
I am have a dimension to use as a slicer, I am trying to associate that dimension with a measure group using a calculated measure. I am using the below query:
With member [THD Inventory Balance] as
IIF(
[THD Conversion Units].[Conversion Units].CURRENTMEMBER = [THD Conversion Units].[Conversion Units].&[EA], [Measures].[thdpos - Inventory Balance EA], 0)
IIF(
[THD Conversion Units].[Conversion Units].CURRENTMEMBER = [THD Conversion Units].[Conversion Units].&[LF],
[Code] ....
But it fails with the error: The syntax for 'IIF' is not correct.
View 6 Replies
ADVERTISEMENT
Jan 10, 2008
My question is fairly simple. When I join between two tables, I always use the ON syntax. For example:
SELECT
*
FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId)
No problems there. However, if I then decide to further filter the selection based on some trait of the UserRole, I have two options: I can add the condition as a WHERE statement, or I can add the condition within the ON block.
--Version 1:
SELECT
*
FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId)
WHERE
UserRoles.Active = 'TRUE'
-- Version 2
SELECT
*
FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId
AND UserRoles.Active = 'TRUE')
So, the question is, which is faster/better, if either? The Query Analyzer shows the two queries have the exact same execution plan, which makes sense, since they're both joining the same tables. However, I'm wondering if adding the condition in the ON statement results in fewer rows the JOIN statement initially needs to join up, thus reducing the overall initial size of the results table before the WHERE conditions are applied.
So is there a difference, performance wise? I imagine that if Users had a thousand records, and UserRoles had 10 records, then the JOIN would create a cartesian product of the two tables, resulting in 10,000 records in the table before the WHERE conditions are applied. However, if only three of the UserRoles is set to Active, would that mean that the resulting table, before applying WHERE conditions, would only contain 3000 records?
Thanks for whatever information you can provide.
View 7 Replies
View Related
Apr 15, 2014
I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car
where Price=@Price1 or Price=@price2 or Price=@price=3
and
where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3
and
where Model=@Model1 or Model=@Model2 or Model=@Model3
and
where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
View 4 Replies
View Related
Sep 12, 2006
Hi All.
Is there a way to have multiple AND conditions on the same field in a database.
EXAMPLE
SELECT * FROM tbl
WHERE field1 = 1 AND field1 = 2 AND field1 = 5
Thanks
View 9 Replies
View Related
Jul 20, 2005
Hi All,Just wondering if anyone can tell me if you can test for multipleconditions as part of an "IF" statement in T-SQL in SQL Server 2000?ie something like:IF @merr = 1 or @merr=2BeginSELECT statementUPDATE statementEndAny help much appreciated!Much warmth,M.
View 1 Replies
View Related
Feb 2, 2008
Hi,
I've a table with a field named 'TypeOfProd' that has an ID for the various types of products: ex.:
1 - product A
2 - product B
3- product C
4 - product D
....
10 - product J
and so on
I need to create a stored procedure that querys only the product types selected by the user.
The user can select 1, 3, 5, 10 or 1, 3 or 3 or 0 for all or some other combination.
For the first user selection a have something like this
SELECT Prod FROM tableProd WHERE TypeOfProd = 1 OR TypeOfProd = 3 or TypeOfProd = 5 OR TypeOfProd = 10
For the second,
SELECT Prod FROM tableProd WHERE TypeOfProd = 1 OR TypeOfProd = 3
Is it possible to have a stored procedures that runs a query with this random scenario?
please help
Thanks
JPP
View 8 Replies
View Related
May 12, 2008
Select c.Plan, c.ClaimNumber
from tbFLags c inner join tbMembers m
On c.Claim = m.HistoryNum
where c.Plan = 'J318' and c.Paymon = c.Rmon and c.Rmon = '2008-03-01'
Now I want to add these into this statement, what should be done.
Members meeting any of the 3 sets of criteria should not be selected
1) tbFlags.Hosp='1'
2) tbFlags.RD='1' OR tbCMSFlags.RAType in ('D', 'I2')
3) Deceased = tbMembers.DOD is not null.
View 27 Replies
View Related
Nov 12, 2007
I have a ListBox controls that contains about 5 items. A stored procedure is executed based on selections of other controls ont he screen, but I cann't figure out how to properly get the dynamically selected conditions passed to the sproc from C#.
If a user selects 3 of the five items, the sproc needs to build something like this:
WHERE Region LIKE Item1 OR Region LIKE Item2 OR Region LIKE Item3
I cannot figure out how to do this. It works properly if I just make the ListBox SelectionMode as Single and pass that one selected item's value.
Any help is greatly appreciated.
Thanks,
Chris
View 3 Replies
View Related
Nov 22, 2007
Hi,
I am attempting to explain my probelm again. Please read it:
I have 3 tables. CallDetail, Call and Request. The tables are populated in the following order: One row for CallDetail, One for Call and one for Request and so on.
I have to generate a UniqueNo - Per empid, Per StateNo, Per CityNo, Per CallType. The no will remain same for the same CallDetailID and ordered by the date created. However if the CallDetailId changes, the no. will increment based on the empid, Per StateNo, Per CityNo, Per CallType
For eg:
For Eg: ( Assume Call Detail id is changing for all the days)
Monday - 3 calls made for empid 1, state SA023, city 12 and call type 1 will generate a unique id 1 for all 3 calls
Tuesday - 2 calls made for empid 1, state SA023, city 12 and call type 1 will generate a unique id 2 for both calls
Wednesday - 3 calls made for emp id 1, state SA023, city 12 and call type 2 will generate a unique id 1 for 3 calls as the call type is different than the previous day for same employee
Thursday - 2 calls made for empid 2, state SA023, city 13 and call type 1 will generate unique id 1 for both the calls as combi of city and call type are different.
So the unique id has to be generated considering empid, state, city and call type, ordered by the EntryDt. EntryDt is needed because :
3 calls made for empid 1, state SA023, city 12 and call type 1 at 10/11/2007 10.00 AM will generate a unique id 1 for all 3 calls
2 calls made for empid 1, state SA023, city 12 and call type 1 at 10/11/2007 12.00 AM will generate a unique id 2 as the call was registered later.
Here is what I wrote with the help of a mod over here:
Code Block
DECLARE @Request TABLE(RequestID INT, CustomerName VARCHAR(30),
StateNo NVARCHAR(5), CityNo INT, CallID INT, UniqueNo INT)
INSERT @Request
SELECT '324234', 'Jack', 'SA023', 12, 111, Null UNION ALL
SELECT '223452', 'Tom', 'SA023', 12, 112, Null UNION ALL
SELECT '456456', 'Bobby', 'SA023', 12, 114, Null UNION ALL
SELECT '22322362', 'Guck', 'SA023', 12, 123, Null UNION ALL
SELECT '22654392', 'Luck', 'SA023', 12, 134, Null UNION ALL
SELECT '225652', 'Jim', 'SA023', 12, 143, Null UNION ALL
SELECT '126756', 'Jasm', 'SA023', 12, 145, Null UNION ALL
SELECT '786234', 'Chuck', 'SA023', 12, 154, Null UNION ALL
SELECT '66234', 'Mutuk', 'SA023', 12, 185, Null UNION ALL
SELECT '2232362', 'Buck', 'SA023', 12, 195, Null
DECLARE @Call TABLE(CallID INT, CallType INT, CallDetailID INT)
INSERT @Call
SELECT 111, 1, 12123 UNION ALL
SELECT 112, 1, 12123 UNION ALL
SELECT 114, 1, 12123 UNION ALL
SELECT 123, 2, 12123 UNION ALL
SELECT 134, 2, 12123 UNION ALL
SELECT 143, 1, 6532 UNION ALL
SELECT 145, 1, 6532 UNION ALL
SELECT 154, 1, 6532 UNION ALL
SELECT 185, 2, 6532 UNION ALL
SELECT 195, 3, 6532
DECLARE @CallDetail TABLE(CallDetailID INT, EmpID INT, EntryDt DateTime)
INSERT @CallDetail
SELECT 12123, 1, '11/5/2007 10:41:34 AM' UNION ALL
SELECT 6532, 1, '12/5/2007 12:12:34 PM'
-- Query written with help of a helpful person here
UPDATE r
SET r.UniqueNo = dt.CallGroup
FROM @Request r
JOIN @Call c
ON r.CallID = c.CallID
JOIN (SELECT
CallDetailID,
EntryDt,EmpID,
CallGroup = ROW_NUMBER() OVER (ORDER BY EntryDt )
FROM @CallDetail
) dt
ON c.CallDetailID = dt.CallDetailID
select * from @Request
OUTPUT IS
324234 Jack SA023 12 111 1
223452 Tom SA023 12 112 1
456456 Bobby SA023 12 114 1
22322362 Guck SA023 12 123 1
22654392 Luck SA023 12 134 1
225652 Jim SA023 12 143 2
126756 Jasm SA023 12 145 2
786234 Chuck SA023 12 154 2
66234 Mutuk SA023 12 185 2
2232362 Buck SA023 12 195 2
Where as it should be
324234 Jack SA023 12 111 1
223452 Tom SA023 12 112 1
456456 Bobby SA023 12 114 1
22322362 Guck SA023 12 123 1
22654392 Luck SA023 12 134 1
225652 Jim SA023 12 143 2
126756 Jasm SA023 12 145 2
786234 Chuck SA023 12 154 2
66234 Mutuk SA023 12 185 2
2232362 Buck SA023 12 195 1
as the call for Buck is of calltype 3 which was not done earlier. So the no starts from 1.
Also how to add the paritioning by empid, StateNo, Per CityNo, Per CallType and yet maintain the same unique no for the same calldetailid.
Eg: CallGroup = ROW_NUMBER() OVER (PARTITION BY empid, state, city, calltype ORDER BY EntryDt )
thanks for any help.
View 4 Replies
View Related
Mar 19, 2008
Hi,
I have a tbl called BANK.
This shows a banking history of transactions and includes a field called TransType and a field called PaymentID.
I also have two other tables called Suppliers and SubContractors.
For each record in the bank, I need to match up a record in either the suppliers or subcontractors tbl based on the PaymentID value. I know if the record relates to either a Supplier or Subcontractor based on the value of the TransType field which will be either SUPPLIER or SUBCONTRACTOR or Null (in which case a match doesn't matter)
I have a working query based on joining just the Supplier tbl.. but how do I do the join to the other tbl aswell?
So overall, for each record in the bank, if the transtype is SUPPLIER I need to look in the supplier tbl for a match for that paymentID, and if the transtype is SUBCONTRACTOR, I need to do the same but in SUBCONTRACTOR tbl.
How would I best write that?
thanks for any help!
View 5 Replies
View Related
Dec 12, 2007
I have a table (GLTRANS) with thousands of lines.
1 column in the table (ACCNO) has 300 different values which all need to change to a new value.
ie. 11100 all change to 8100
11200 all change to 8200
I know how to do a simple UPDATE
UPDATE GLTRANS
SET ACCNO = '8100'
WHERE ACCNO = '11100'
But how can i combine into 1 script rather than having to continually change this script 300 times??
Thanks
Wilbur
View 6 Replies
View Related
Jan 6, 2004
I have a record that I want to insert into (2) tables. The first thing I want to do is see if a record already exists in the table for the user, if it does - I just want to skip over the insert.
next I want to do the same thing in the SW_REQUEST table. If there is a record in there for the member, I want to just skip the insert.
My code works as long as there isn't an existing record in the tables. Can someone give me a hand?
Here's what I have (and it doesn't work)
CREATE PROCEDURE b4b_sw_request
@FName as varchar(50)= NULL,
@LName as varchar(50)=NULL,
@Address1 as varchar(100) = NULL,
@Address2 as varchar(100) = NULL,
@City as varchar(50) = NULL,
@State as char(2) = NULL,
@Zip as char(5) = NULL,
@Email as varchar(100) = NULL,
@Send_Updates as smallint = '0'
AS
IF EXISTS
(SELECT FName, LName, Address1, Zip from MEMBERS WHERE FName = @FName AND LName = @LName AND Zip = @Zip)
BEGIN
RETURN
END
ELSE
BEGIN
INSERT INTO MEMBERS
(FName, LName, Address1, Address2, City, State, Zip, Email)
Values
(@FName, @LName, @Address1, @Address2, @City, @State, @Zip, @Email)
END
IF EXISTS
(SELECT MEMBER_ID FROM SW_REQUESTS WHERE MEMBER_ID = @@Identity)
BEGIN
RETURN
END
ELSE
BEGIN
INSERT INTO SW_REQUESTS
(MEMBER_ID, Send_Updates)
Values
(@@Identity, @Send_Updates)
END
GO
View 2 Replies
View Related
Jun 1, 2015
I have following query. Now I want to change this in such a way that if Condition 1 satisfies then it should not go to next condition ,if not then should go to next. Similarly the same format till condition 4.
select * from table where
IND = 'Y'
and
(
(STATE = @STATE and LOB =@LOB and COMPANY=@COMPANY)--Condition 1
OR
(STATE = @STATE and LOB =@LOB)--Condition 2
OR
(STATE = @STATE and COMPANY=@COMPANY)--Condition 3
OR
(STATE = @STATE)--Condition 4
)
View 4 Replies
View Related
Jul 8, 2015
I have a table with the following fields and data:
Offense, Year, Month, Count, Amount
F 2007 January 11 $49,238.00
F 2007 February 12 $24,000.00
F 2007 March 31 $55,184.00
F 2007 April 19 $64,647.00
F 2007 May 33 $100,010.00
F 2007 June 16 $59,678.00
F 2007 July 22 $39,700.00
F 2007 August 3 $9,000.00
F 2007 September 4 $75,000.00
F 2007 October 8 $19,250.00
F 2007 November 50 $106,153.00
F 2007 December 26 $80,000.00
I have data for years 2007-2014. I am designing an SSRS report, and want to use IIF statement to return data. Using the following:
=IIF(Fields!Year.Value="2007" AND Fields!Month.Value="February", "return value of count field", "n/a")
The above does not work, it doesn't return the value of Count field, it does return the n/a.
View 14 Replies
View Related
Feb 6, 2015
I have the got below T SQL query to fetch the matching record
DECLARE @MappingTable TABLE
(
Productname nvarchar(10),
identification_key1 int,
identification_key2 int,
identification_key3 int
[Code] .....
-- result - 'Orange'
This is an exact matching record and straight forward
Is it possible to identify the record using T SQL query based on the following scenarios
1) return the record - If all the three where conditions match
2) if record not found check and return the record where 2 columns values in the where condition match
-- Expected Result for below query: 'Orange', because 2 of the columns in where condition have matching values
SELECT Productname
FROM @MappingTable where identification_key1=1 or identification_key2 =2 or identification_key3 =1
-- result - 'Orange'
View 4 Replies
View Related
Jun 5, 2008
Hi,
I have 1 table with 5 rows. One of the rows has dateTime values. I want to know how many rows there are with a value in that column < today AND how many rows there are with a value in that column > today.
I'm not sure how to do this.
SELECT Count(*) WHERE dateColumn <= today AND dateColumn > today gives me everything.
I like to end up with a column containing the count of rows <= today
and a column with rows where date > today.
Is this possible in SQL or do I have to retrieve all rows and then loop over the resultset and check each row?
Thanks,
Marc
View 2 Replies
View Related
Sep 6, 2007
My goal is to create a trigger to automatically set the value for a status id on a table based on set criteria. Depending on the values of other fields will determine the value that the status id field is assigned. So far, I have written the trigger to update the status id field and I go through a seperate Update statement for each status id value. The problem is that I can't get this to work at the record level. The problem that I am getting is that if I have 50 records in TABLE1 and at least one of them satisfies the where clause of the update statement, all of the records get updated. So, using these two update statements, all of my records end up with a status value of '24' because that was the last update statement run in the trigger. Here is the code I have so far:
CREATE TRIGGER dbo.JulieTrigger1
ON dbo.Table1
AFTER INSERT,UPDATE
AS
BEGIN
BEGIN TRY
/*Update Table1.Status to POTENTIAL (id 23) status */
UPDATE TABLE1
SET status_id = 23
WHERE EXISTS (SELECT *
FROM TABLE1 a INNER JOIN TABLE2 b
ON b.order_id = a.order_id
WHERE a.start_dt IS NULL
AND b.current_status_ind = 1
AND b.lead_status_id NOT IN (15,16)
AND a.order_id = TABLE1.order_id)
/*Update Table1.Status to ACTIVE (id 24) status */
UPDATE TABLE1
SET status_id = 24
WHERE EXISTS (SELECT *
FROM TABLE1 a
WHERE fill_ind = 1
AND (end_dt IS NULL OR end_dt > getdate() )
AND a.job_order_id = TABLE1.job_order_id)
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE()
IF @@TRANCOUNT > 0
ROLLBACK TRAN
-- Return the error to the calling object
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
)
END CATCH
SET NOCOUNT ON;
END
GO
Thanks in advance for any help!
-Julie
View 1 Replies
View Related
Nov 18, 2015
Have a nvarchar column in ms sql that, based on user input, may or may not have a carriage return and a line break together (CHR(13)&CHR(10)), just a carriage return (CHR(13)), just a line break (CHR(10)). In using that column in ssrs, I need to find if any of the above exist and replace them with the string "x0Dx0A", "x0D", or "x0A" respectively.
I'm getting lost in this expression:
=SWITCH
( (InStr(Fields!Info.Value, CHR(13)&CHR(10) > 0, REPLACE(Fields!Info.Value, CHR(13)&CHR(10)), "x0Dx0A")), (InStr(Fields!Info.Value, CHR(13) > 0, REPLACE(Fields!Info.Value, CHR(13)), "x0D")), (InStr(Fields!Info.Value, CHR(10)
> 0, REPLACE(Fields!Info.Value, CHR(10)), "x0A")) )
Error is:
System.Web.Services.Protocols.SoapException: The Value expression for the textrun ‘Info.Paragraphs[0].TextRuns[0]’ contains an error: [BC30455] Argument not specified for parameter 'Replacement' of 'Public Function Replace(Expression As String, Find As String,
[Code] .....
View 2 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
Jun 2, 2015
I am using multiple IIF statements in ssas, it does not produce any output, does not shows any error, simply it processing..
View 3 Replies
View Related
Nov 5, 2015
Using 'TAIL([Time].[Time].[Year].MEMBERS,1)' I can get the current year dynamically. My question is how do I get the previous two years dynamically as well. I've tried a few different ways with no luck. As you can see below I'd like to replace 2014 and 2013 with current year -1 and current year -2.
SELECT
{
[Time].[Time].[Year].&[2013],
[Time].[Time].[Year].&[2014],
TAIL([Time].[Time].[Year].MEMBERS,1)
}ON COLUMNS,
NON EMPTY
{[Branches].[Branches].[Region]*[Measures].[Ship Resale S&D Run Rate]} ON ROWS
FROM SALES
View 9 Replies
View Related
Nov 5, 2015
I am trying to select some values as default for a parameter, I add my data set,add my parameter then I select the values in the default pane, when I run the report I get :
'the default value expression for the query parameter contains an error[BC30451] name is not declared.
When I look at the expression of the default parameter (in the report data pane under the specify values) it appears like
=new Object() {[Sty].[ST].&[1], [Sty].[ST]..&[15]}
with a red scrible under the ()
View 9 Replies
View Related
Jun 3, 2015
In Analysis services,i have multiple DB's. i need to delete all using XMLA script.
View 3 Replies
View Related
Apr 14, 2015
I am recently encountering proble with SSAS cube,In a day cube is going to offline for several time and unable to browse it and after some time automatically cube is getting online.I am unable to figure it out what is happening.
FYIP..For every 15 Min cube will be Proccessed Full.
View 8 Replies
View Related
Jul 2, 2015
I am stuck in a situation where I want to use YTD for three different calendars of our company and don't want to create three different YTD calculations. However I want to make this work for any measure not for a particular measure
If I create one YTD and try to use in context of three calendars in SCOPE statements then it does not give my right results. Following is my syntax but It does not work.
SCOPE([Billing Date].[SalesCalendar].MEMBERS);
( [Aggregate].[AGGREGATE CODE].[YTD] )
= Aggregate({[Aggregate].[AGGREGATE CODE].DefaultMember}*{PERIODSTODATE([Billing Date].[SalesCalendar].[Sales Calendar Year],[Billing Date].[SalesCalendar].CURRENTMEMBER) } );
[Code] ....
However if I comment on one of SCOPE statement , other one works but both don't work simultaneously in context of different calendars.
View 2 Replies
View Related
Aug 6, 2015
It seems simple but I'm not able to make it works. I've got a fact table with sites (DimImplantation) with allowed users (DimDroit) :
1 user can have access to many sites. I've tried these codes but only one worked and only in case where the user had only one site :
a) =DimImplantation[DimImplantation_ID]= LOOKUPVALUE (DimDroit[DimImplantation_ID]; DimDroit[Utilisateur];USERNAME() ) ==> worked for 1user with 1 site
b) =DimImplantation[DimImplantation_ID]= LOOKUPVALUE (DimDroit[DimImplantation_ID]; DimDroit[Utilisateur];USERNAME(); DimDroit[DimImplantation_ID];DimImplantation[DimImplantation_ID] ) ==> doesn't work and says 'not able to to identifie the value of DimImplantation[DimImplantation_ID]
in the current context.
c) CALCULATE(VALUES(DimDroit[DimImplantation_ID], SUMMARIZE('DimDroit',DimDroit[Utilisateur]), DimDroit[Utilisateur]=USERNAME()) ==> version for SQL2012. I've had 'End of input reached'.
View 3 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
May 29, 2015
Link : [URL] .....
This provides a good example for my situation. In this example, you will see a Movie dimension with four attributes; Genre, Language, Movie, and Theme. I have a similar setup except mine is Top Level Hierarchy>Categories>Values which are all under the one hierarchy.
My Question: I have the dimension setup as a multi-value parameter in one of my reports. When I filter on a value in Genre and in Language, it provides all values from that genre and all values from that language. I really only want the values that include both.
Genre - Western: Movie1, Movie2, Movie3
Language - English: Movie2, Movie4, Movie5
If I filter on Western and English, I get Movie1-5 when all I really want is Movie2 only. Is there any way to have this do an Intersect within the same dimension or do I have to build each one out into its own dimension?
View 10 Replies
View Related
Jul 27, 2015
1. As per my current development SQL Sever Analysis Database consists of two Cube (Cube A and Cube B). Cube A and Cube B share the same data source view (DSV1). The source for both these cubes has the same data source (DS1).
2. As per the requirement I need to create third Cube i.e. Cube C. Is it possible to create a second Data Source View (DSV2). The Source of second Data Source View (DSV2) will be the same data source(DS1).
I am thinking to create second Data Source View (DSV2) for Cube C because existing layout of the DSV1 has become complex. I wanted to know the pros and cons of creating a multiple data source view with same data source
View 3 Replies
View Related
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
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
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
