I can add a new logical table to the SALES fact in Business model but is it possible that in the WHERE clause i can write a sql like
customer_key in (Select customer_key from New_customer)
create a row wise initilization variable with the SQL (Select customer_key from New_customer) and add it in Content level of Logical table
Customer Key = VALUEOF(ROW_WISE_initilization_var)
Need more detail about New_Customer and Customer table like at what time New_Customer would be treated as old customer so that we can find him in Customer table.
I'm not sure about your design in BMM but throwing suggestion:
New_Customer and Customer are as LTS
create a flag using New_Customer like
case when New_Customer.Customer_key is not NULL then 'Y' else 'N' end
in your report use this flag to identify (force to keep in where clause) records from New_Customer table.
If helps mark
Remember, NEW_CUSTOMER table has list of new customers for each month. So (Select customer_key from New_customer) will give a list all records in the table. I need the new customers totals for a specific month which is selected when the user is performing analytics.
I need a calculation column 'NEW_CUSTOMER_SALES' in my sales folder in BMM layer. I cannot touch the Customer folder since the NEW_CUSTOMER data is based on each month.
Pls. forget how the NEW_CUSTOMER data is calculated. Its in the table.
select sum(sales_amount) from sales where customer_key in (select customer_key from NEW_CUSTOMER where month_key=jan2013)
the above query will give me the result from sql.