CREATE VIEW...
Select 'Due_0-1_Month' as Ageing_Threshold
union all
Select 'Due_1-2_Month'
union all
Select 'Due_2-3_Month'
[Code] ....
I was successful in processing the cube, however the problem is everytime I drag the dimension on the columns field in Pivot tables the Thresholds start to break up the other amounts that I have on display like Acquisition Costs, Tax amounts. I am only interested in showing the breakdown of Premium amount measure by the Threshold dimension.
somehow 'Hide' or 'prevent' the Threshold dimension from breaking down the other measures on the Pivot and only breakdown the amounts for Premium?
how I should structure my tables in SQL or any MDX queries to resolve this.
For Example: I have one dimension named as "Name", Under this I have "FirstName" and "LastName" Attributes are there.But when i drag "Name" dimension, By default "First Name" dragged. But i Want "Last Name" should drag.
I have a dimension like Districts, Under that 2 Attributes are there i.e,District ID and Districts. When i drag Dimension "Districts", in OLAP grid it come District ID first. But i want Districts to drag first. How can we sort Attributes(District ID and Districts) for a dimension.
I have a fact table that has terminations. Fields include EmployeeName, TermDate, TermReason, and HireDate, et al.
I need to make EmployeeName available to drillthrough, and since it's a varchar field I can't make it a measure, so it has to be a dimension attribute. My question is, should I leave the fact table as it is and use SSAS to create a dimension that contains only EmployeeName and the link to TerminationID? Or should I redesign the OLAP tables so that EmployeeName is in a separate table?
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?
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 created SSAS cube in VS 2008 and have been able to deploy it successfully to the server. While creating the cube I was able to browse dimensions and all underlying tables just to make sure it has data. After deploying successfully when I drag and drop any measure group to browser it does not display anything.
The only thing I did different from straightforward cube building process was that when I created those measure groups the partitions that were created by default were giving me some unknown errors so I had to delete them in order for cube to process successfully.
Did that made any difference because I thought partitions are for improving query performance and has nothing to do with cube processing errors.
We have hidden few measure groups in cube for time being, where Users can browse the cube with Excel pivot. But, All these measures can be seen from Excel pivot in 'Show fields related to' drop down.
Need to remove the hidden measure groups from showing in Excel pivot and to remove 'All' option in 'Show Fields related to', So that users may not get confused by seeing all the measures. Can we achieve this.
how to move the dimension attributes from currency to geography and vice versa(i.e need to change their positions) in SQL Server 2012. i need currency to be placed in top of geography or geography below currency.
I'm using a DW from Northwind database to build a cube to do some analitical taks. I already create the cube and now I am "cleaning" the dimensions. I'm having some difficults to understand the logical off this part. The reason is that When I create the Data Source View, I only import the Foreign Keys that connect the Dimensions to Fact_Table. I have to drag the attributes of Dimension from Data Source View to the tab attributes?
Imagine this:
I have the following dimension:
Dim_Customer: Customer_ID Name_Customer Job_Function Date_of_Birth Contact Address City Country
When I create the cube only Customer_ID appears in attributes tab, it's normal?
One more question:
I don't want to create a hierarchy like:
Customer ID -> Name_Customer Customer ID -> Date_of_Birth Customer ID -> Address Customer ID -> City Customer ID -> Country
My idea is to create the following hierarchy:
Name_Customer -> Date_of_Birth -> Address -> City -> Country
But the first hierarchy that I show is always appears to me. Do you know what is happens?
I am working on SQL 2012.We have a SSAS Cube build. On top of it client use Excel to connect to SSAS Cube and See the reports.My Cube process every hour and take almost a 1-2 min to Process.When ever End user/Client See or refresh the Excel report (Which use Cube as Source),WHEN CUBE IS PROCESSING ,they get an error that Source is not available
We Have tried best but cannot bring down the Processing time of the cube to 3-5 Second , so that End user don't face report refresh issue at the moment of cube processing
Requirement :In Case End User see the report while Cube is processing from back end , Instead of Error they should see some customize Msg which we can provide some thing Like "Data is Refreshing , please wait ".
All I get back is an error message of "Analysis Services Processing Task Error: A Connection cannot be made. Ensure the Server is running" The server is running, I can process the cube by connecting to the AS instance and right-click processing it.
I can process the cube by running the SSIS task inside of SSDT Just when I deploy the SSIS package (in Project mode) and then execute it do I get the error message.
SQL Server, SSAS, and SSIS processes are all running under the same account. SSAS is on a separate server from SSIS and SQL if that matters.
When 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.
Now I have a different constellation: Integration Services run on one server, in version 2014, the Analysis Services instance to process the cube database on runs on another server, version 2012.I tried several different combinations of SSIS version and Analysis Management Objects version, and got several errors while running the process package (e.g. object reference not set to an instance of an object, cannot find AnalyisServices.dll..)
Is this combination 2014/2012 possible at all?I assume the BIDS version has to be for SQL Server 2014, as I want to run SSIS packages on a 2014 server, is that correct? Does it matter at all, can I also deploy 2012 packages?Which version of Analysis Management Objects do I have to use? I assumed I have to use version 11.0 here, because I want to process a 2012 cube?If it is possible to use the "old" 11.0 version of AMO, do I have to do anything so that it can be found by the SSIS package running on the server (it was built on my local computer, there I have all SQL Server versions from 2005 to 2014 installed in parallel), or do I just have to copy it to the appropriate SQL Server folder?
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?
How can we select translated text of cube translations, like measures, dimensions, dimension attributes. I have below query which return translations of dimension attribute's memebers
WITH MEMBER Measures.CategoryCaption AS Product.Category.CurrentMember.MEMBER_CAPTION MEMBER Measures.SpanishCategoryCaption AS Product.Category.CurrentMember.Properties("LCID3082") MEMBER Measures.FrenchCategoryCaption AS Product.Category.CurrentMember.Properties("LCID1036") SELECT { Measures.CategoryCaption, Measures.SpanishCategoryCaption, Measures.FrenchCategoryCaption } ON 0 ,[Product].[Category].MEMBERS ON 1 FROM [Adventure Works]
I am getting translations of product category members but I want to get translated text of "Category"?
In SSAS, I want to add a calculated measure and set that measure as the Parent of existing measure. For example I have the measures as A and B from the fact table. Now i want to add a calculated measure as C and set this measure as Parent for measures A and B. How to do that in SSAS.
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,
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,
Now I create datawarehouse for my client, I have SSIS a lot for ETL process, I a problem that some fact table need to be updatetable and there is a lot of data of this, I need some efficent way to load this data to data warehouse. I have read your article about SCD in SSIS (Slowly Changing Dimensions in SQL Server 2005). I think the purpose of SCD for Dimension table. If I have some fact table that need rows to be updatetable can you give me an example, best practice, the efficient way or fastet way to load fact table that can be updatetable? If you have link or link about this problem please reply my email. Thanks My datasource from ORACLE and my datawarehouse in mssql2005
What is the best way to move data from Online system tro data warehouse? I have created 3 dimension tables(product,date and customer tables) and I wanna create fact table and get foreign keys from dimension tables. What is the best method to do that in SSIS?
I am new at SSIS and I am trying to create a Datawarehouse using SSIS. I have the data files as flat files I have the Dimensional Model ready on Paper and Now I need to use the SSIS for the ETL process.
I am trying to figure out how to make dimension tables in SSIS? I mean I want to create the 5 Dimension tables and then create a Fact table out of it but I cant understand where to start? Can any one tell me how we create Dimesion tables in SSIS. Like one of the dimesion tables I need to create uses 2 flat files and is like a flattened dimension, How would I create this in SSIS?
Even if there is any tutorial which shows this step by step do let me know. I would really appreciate any guidance on this.
How @StartDate and @EndDate parameters must added to the MDX query for usage in SSRS data set. strtomember can be used like
SELECT ( strtomember(@StartDate) : strtomember(@EndDate) ) ON COLUMNS FROM [Cube]
How can i specify that sub-select must work on the [Fact A] and [Fact B] rundate? strtomember(@StartDate) does not specify on which attribute this sub select is going to work. Any pointers ?
FROM ( SELECT ( [Fact B].[Rundate].&[2015-01-02T00:00:00] : [Fact B].[Rundate].&[2015-01-15T00:00:00] ) ON COLUMNS FROM ( SELECT ( [Fact A].[Rundate].&[2015-01-02T00:00:00] : [Fact B].[Rundate].&[2015-01-15T00:00:00] ) ON COLUMNS FROM [Cube] ) )
we are having an existing cube in that we need to update with new measures . The Measure groups are added to the cube as linked object. so when we are updating the measure group it is throwing the exceptions as follows..“Errors in the OLAP storage engine: The metadata for the statically linked measure group, with the name of 'SalesActual', cannot be verified against the source object.”
Developing a Retail cube using SSAS 2012. One of the dimension is DimCustomer with SCD type II. Each Customer can be a member or a non-member over a period of time. We have StartDt and EndDt to reflect the membership status.
eg: Joe is a member between 06-01-2014 and 31-08-2014 Joe is a non-member between 09-01-2014 and 01-31-2015 Joe is a member between 02-01-2015 and 04-30-2015 Joe is a non-member between 05-01-2015 and 12-31-9999
Without adding fact row of Joe for each day to reflect the membership status, I want to provide the ability to measure "Active Customers Count" on a given date. There are 2 million customers in the DimCustomer Table.
