I have 3 partitions using a year grouping. Current year, previous 4 years, older than 5 years. I have two measure groups, one is a distinct count, so I actually have 6 partitions.I also use usage based optimization to build my aggregations. Should each partition have a separate aggregation or should there be one for each measure group?
View 5 Replies
Hi,
Can anyone please help me.....
how to design aggregation for new partition using XML or AMO ???
I know how to clone partition using AMO(I guess it will take care of aggregation) and set AggregationDesignID in XML.
But, want to know how to design aggregation for new partition using XML or AMO....
Thanks
Nisanth
I have a fact, like this
DimCustomerKey, DimCardKey, Amount
1, 1, 20
2, 3, 30
1, 1, 40
3, 4, 50
2, 3, 50
I want a report like this
1, 1, 40
2, 3, 80
3, 4, 50
Logic :
- Each DimCustomerKey has only 1 DimCardKey
- If DimCardKey = 1, reported amount = max(amount)
- If DimcardKey other than 1, reported amount = sum(amount)
How can I implement this with MDX script ?
How can I implement this inside the CUBE (using scope or calculated member etc...)?
I want to display my problem. I have a cube that connected to hive DB through views. There are some changes that apply to some related tables on hive. This changes reflect on the cube so i make full process for the cube. I want to process only partitions that have been changed without full process. I detect changes on this table on another table on the local database.
View 3 Replies View RelatedSQL 2014 I've inherited a db that has several partitioned tables. They are partitioned by month. We're approaching the last partition, 11-30-2015, so we need to extend the tables. My question is how do I do this? There are Partition Schemes and Partition Functions setup at the db level. I've figured out how to ALTER those. Next I go to a table that I know is partitioned, right-click Storage and select Manage Partitions.
My only option is to "create a staging table for partition switching". Not knowing what switching is, I'm not sure if this is what I want to do. All I want to do is add new partitions to the table - and remove some of the old ones since they are empty due to archiving of data.
So, what is the proper steps to adding new partitions to a table that is already partitioned?
I trying to update my Aggregation Design for a partition using BIDS Helper. The current aggregation design contains about 60 aggregations and the new aggregation I am trying to add is across 5 dimension attributes, the product of which is about 500,000 unique values. The fact table is about 13,000,000 rows.
When I deploy the aggregation and run ProcessIndex, I get the follow error:
File system error: The following file is corrupted: Physical file: ?E:Program FilesMicrosoft SQL ServerMSAS10_50.MSSQLSERVEROLAPDataTestDB.14.dbcube_2.607.cub
cubemeasuregroup_2.633.detDefaultPartition_2.579.prt852.agg.flex.data. Logical file.
If I remove the new aggregation, deploy, and run ProcessIndex again, it processes fine.
Is there some file size limitation I am running into? The agg.flex.data file is 7.8 GB before adding the new aggregation, so it isn't subject to the same 4 GB limit as .asstore.
Windows Server 2008 64 bit
SQL Server 2008 R2 (10.50.1746.0) 64 bit
I have a monthly time period dimension representing average number of students for each month. At the yearly aggregate level I don't want it to sum up the avg number of students from every month because that number is incorrect. I would like it to use the number of students from the most recent month as a roll up. Is that possible to configure in SSAS?
View 2 Replies View RelatedI have defined a stored procedure with one parameter. With this parameter I'm able to controll which year of the sales amount data should be selected. This works fine.
Now I want to implement this stored procedure as the source of the partitions. But if I do this I get an error. The syntax-check says, that everything is fine. But if I want to calculate the partition with this command: "exec dst.fact_umsatz_year 0" get the following error (in German):
OLE DB-Fehler: OLE DB- oder ODBC-Fehler : Falsche Syntax in der Nähe von ')'.; 42000; Falsche Syntax in der Nähe des exec-Schlüsselworts.; 42000.
Fehler im OLAP-Speichermodul: Fehler beim Verarbeiten der FACT Umsatz Pivot View-Partition der Anzahl Kunden-Measuregruppe für den Vertrieb-Cube aus der OLAP AS-Datenbank.
I have set the slicers on paritions using Script task.
After cube process, the mdx queries are not hitting partitions as per Slicers.
But once i open each partition slicers from SSMS and close it by doing ok. My mdx query is good enough to hit the relative partition only.
But it is not possible to manually refresh each slicer on partition.
Is there any way out to solve this issue. I am using 2008 R2.
I have a question about the storage design wizard in the analysis manager.
We are working with different seasons in our reports and every week we update the data of the seasons in our cubes. But as seasons end, and at some point the data for old seasons doesn't change anymore, I don't think it is necessary to update every season every week (which we now still do now for seasons in, for example, 2004). It's a waste of time. So my question now is. How can I storage the data of previous seasons (and work with them in the report) and still be able to update the current season? Can I use the storage design for this??
I have a (hopefully typical) problem when it comes to cube design. Westore millions of product records every year, broken down bymonth/quarter. Each product can be assigned to various heirarchialclassification groups etc. The data in an OLTP DB occupies roughly100G for a typical year.We're looking at breaking this out into OLAP to provide faster accessto the data in various configurations and groupings. This is not aproblem, as this is the intended use for Analysis Services.The problem is that we apply projection factors on the product pricesand quantities. This would be ok if it only happened once, however,this happens every quarter (don't ask why). The projection factorschange 4 times a year, and they affect all historical product records.This presents a challenge because to aggregate the data into a usefulconfiguration in the cubes, you throw out the detail data, but thismeans throwing out the price and quantities which are needed to applythe projection.So if you have Product A at $10 and Product B at $20, and roll both upinto Category X, you'll have $30, but you'll lose the ability to applya projection factor of .5 to Product A and .78 to Product B. They'rerolled up.I don't want to regenerate the cubes every 3 months. That's absurd.But we can't live without the ability of projection theprices/quantities on a product level (detail level). So how can thisbe achieved when the other cubes are created at a higher level withless details and sums of the detail data?My initial guess is that we have to update the product data, and thenreaggregate all the other data that is built upon that product data.Is there any other way to apply math to the data on the way out?Thanks in advance!Regards,Zach
View 2 Replies View RelatedI have a cube. Its xml is different at some point than its design view. Suppose for some dimension and its attributes, source table is different than what it showing in the properties window for them..
Is this possible? How to read cube xml because there are repeating tags in it. There are two type if dimension tags.. one has only attributed and other has all properties.
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.
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'm using an Aggregation task to summarize an input file by item and week before inserting it into a SQL table.
Two of the fields I'm summing, because their totals per record can occasionally exceed 32k, are defined as int (I4) instead of smallint (I2). However, the summarized total never exceeds the value an int can hold.
I ran into a problem on the insert, however, with SSIS telling me it couldn't insert an I8 value into an I4 table field. I discovered the metadata for the summed totals had automatically been set to bigint (I8), and the mapping was failing.
I didn't see a way to change that metadata within the Aggregation task itself, so I added a Data Conversion task to convert the totals to four-byte signed integers and enable the mapping. Was that the proper workaround?
Does anyone have any statistics on the performance gains one can get using raw partitions. The database in question is very IO intensive and performs about 1,000,000 inserts/updates per select.
Thanks...
Here is what I want to do.
I have a database with HotelInfo
hotelid
hotelname
hotelstate
hotelcity
hotelphone etc etc
I want to display all hotels I have grouped by state
SELECT hotelname, hotelcity, hotelphone
FROM HotelInfo
GROUP BY hotelstate
So basically I was For eg.
TX
hotel1 Dallas xxx-xxx-xxxx
hotel4 Plano xxx-xxx-xxxx
CA
hotel2 San Fransisco xxx-xxx-xxxx
How can I group by without Aggregation ?
Hi.
i have a problem that related to aggregation functions.
i have a table (requests)that consist with the following fields:event_id, request_type,data.
i want to present a report that for each event_id it will show the number of records where the request_type value is:"VMR".
i wrote the following query-
SELECT event_id, Count(request_type)
FROM requests
WHERE ((requestType)="VMR")
GROUP BY event_id
the problem is that:if event_id don't has a record with the value "VMR" it will not be showen in the report and the goal is to present those kind of events with the value 0.
who can i make it happen.
thanks.
Can anyone help me with an problem I have come across in my databasedesign.I have a primary table and a related table with 3 child records (eachwith a numeric field). I require a query to return the primary keyfrom the main table and the PRODUCT (i.e. all numeric valuesmultiplied together) of the three child records, much like a SUM wouldadd them together.Any help would be gratefully received!Tony.
View 3 Replies View RelatedHello,
I have a matrix where I count number of employees per country, per product line.
The calcul is a simple sum :
(sum(Fields!ID__T_E_Employee_Distinct.Value)).
My report is correct at week level:
March 2007
W9 W10 W11 W12 W13
Product_Line1 17 17 17 17 17
Country Product_Line2 4 4 5 5 5
Product_Line3 25 25 26 25 25
Product_Line4 12 12 12 12 12
However my report is false when I drill-up to month level.
I obtain:
March 2007
Product_Line1 85
Country Product_Line2 23
Product_Line3 126
Product_Line4 60
but I want to obtain:
March 2007
Product_Line1 17
Country Product_Line2 5
Product_Line3 26
Product_Line4 12
For information, my datasource is an OLAP cube.
I have tried to add scope in the sum function but it doesn't work...
Moreover the sum is mandatory for me at week level, but I can have max of the sum at month level.
How to do max of sum in SSRS (I can have only one aggragation function in an expression).
Any help is welcome.
Thanks.
Guillaume.
Hi,
I am new to the reporting services and I've been working on problem in one of my reports all day long and after 8 hours of frusturation I decided ask for a profesional help.
Ok here is my problem: I have a report that calculates the amount of meetings with our clients. The dataset contains an activity_id field that we assign for each our meetings with our clients. SSRS counts these meetings and shows it in a drilldown enabled report. Everything seems fine on the report except that someof the activities involves few different clients and SSRS is not counting the activities multiple times in region drilldown as there is only one activity id associates in that region even though it contains different companies. And I want those companies to be calculated in too.
From the crude drawing below I wanted to explain my dilemma visually. As it can be seen the total number of meetings we had is actually 40. But as we had 3 activities that involves more than 1 clients it only gives 37 as a count. I would like to know is there a way to make the report count the same activity multiple times if activity_id is associated with more than one clients.
I hope I managed to explain my problem
**********************************************************************************************
Manager Region Market Company Meeting Detail
+Manager 1 (9 meetings)
+Manager 2 (37 meetings)
- West (37 meetings)
-Denver (37 meetings)
+Company 1 (5 meetings)
+Company 2 (2meetings)
+Company 3 (2meetings)
+Company 4 (3meetings)
+Company 5 (0meetings)
+Company 6 (0meetings)
+Company 7 (5meetings)
+Company 8 (1meetings)
+Comapny 9 (19meetings)
+Company 10 (3meetings)
Total (40 meetings)
I know we can design Aggregation usage for a dimension within a cube. For example, you can choose Full , None, Unrestricted and Default method. I assume this is the Global settings.
Is it possible that Ican do the same thing for the attributes within a dimension? I cannot find such property in attributes. Does this means we can't control which attributes we want Analysis Services to aggregate in a dimension?
Hi all
i need to create aggregation table from 2 tables group by date, any one have any idea how to create it by using SSIS
thanks & regards
I'm having problems implementing the following in reporting services 2005.
My hierarchy looks like this (just to illustrate the problem...):
University->Student->Exam
My query returns the following fields:
University,Student,StudentPayment,ExamName,ExamScore
I need to create a report that will show the hierarchy and to smartly aggregate the StudentPayment to both the Student and the University levels.
The problem is that the StudentPayment field is being multiplied by the number of exams in the upper level aggregation.
If only I could set the granularity level of the StudentPayment measurement...
Note that I don't have access to the query, so I can't change anything on that front.
Thanks,
Efi
Hi,
I have the following three tables :
Account (Id int, AccountName nvarchar(25))
Role (id int, Rights int)
AccountRole (AccountID, RoleID)
In Role table - Rights Column is a bit map where in each bit would refer to access to a method.
One account can be associated with multiple roles - AccountRole table is used for representing the N:N relation.
I want to develop a store procedure - which would return all AccountName and their Consolidated Rights.
Basically I want to do a BitWise OR operation for all the Rights in the Aggregation instead of the SUM as shown in the following statement.
SELECT Account.Name, SUM(Role.Rights) FROM Account WITH (NOLOCK)
JOIN RoleAccount ON RoleAccount.AccountID = Account.Id
JOIN Role ON RoleAccount.RoleId = Role.Id
GROUP BY Account.Name
Thanks,
Loonysan
Please help!
I have a server that has SQL Server installed on both C and D drives. The SQL Server software is currently running from the C drive and the live databases and backups are stored on the D drive.
I need to have everything on the D drive. Is there an easy way to make the registry point to the D drive without reinstalling SQL Server? The software will needs to run from the D drive because the C drive is running out of disk space. I will also need to delete the whole C:mssql directory.
Thanks.
assuming that you have two databases, the OLTP db and the OLAP db (take not that both have the same structure -- archiving purposes)... using table partitioning, is there a way where we can move 1 partition from the OLTP db to the OLAP db???
i'm actually trying to use this example with both tables in the DB.. I tried to modify to use two databases but sql server is unable to move the partition...
ALTER TABLE [Production].[TransactionHistory]
SWITCH PARTITION 1
TO [Production].[TransactionHistoryArchive] PARTITION 2;
SlayerS_`BoxeR` + [ReD]NaDa
Hi experts,
In SQL Server 2005 database we have partitioned a very big table into 30 partitions each holding few million of records.
Im just curious to know whether there are some configuration related to processors or system hardware in order to benefit from partitioning ? (Ex : If we have multiple processors Whether they need be configured to do a parallel processing ? )
Any real time experience (other than referring links) would be really helpful for me.
Thanks in advance,
Hariarul
Hi There,
I have a quick question regarding merging cube partitions.
If I create partitions sliced by date (let's assume we have year level partitions like 2006,2007,2008...)
Later, if I want to merge selected partition to another partition , for example I have history partition and 2006 partition and I want to merge 2006 to history partition then I can simply merge them using ' merge partition' through Management Studio.
My question is that in script, History partition has condition which is where clause and restricted by year level (i.g. WHERE date < '01-01-2006' ) ; however after merging , script won't change like WHERE date <'01-01-2007').
If so, whenever I merge partitions then I have to alter the script as well based on selected merge partion?
I need to refresh history partition once a month;however even if I merge 2006 partition , once I reprocess history partition then it only process what it was wrote in script. So, after reprocessing 2006 data won't appear in this history partition.
So, wondering it's mentatory to alter the script once partition is merged.
Please give me some comments on this issue.
Thanks in advance.
I have a table imported from a legacy Oracle database that stores values vertically in name/value pairs. I store it in table-type variable that is an exact copy of the structure:
DECLARE @OracleEngData TABLE
( DataSourceCHAR(8)
, [OMNI_NUMBER] INTEGER
, [TIMESTAMP] INTEGER
, [DATA_TYPE] NVARCHAR(24)
, [PARAMETER] NVARCHAR(32)
, [PARAMETER_VALUE] NVARCHAR(132));
If this information were pivoted horizontally: OMNI_NUMBER would be the primary key.
TIMESTAMP is a 10-digit integer that represents the number of seconds since 1/1/1970 UTC that requires additional conversion. DATA_TYPE is not the data type. It is a general categorization of the next two columns.PARAMTER would be the column headings if it were horizontal..PARAMETER_VALUE would be the data value in that column.
I would like to try to use PIVOT to list the PARAMETER column values as column headers. This seems to work fine. What's confusing me is that I'd like it to list the PARAMETER_VALUE column values as raw data, just as it is in the source version, without having to apply some sort of aggregate function to it. Here's a CSV sample of the data you can paste into Excel. I'm trying to transform this:
OMNI_NUMBER,TIMESTAMP,DATA_TYPE,PARAMETER,PARAMETE_VALUE
506026,1413240436,test_data,cnr,211250000,54.8
506026,1413244259,test_data,cnr,211250000,53.2
506026,1413244679,test_data,cnr,211250000,53.1
506026,1413309646,test_data,cnr,211250000,53.4
506026,1413315987,test_data,cnr,211250000,53
[code]...
But I don't want the sum of the values or the average of the values, just the values. The PIVOT syntax seems to require an aggregate operation.
Hello,
I have a Sales cube and I want to be able to display Products and the
Price at which they were purchased in a given period. I have a
Product dimension while Price is a measure in my Sales Fact Table. Is there a way to have a "Group By" aggregation instead of a Sum? This way, I can show Products grouped by their list price.
My Product Dimension consists of product_numbers (such as 100, 101,
102 etc...)
My Sales Fact Table consists of sales data (such as product_key,
price, net_sales, returns, etc...) at the transaction level.
I want to be able to view the data like this:
Price Net_sales Returns
Product
100 $5.99 $2005 $320
101 $3.51 $7110 $998
where Net_sales and Returns are "summed" and Price is simply a "Group
By". In other words, this report would show the net sales and returns
by product for a given price.
I'd rather not use a Price dimension since we have hundreds of
products at hundreds of different prices. Moreover, this data is
already in the Sales Fact table.
Thanks for any help provided
I am trying to port a database from SQL Server 2005 Express to SQLServer 2005 Standard. Idetached my database from the SQL Sever 2005 Express instance andattached it to the SQL Server 2005 Standard instance. Whenever I do aselect on tables in the database I have no problem. However if Iattempt to open or modify a table by selecting open or modify from thepopup menu, I get the following error --TITLE: Microsoft SQL Server Management Studio------------------------------Class does not support aggregation (or class object is remote)(Exception from HRESULT: 0x80040110 (CLASS_E_NOAGGREGATION))(Microsoft.SqlServer.SqlTools.VSIntegration)------------------------------BUTTONS:OK------------------------------I tried repairing the .NET 2.0 framework but to no avail.Please help!!!Thanks,
View 3 Replies View RelatedHello, I have a resultset as follows:fields: Name, RankIDvalues:Prorduct A, 4Product B, 33Product C, 221(etc)Name is always unique. RankID may not be.I want to take that result set and basically pivot it to have the Namevalues as columns, and the RankID of each one as the data. So youwould end up with only one row like:Product A | Product B | Product C | etc4 | 33 | 221 | etcIs this possible? I do not want to sum the data or anything, simplyrotate it sort of.Any advice is appreciated.
View 3 Replies View Related