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
We have a fact table where one of the measures in that fact table is invoice balance. Some of our reports requirements list that they only want to see the sum of those invoices where the balance is greater than X dollars. How do I go about filtering calculations based on a measures value?
View 3 Replies View Relatedi 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 ..
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..
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.
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.
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 RelatedWe 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"?
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.
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?
I am using
SSAS: version 2008
Excel: version 2013
I am connecting to SSAS cube from Excel and I have date dimension with 4 fields (I have others but I don't use it for this case). I created 4 fields in order to test all possible scenarios that I could think of:
DateKey:
- Type: System.Integer
- Value: yyyyMMdd
Date:
- Type: System.DateTime
DateStr0:
- Type: System.String
- Value: dd/MM/yyyy (note: I am not using US culture)
- Example: 01/11/2015
DateStr1:
- Type: System.String
- Value: %d/%M/yyyy (note: I am not using US culture)
- Example: 1/11/2015
Filtering on date is working fine:
Initially, in excel, filtering on date was not working. But after changing dimensional type to time, and setting DataType to Date, as mentioned in [URL] filter is working fine as you can see in the picture.Grouping on date is not working:
I have hierarchy in my Date dimension and I can group based on hierarchy, no problem. But user is used to pre-build grouping function of excel, and he wants to use that. Pre-build functions of Excel, Group and ungroup seems to be available as you can see in following picture:
But when user clicks 'Group', excel groups it as if it is a string, and that is the problem. User wants to group using pre-build grouping function available in Pivot table. I also find out that Power Pivot Table does not support this excel grouping functionality. And if I understood well, this pre-build grouping functionality of excel, needs to do calculation at run time, and that is not viable solution if you have millions of rows. So Power pivot table does not support pre-build grouping functionality of excel and hence we need to use dimension hierarchy to do the grouping. But I am not using Power Pivot table, I am using simple Pivot Table. So I expect grouping functionality to be working fine. Then I tried to do simple test. I created a simple data source in excel itself. And use it as source of my Pivot table. Then grouping is working fine. The only difference that I can see is (When double click the Measure value in Excel),For date values of my simple test, excel consider them as 'Date'.
For date values of my data coming from cube, excel consider them as 'General'
2.1. But value here is same as it was in simple test.
2.2. 'Date Filter' works just fine.
2.3. If I just select this cell and unselect it, then excel change type to 'Date' though for that cell.
2.4. I have created 4 different types of fields in my date dimension thinking that values of attribute of my dimension might be the problem, but excel consider 'General' for all of them.
2.5 This value (that can be seen when double clicking on measure) comes from 'Name Column' of the attribute. And the DataType defined is WChar. And I thought that might be the reason of issue. And I changed it to 'Date'. But SSAS does not allow it to change to 'Date' giving error : The 'Date' data type is not allowed for the 'NameColumn' property; 'WChar' should be used.
So, I don't know, what is the puzzle piece that I am missing.
1. Date filter works, group does not work
2. Excel consider it as 'General' string.
3. SSAS does not allow to change 'NameColumn' to Date.
how can I have a default filters in my Excel PowerPivot Table, without the user need to select them in Excel creating with DAX Language.
View 8 Replies View RelatedSay 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.
I've got a dimension built from a fact (whatever that's called?) ... it's a date interval field, i.e. 0-5 weeks, 6-10 weeks 11+ weeks. How do I sort these members in the respective order? Looks like this currently:
The problem lies in the fact that I don't have any secondary attributes to order it by, i.e. it's not a physical dimension where I can use a key for the 3 members. I was hoping I wouldn't need to create a separate dimension to get round this.
I am using sql server 2005 enterprise edition.
How to list all the dimension and fact column names with mdx or tsql query...
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 ?
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 RelatedI'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?
I have a Fact table that contains several degenerate string values that I have pulled into a Fact Dimension.
When I browse the cube and cut one of the measures by an attribute from the Fact Dimension, I am getting incorrect data.
In other words, when I query the fact table directly via SQL and apply the same filters, I see the data I am expecting to see. But cube browse with same filters yields different results.
How can this happen since the fact dimension has a 1:1 relationship with the fact table.
I do have the Dimension Usage configured properly.
Is this an aggregation thing? Attribute key thing? What am I missing?
I would like to know the best practice for running analysis service in terms of port usage. Is it better to run on a specific port or have dynamic ports ? We have clustered servers that run default on 2383 but not sure with non clustered what's the best way to get performance.
View 2 Replies View RelatedI want to find out if it is possible to create a role in a cube without an AD account, e.g. using a GMail email address.
View 3 Replies View RelatedI'm having what i think it's a strange error in CALCULATE function:
Calculation:=CALCULATE( DISTINCTCOUNT([SK_CUSTOMER]); 'fact x'[_sales net]<>0)
where _sales net is a calculated column, sum of 3 existing columns lets call columnA , columnB, columnC
This calculation doesn't work well, the results seem to be like as if _sales net was only equal to columnA.
Naturally I've already done build deploy and full process.
I need to conditionally include a filter in the below DAX query such a way that if an Account_ID is passed (as client side parameter) the filter should be included in the query otherwise if it is zero, the query should return results across all account. For example, if total purchase is queried for Account_ID=2 the query should be like this:
evaluate
calculatetable (
addcolumns (
summarize (
'Event'
[code]...
but if the Account_ID is passed as zero, condition 'Event'[Account_ID] = 2 should be removed.Is this possible to implement in DAX?
I am trying to get a list of years from a Date dimension that includes a limited number of values. I want the current year and the previous 5 years. The year number in the cube is actually a char(4) value so I will have to cast that.
Also I want to alias the field, since Im using this list to use as a parameter in an SSRS report.
The MDX I have at the moment is this:<br/>
WITH
MEMBER [SALESYEAR] AS
CInt([DimCalendar].[Year].CURRENTMEMBER)
SELECT
NULL ON COLUMNS,FILTER(
[DimCalendar].[Year].MEMBERS , [SALESYEAR] > 2012) ON ROWS
FROM [MySalescube]
The above returns empty, but I also need to define an expression that is the current year - 5 years
Hello,
Here is my problem:
I use SQL Server 2005. I have approx. 50 tables in my database and 30 of them have a filed named "CompanyID". Example:
create table A (ID int identity, NAME varchar(100), COMPANYID int)create table A (ID int identity, REF_ID int, FIELD1 varchar(100), FIELD2 varchar(100), COMPANYID int)
Also there are nearly 200 stored procedures that read data from these tables. Example:
create procedure ABCasbegin /* some checks and expressions here ... */ select ... from A inner join B on (A.ID = B.REF_ID) where ... /* ... */end;
All my queries in the Stored procedure does not filter the tables by CompanyID, so they process the entire data.
However, now we have a requirement to separate the data for each company. That means that we have to put a filter by CompanyID to each of those 20 tables in each query where the tables appear.
Firstly, I put the CompanyID in the context so now its value is accessible through the context_info() function. Thus I do not need now to pass it as a parameter to the stored procedures.
However, I don't know what is the easiest and fastest way to filter the tables. Example:
I modified the above mentioned procedure in the following way:
create procedure ABCasbegin /* some checks and expressions here ... */
-- gets the CompanyID from the context: DECLARE @CompanyID int; SELECT @CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))
select ... from A inner join B on (A.ID = B.REF_ID) where ...
and A.COMPANYID = @CompanyID and B.COMPANYID = @CompanyID /* ... */end;
Now I have the desired filter by CompanyID. However, modifying over 200 stored procedures is rather tedious work and I don't think that this is the best approach. Is there any functionality in SQL Server that can provide the possibility to put an automatic filter to the tables.
For example: when I wrote "SELECT * FROM A", the actual statements to be executed would be "SELECT * FROM A WHERE CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))".
I was looking for something like "INSTEAD OF SELECT" triggers but I didn't manage to find any answer.
I would very grateful is someone suggests a solution for something like "global table filter" (that will help me make an easy refactoring)?
Thanks in advance.
Best regards,
Beroetz
Hello,
In our project, we would like to use the same data source for our analysis service database cubes and for our reporting service reports.
I created the analysis service project first, deployed successfully. When trying to setting up the data source in the report model project, I selected the "create a data source based on another object", and then selected the "create a data source based on an analysis service project". However, there is no analysis service project to select, and no browse button to see where the reporting service is looking for analysis service project either.
I have tried creating a new analysis service project with data source views, cubes, dimensions and all the stuff, but still cannot see the analysis service project in the drop down box to be selected for my reporting model project data source.
As I am fairly new to the reporting service, I'm sure I'm missing something, but couldn't find much information in the help or on the web. Any suggestion would be much appreciated.
Thank you very much,
Annie
For example, I have a Date dimension with attributes like Current Day and Current Month. If I run the following, I get exactly what I expect: a list of the days in the current month.
select
NULL on 0,
[Case - Date - PSPT Entry].[Year - Quarter - Month].[Date] on 1
from [Customer Support]
where
[Case - Date - PSPT Entry].[Current Month].&[True]
When I run the following, I'm getting a list of the days in the current month *plus the first couple days of the next month*. with
set [Days of Interest]
as
filter([Case - Date - PSPT Entry].[Year - Quarter - Month].[Date], [Case - Date - PSPT Entry].[Current Month].&[True])
select
NULL on 0,
[Days of Interest] on 1
from [Customer Support]
I'm trying to create a percentile rank function based on the standard WIKI version:
I've seen Brian Knight's article here, but that only deals with percentile.
Where I'm struggling is getting the count of members in a set using a measure, in the current context on the same hierarchy, as the filter expression. I'm using the comparative set as in belonging to the same geographical location, and therefore associating by another attribute.
So, cl as below:
MEMBER [Measures].[RegionPercentileCount] AS
Count(
Filter(
NonEmpty(
descendants(Ancestor(
[Supplier].[NameMap].CurrentMember,
[Supplier].[NameMap].[Region]),
[Supplier].[NameMap].[Supplier Id]),
[Measures].[ActiveMeasure])
, [Measures].[ActiveMeasure] <
([Supplier].[NameMap].CurrentMember,
[Measures].[ActiveMeasure])))
Using the same measure and context hierarchy is always going to be equal, and therefore the count is always zero. Its almost as if I need a nested context for the FILTER which allows me to use enumerate the set on the same hierarchy whilst maintaining the external reference.
I'm thinking that perhaps I'm going to have to create another hierarchy and use that as the filter set and reference through StrToMember or similar.
I have an existing MDX query returning the correct resultset. However, I want to add a filter so that for a combination of value in Hierarchy1 and Hierarchy2, the data is filtered out.
For example I have data like
H1 H2 Amount
1 1 100
2 1 50
3 1 45
I am getting a value of 100+50+45=195.
I want to filter the data for the combination H1=3 and H2=1. Expected result would be 100...
H1 H2 Amount
1 1 100
2 1 50
I have been living in the T-SQL world for years, and am working on my first Tabular model.
I have a fact table that I would like to use for many reports. My idea is that I would use Perspectives in the tabular model to pull in the columns that I need for each purpose. In some cases, I also need to filter out some rows. Is there a way to filter perspectives? The only solution I am coming up with is to create views in SQL Server and import the fact table multiple times for each purpose. That doesn't seem very efficient.
My requirement is to count the customer order number for premium order type orders which has some order quantity.I am using below MDX
count
(
Filter
(([Customer Order].[Dim Customer Orderkey].[Dim Customer Orderkey].members,
[Outbound Order Attributes].[Order Type].&[P]),[Measures].[Ordered, pcs]>0 ) ,
)
The result is accurate but the query execution time is 3-4 minutes for 10 fact records, when i use multiple dimension. it is showing me 0 valus for this measure for all the members for the dimesion attribute which doen't have any customer order. example it shows all the member of date dimension. is there any way to reduce the rows. i think this is the reason to take more execution time.when i use EXCCLUDEEMPTY the result is NULL
count
(
Filter
(([Customer Order].[Dim Customer Orderkey].[Dim Customer Orderkey].members,
[Outbound Order Attributes].[Order Type].&[C]),[Measures].[Shipped, pcs]>0 ) ,
EXCLUDEEMPTY)
We have two measures
[Measures].[NSPO]
is in FACT1 Measure Group
[Measures].[NetSignedPremium] is in FACT2 Measure Group
The requirement is Measure.[NetPremium] should be the greater of
[Measures].[NSPO] OR [Measures].[NetSignedPremium].
All three measures are created in the cube and this logic is to be applied in the Calculations section of the cube.
Using an IIF statement like one mentioned below does the job, but data does not aggregate correctly for the
Measure.[NetPremium]
Measure.[NetPremium] = IIF ([Measures].[NetSignedPremium] > [Measures].[NSPO], [Measures].[NetSignedPremium] , [Measures].[NSPO])
To ensure that the columns are aggregated I have to use a SCOPE statement at the leaf level
[01 UWR Reference].[UWR Reference].Members
SCOPE(
FILTER( NONEMPTY( [01 UWR Reference].[UWR Reference].Members ) ,
( ( ([Measures].[Net Signed Premium Rcvd])
- ( [Measures].[NSPOTest] ) )
< 0 ) )) ;
This = [Measures].[Net Signed Premium Rcvd] ;
END SCOPE;
My problem is the FILTER within the SCOPE statement does not work. How to modify the filter in SCOPE? OR How to make IIF Aggregate correctly?
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.