hi,
I'm making a cube using SSAS and the data in the cube is updated daily. however, each day I take the data with the date of migration because I want to keep the data as history snapshots. the problem is when browsing the data I want to aggregate only the data of a desired date not all the data together. can I define a custom aggregator rather than those found in the properties for the measures?
Thanks a lot.
Mina
Quick question for you pro's...
Within a report matrix, I want to be able to have different aggregations for totals. One column would be a Sum of the data in that column, the next column would be an Average of the data. Is this possible, or is this not supported in SSR2005?
Thanks in advance for your help,
Clint
Hey,
I am trying to add aggregations to the partitions of my cube.
For that, I am using the Aggregation Designer, In which I choose MOLAP partitions with "Performance gain reaches 80%". Next I run the calculation. But the designer found only 78 aggregations and stop at 20% (496 Kb) !
My configuration is SSAS 2005 SP2, x64. Except this problem, everything is allright. Dimension attributes are all aggregatable.
Have you any idea of what can cause such a problem ?
Thanks in advance.
Alex
We are trying to compile a difinitive procedure for our customers moving from SSAS 2000 (where we use DSO) to 2005 (where we use AMO). However, we are running into problems getting even the 2000 environment set up according to the specs.
The requirement is a domain environment where the web server and the DB server are 2 different machines. We are replicating this environment locally using 3 Virtual Machines in VMWare Player. All have Windows Server 2003 Enterprise with SP2 installed. One is our domain controller, one is the DB server (SQL Server 2000 and SSAS 2000 SP4 with the hotfix rollup installed) where almost none of our application components are installed, one is the web server with IIS and our ASP.NET application and COM components, which bundles DSO and dependencies, but no database client tools installed. We set up ASP.NET to run as a domain user, a member of Administrators group on the domain and both server machines, and also OLAP Admins on the DB server (we can limit access later once everything works).
I can successfully create the database, cube, dimensions, and partitions from the ASP.NET code calligng COM components written in VB6 which use DSO. However, creating aggregations results in a clipboard error. Here is the code:
1380
Set dsoPartition = m_dsoServer.MDStores(mvarDBName).MDStores(cubeName).MDStores(1)
dsoPartition.OlapMode = olapmodeMolapIndex
1385
' get the partition analyzer
Dim dsoPartitionAnalyzer As DSO.PartitionAnalyzer
Set dsoPartitionAnalyzer = dsoPartition.Analyzer
' initialize the analyzer
dsoPartitionAnalyzer.InitializeDesign
1390
There is more, but the error occurs on one of the three statements between 1385 and 1390
The error is: "Can't open Clipboard" number 521 Source "DSO"
Creating the aggregations via the Storage Design wizard on the DB server directly seems to succeed.
Can somebody help with this?
Thanks,
Boris Zakharin, MCAD
Fact table gets update every day with thousands of records, I been increase the aggregations manually. Is there a way I can increase aggregations (design storage) programmatically . My cub gets process ones in day with VB program. I want to increase aggregations before processing cub through VB.
It's argent
please help me
thanks
Hello,
Not sure if data minimg can help me solving this type of inquiries. Thanks in advance for any pointers you could provide me.
Problem:
We have many dimensions with many levels. Sales directors will sometimes note a drop or a spike when looking at one of the dimensions members, most of the time at a high level.
Example, For Q4 07, the aggregation of Q4 Billing plus remaining Q4 Backlog dropped by $10M in America for the Automotive business from week 4 to week 5 while it was forecasted to remain stable.
The immediate question that will be sent to the guys in charge of this business is "What happened?".
Obviously evyone struggle to answer these questions eveytine there is a drop anywhere.
Using classic tools, they will try to spot a single anomaly or determine if this is accross the board and what are the concurent factors like lower than expected shipments combined with some orders cancellations.
The drop-off can lie in a single product or customer location, in which case it is fairly easy while time consuming to spot the anomaly using classic drill-down approach.
The drop-off can also be more wide spread or caused by a combination of factors like unable to ship in time, orders cancellations and sales credits in which case it is more difficult to spot where is the anomaly and explain it.
So I wonder if there is any way that I could use data mining to make the entire process of detecting anomalies accross dimensions levels, members and measures more straight-forward for end-users.
What do you think?
Thanks,
Philippe
Hi,
I am creating a custom transformation component, and a custom user interface for that component.
In
my custom UI, I want to show the custom properties, and allow users to
edit these properties similar to how the advanced editor shows the
properties.
I know in my UI I need to create a "Property Grid".
In
the properties of this grid, I can select the object I want to display
data for, however, the only objects that appear are the objects that I
have already created within this UI, and not the actual component
object with the custom properties.
How do I go about getting the properties for my transformation component listed in this property grid?
I am writing in C#.
Hi,
I've created a Custom Data Flow Component and added some Custom Properties.
I want the user to set the contents using an expression. I did some research and come up with the folowing:
Code Snippet
IDTSCustomProperty90 SourceTableProperty = ComponentMetaData.CustomPropertyCollection.New();
SourceTableProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
SourceTableProperty.Name = "SourceTable";
But it doesn't work, if I enter @[System:ackageName] in the field. It comes out "@[System:ackageName]" instead of the actual package name.
I'm also unable to find how I can tell the designer to show the Expression editor. I would like to see the elipses (...) next to my field.
Any help would be greatly appreciated!
Thank you
Hi,
I'm trying to enable Expression for a custom property in my custom data flow component.
Here is the code I wrote to declare the custom property:
public override void ProvideComponentProperties()
{
ComponentMetaData.RuntimeConnectionCollection.RemoveAll();
RemoveAllInputsOutputsAndCustomProperties();
IDTSCustomProperty90 prop = ComponentMetaData.CustomPropertyCollection.New();
prop.Name = "MyProperty";
prop.Description = "My property description";
prop.Value = string.Empty;
prop.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
...
}
In design mode, I can assign an expression to my custom property, but it get evaluated in design mode and not in runtime
Here is my expression (a file name based on a date contained in a user variable):
"DB" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + "\" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + ".VER"
@[User::varCurrentDate] is a DateTime variable and is assign to 0 at design time
So the expression is evaluated as: "DB189912189912.VER".
My package contains 2 data flow.
At runtime,
The first one is responsible to set a valid date in @[User::varCurrentDate] variable. (the date is 2007-01-15)
The second one contains my custom data flow component with my custom property that was set to an expression at design time
When my component get executed, my custom property value is still "DB189912189912.VER" and I expected "DB200701200701.VER"
Any idea ?
What I want to accomplish is that at design time the designer can enter a value for some custom property on my custom task and that this value is accessed at executing time.
View 10 Replies View RelatedI am writing a custom task that has some custom properties. I would like to parameterize these properties i.e. read from a varaible, so I can change these variables from a config file during runtime.
I read the documentation and it says if we set the ExpressionType to CPET_NOTIFY, it should work, but it does not seem to work. Not sure if I am missing anything. Can someone please help me?
This is what I did in the custom task
customProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
In the Editor of my custom task, under custom properties section, I expected a button with 3 dots, to click & pop-up so we can specify the expression or at least so it evaluates the variables if we give @[User::VaraibleName]
Any help on this will be very much appreciated.
Thanks
I have to use a CTE based on conditions. But I am not able to write a common CTE and access it on different conditions.
Now I am duplicating the CTE definition inside each if condition. But I expects a solution like bellow to reduce code duplication.
For example
---- I need a common definition of CTE like this.
with CustomGroup( id)
as
(
Select id from Groups g
Inner join GroupStatus s on s.Id = g.Id
Where s.Status = €˜Active€™
)
IF @DisplyStatus=€?UserDetail€?
begin
<!--[if !supportLists]-->- <!--[endif]-->at present CTE is defined here.
select u.* from users u
inner join userGroups ug on ug.UserId = u.Id
inner join CustomGroup cg on cg.Id =ug.GroupId -- Accessing the CTE
end
else if @DisplyStatus=€?UserSimple€?
begin
<!--[if !supportLists]-->- <!--[endif]-->at present CTE is defined here.
select u.FirstName,u.LastName,u.DOB from users u
inner join userGroups ug on ug.UserId = u.Id
inner join CustomGroup cg on cg.Id =ug.GroupId -- Accessing the CTE
end
else if @DisplyStatus=€?UserWithAddress€?
begin
end
Please suggest possibilities, and your suggestions.
Hi, here am i back ..not sure it's possible to solve it in one query ...there are four tables:1) headquarters---------------hqID (primary key)hqname2) department---------------depID (primary key)depnamehqID3) reports----------repID (p. key)depIDuserID4) users--------userID (p.key)usernameI want to get in one query those four fields:hqID, hqname, depID, depname + varchar(number of distinct users that has made a report foreach department) . This last field is a concatenation.The complexity resides in the fact there is need for a COUNT among other fields ...E.g.: table reports may look like this: repID depID userID 1 1 1 2 1 1 3 1 3 4 3 6 5 4 8This gives:for dep 1: 2 distinct usersfor dep 2: 0for dep 3: 1 distinct userfor dep 4: 1 distinct userThanks for helpTartuffe
View 9 Replies View RelatedHi,i have a dropdownlist connected to a sqldatasource like this: <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource1" DataTextField="Name" DataValueField="Name"> </asp:DropDownList>What i want is to put a particular value on the top of the dd. I can't use ORDER BY.e.g. the table which feeds the dd contains this:RDAhow to put value D on top of the dd?ThanksTartuffe
View 6 Replies View RelatedGreeting SQLers,
I'm attempting to define a Default value for a new user-defined-datatype in SQL Server 6.5, but can't as the dropdown list only allow (none) value!
Anyone can help? TIA.
How to define schemaname & how to qualify schemaname in SQL
statement.
Hello, everyone:
I need to define composite PK and FK for a ERD. Could someone offer the methods that work with,
1. T-SQL
2. ERD
Thanks a lot.
ZYT
I get the word TempleMode marked in Yellow. it's to clear all recoreds from my Pivot tables linked to SQL Database.
Sub ClearAllSheets()
On Error Resume Next
'Dim pt As PivotTable
Dim Sh As Worksheet
Dim refresh As Boolean
Dim tempMode As Boolean
Dim startTime As Date
[code]...
Is there a way to define a key that puts the text 'SELECT * FROM 'into the Query Analyzer window?I must type this about 50 times a day but cannot see a simple way ofdefining a key to write it for me..(tools/customize) seems to execute everything you put in there ratherthan leave it on the screen for me to add table names etc to.thanks for your time...
View 2 Replies View Relatedhallo
i am on the very beginning with sql.s,
how can i define a default value for the table field like its possible in access,
for example: the if the user not gonna enter a val so there is a default val predefined in the table field,
for example i would like to predefine the date now on the smalldate field....
thanks
Hi, all experts here,
I am having a question on defining the value of Periodicity_hint. e.g, I wanna predict the monthly sales amont of a product, and assume the data follows monthly patterns, then in this case, what value should we set for the periodicity_hint parametre?
Thanks a lot for your kind attention and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
Hi
I have a DropDownlist (Drop1) and a GridView,the GridView is bount to an SqlDataSource1 that has 2 Select parameters CatId and SourceId
The dropdownlist has a selectedvalue of the following format 15-10(2 numbers seperated by -).I want to set CatId to 15 and SourceId to 10
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Art %>"
SelectCommand="Select * from Option Where SourceId=@SourceId And CatId=@CatId">
<SelectParameters>
<asp:ControlParameter ControlID="Drop1" Name="SourceId" />
<asp:ControlParameter ControlID="Drop1" Name="CatId" />
</SelectParameters>
</asp:SqlDataSource>
Can anyone help me to define the parameters?
thanks
I've always created database tables with one Primary Key that increments by one for each new record. I'm working on a database that was built by someone else now that has a lot of defined foreign key relationships (under Tables > specific table > Keys). How helpful is it to define these Keys? I assume it helps make the database more efficient but is it necessary if you're working with small databases?
View 1 Replies View RelatedWhy is it not possible to define more than one relationship per table?
i have a primary table that i would like to cascade deletes to 2 other foreign tables in 2 separate relationships. why can't i do this and what are my alternatives?
thank you
Hi, I am trying to connect to my local SQL Server through a webform ( on VS.NET ). My web.config shows something like this -
sqlConnectionString="data source=IP address;Trusted_Connection=yes"and the tutorial I am following says something this - SqlConnection myConnection = new SqlConnection( "server=(local)\NetSDK;database=pubs;Integrated Security=SSPI");If I just replace the NetSDK either with my IP address or the servername (my machine name), I get a SQLException that server doesn't exist or access denied. I have specified "windows authentication". How do I correctly define my connection string ? Please guide me.Thanks,sbs.
I noticed in SQL ODBC API reference that SQLGetTypeInfo would return true for AUTO_INCREMENT if a smallint field is defined as autoincrement. Is there a simple way to set a field autoincrement thru the SQL Server`s front end?
T.G.
One more silly question,
What is an identity column? how can I define it?
table abc
=========
no (primary key) name
================ ====
1 jung
2 love
4 tom
In this example, I want to define 'no' as an identify column so that I can update it as I wish.
Thanks in advance,
-----------------------------------------------------------------------------
Subject:
From:
Date: sorry one more question (reply)
Kurt ()
6/23/01 2:48:32 AM
Jung,
Take a look at your table. It looks like you don't have an identity column
defined for "no" (your column name). Set Indentity_Insert doesn't work for tables that don't have one defined. PS A primary key is not the same as an Idendtity column.
------------
jung at 6/22/01 9:49:34 PM
table : abc
no (primary key) name
================ ====
1 jung
2 love
4 tom
In this record, I wanted to update no 4 to 3 which is primary key.
So, I used the query as below.
=============================
Set Identity_insert abc on
UPDATE abc
SET no = 3
WHERE no = 4
Set Identity_insert abc off
go
=============================
But I got an error;
Server: Msg 8106, Level 16, State 1, Line 1
Table 'abc' does not have the identity property. Cannot perform SET operation.
Why this happens? I really want to update the primary key.
I'm working on a SQL report and am returning a list of values for a column/field, "STATUS", to be used as one of the search parameter.
SELECT DISTINCT STATUS FROM [table]
ORDER BY STATUS
Values returned are [blank], approved, onhold, etc.How can I get the [blank] value to be set as "All" within the report parameter for STATUS?
Fazlul Haq writes "i m new to sql server2000.my question is
" I CREATE A TABLE IN WHICH THREE COLUMS EXIST i.e SNO(NUMERIC),NAME(CHAR),FNAME(CHAR).I WANT TO ENTER VALUE IN SNO FIELD STARTING FROM ZERO(0)e.g 011,021,022 etc and when enter value without starting from zero an error messag is appered on the screen.I WANT THAT THIS CAN BE DONE BY CREATING A "FUNCTION" OR "RULE" OR "STORED PROCEDURE". PLZ HELP ME ""
Hi
I have a question about user define function in sql.
How can I use Exec in UDF?
What I mean is that I made a string in a UDF, And I need to
execute the string in function. but I think it's illegal.
so tell me how can I use it?
P.S : forget about useing stored procedure instead of function.
thanx.
I am designing a new table with a few columns that may or may not havea value on each row that is inserted.What issues determine whether to allow a NULL value to be inserted forthat column or define a default value to be used?I want to think through the repercussions of this decision before I getinto production.
View 3 Replies View RelatedI have a primary key (column name is emp_id) in employee table. Also,I would like to make a combination of other two columns is unique.(combination of officecode field and claimno field must be unique).how can I implement this uniquess in ms sql 2000? thank you.
View 2 Replies View RelatedI have a DTS package (SQL2000) which uses ActiveX scripts to check for records with certain criteria in a table. If the certain criteria is met, I want to initiate a child package that preforms a data-extraction. Otherwise move onto the next step which check for a different set of criteria. Currently if the criteria is met, I flag the task as success to dictate moving to one set of steps, otherwise failure & moving onto the next check.
The problem I am realizing, is that if I 'Fail' on of the check steps, subsequent steps furthur down the route that I am directing it to go, do not run. These are other ActiveX tasks that send email, and do furthur analysis.
Why is this the case? Can't you perform a logical check, and based upon the check have a decision to run certain steps? I attempted to use the result 'Main = DTSStepScriptResult_DontExecuteTask', but I must not be using it correctly, because it lists the task as returning failure.
Any help would be great.
Kevin Albrecht