Filter Cube Data
Dec 28, 2006
Hi
i am using pivot table 11 to display the data from SSAS Cube. the problem is
1- pivot table fetches all the data and display it according to the design. but what i need is
a- the user could only see the data which was posted by himself.
b. He must be unaware of the data posted by other users.
in database table there is a field named userId which is also which is part of dimenssion in myCube.
i dont want to use filteraxes for this purpose actually i want the user to be unaware of other users of system also
any single helping word will be appriciated
Regards
Salman Bashir
View 5 Replies
ADVERTISEMENT
Mar 12, 2008
Although I can create the local cube, the cube that is created is very large because it brings over all accounts for the employee. In AS2K, it filtered automatically and only returned accounts that had revenue, which is what I'm trying to do using AS2005. I've read some of the responses about using XMLA and AMO, but I am not sure how to use these within the VS2005 windows application that I use to create and distribute the local cubes. Does anyone have a good example of how to use XMLA, AMO or anything else that would allow me to programmatically create and filter offline cubes using C#?
For instance, this code DOES create an empty cube. But how would I go about getting data into this cube?
Server server = new Server();
server.Connect(@"Data Source=c:MyLocalCube.cub");
server.Disconnect();
View 2 Replies
View Related
Apr 27, 2007
Hi all
sorry im new with using Reporting Services and even more inexperienced with using cubes.
My situation is as follows. I perform dynamic grouping (user selects the view via a parameter) Depending on the view selected, I need to change the dimension filter in the dataset.. Is this possible ?
Regards,
Neil
View 5 Replies
View Related
Jul 31, 2015
I have a cube that has a Dimension set up with several values some of which are bools. While Browsing in Excel or SSMS, two new values, when used as a filter shows (All) (Blank) and (True) for selections instead of (All) (True) and (False).Â
View 2 Replies
View Related
May 9, 2006
I've got a report that is using a cube as a data source and I can't get the report to show all the data. Only data at the lowest level of the cube is displayed. The problem is that most of the data I'm concerned with is at higher levels. There's no problem with the MDX. I get the correct results when I run the query.
I'm using a table to show the results. I've also tried a matrix, but I get the same results. I'm using SSRS 2005 and SSAS 2000.
Anyone have experience with this? Am I missing something simple?
View 7 Replies
View Related
Aug 23, 2015
I am just starting out using CUBEMEMBER/CUBEVALUE formulas in excel linked into a sql olap db - using this method for some custom reports where pivot tables are not suitable.
The time dimension values include Months, Quarters and Years and the CUBEMEMBER formulas like
=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1].&[1]") work fine - 1st quarter 1st month etc.
Is there a straightforward notation to aggregate months or do I need to use a plus sign to add a number of CUBEMEMBER formulas together.In other words - Is there an easier way of for say jan to july 2015 totals than
=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1]") + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[2]")) + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[3].&[7]"))
I haven't tested this but have assumed it works but a bit long and clumsy.
View 5 Replies
View Related
May 19, 2015
I have problems creating a cube with AMO.
I can add the cube to the database object and fill it with dimensions and a measuregroup (see code below).
If I call cube.Update() it says something like "Error in meta data manager. Cube has no measuregroups." (getting the message in german language)
The error in Microsoft.AnalysisServices.OperationException.Results.Messages is -1055653629
I can't find any documentation about this (or any other) error code in Microsoft documentation.
Here's my Code:
Cube newCube = database.Cubes.Add("MyCube","MyCube");
newCube.Language = 1031;
newCube.Collation = "Latin1_General_CI_AS";
CubeDimension dim = newCube.Dimensions.Add("dim1","dim1","dim1");
CubeAttribute attrib = dim.Attributes.Find("dim1Attr1");
[code]....
View 2 Replies
View Related
Nov 14, 2006
I need to query the Employees table in the ABC database to find all employees who report to EmployeeID 5 (indicated by a value of 5 in the €˜ReportsTo€™ column) and who have the word €˜Sales€™ in their job title (i.e the Title column). In my database the Employees table is very large so you want to pre-filter the data before doing more complicated processing on it. How would I use the following statement to to pre-filter the data?
SELECT * FROM EMPLOYEES WHERE ReportsTo = 5
View 1 Replies
View Related
Aug 9, 2004
Hi,
I have made sure that I am a member of OLSP Adminstrator group on the server and everything seems to work al the way till design storgae and cube processing but then when I browse the cube, I do not get any data :confused:
Can someone kindly help and guide please.
Many thanks.
View 7 Replies
View Related
Feb 29, 2008
Can someone help me out in telling me what a data cube is and if you have any examples I can look at?
We are in the process of using data cubes through SQL. What we are looking for is to be able to have summary of data, but if we wanted to click on a grouping we can go to the details of the data.
I just wanted to know if there was any infomation/websites I can go and look at.
Thanks,
Wendy
View 2 Replies
View Related
Apr 15, 2008
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
View 5 Replies
View Related
May 27, 2008
When I make a call to GetSchemaDataset with a restriction of a cube name with a space in the name of the cube the call fails. Following is a sample of the code: adoRestriction = new AdomdRestriction("CATALOG_NAME", "Contoso Telecom_Contoso"); adoRestrictions.Add(adoRestriction); dataSet = conn.GetSchemaDataSet("MDSCHEMA_CUBES", adoRestrictions); I am running SQL Server 2005 Analysis Services SP2. Is there some way to qualify the cube name in the restriction or is this just a bug? Thanks.
View 1 Replies
View Related
Jan 22, 2008
Hi All,
I have an SQL2005 db where I have some data I want to retrieve for a report. There are a couple of related tables holding some secondary info linked by foreign keys and one large table holding the bulk of the data. On my site I want to have a page where people can select filter options such as to display items that were entered before a date, after a date, between two dates, objects that are marked as complete in the table, etc.... Essentially various optional filters.
What I'm wondering is what's the best way to handle building the query to retrieve this data for the next page to display the report? I can think of a couple of ways but would essentially result in a lot of messy combining of strings to create the SQL query either in the C# code or in the stored procedure itself. I'm guessing, and hopping, there might be a cleaner method for this as it's a rather common thing I would think.
Thanks :)
View 1 Replies
View Related
Jan 30, 2007
SELECT *
FROM (SELECT [Patient Identifier], [Operator Index], Date, Time, ROW_NUMBER() OVER (PARTITION BY [Patient Identifier], Date
ORDER BY [Patient Identifier], Date, Time) AS RowNum
FROM Complete
WHERE [Operator Index] <= 89) AS a
WHERE RowNum <= 4
UNION
SELECT *
FROM (SELECT [Patient Identifier], [Operator Index], Date, Time, ROW_NUMBER() OVER (PARTITION BY [Patient Identifier], Date
ORDER BY [Patient Identifier], Date, Time) AS RowNum
FROM Complete
WHERE [Operator Index] >= 90) AS a
WHERE RowNum <= 2
This query returns values above 90 (I need 2 of them) or values between 80 and 89 (data is already filtered for only greater >=80) and I need 4 values above 80. I only need either 2 above 90 or 4 above 80, not both, and this query returns 2 above 90, but also the values between 80 and 89. If there are already 2 above 90, I do not want any values between 80 and 89. If there are 4 above 80, I do not need any additional values. If the are two above 80 and 1 above 90, I will take all of them (max I will ever take is 4).
View 1 Replies
View Related
Jan 31, 2007
Current Code
SELECT [Patient Identifier], Date, [Operator Index], Time
FROM (SELECT ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]) AS [Patient Identifier], ISNULL(t9.Date, t8.Date) AS Date, ISNULL(t9.Rows, t8.Rows)
AS Rows, c.[Operator Index], c.Time, ROW_NUMBER() OVER (PARTITION BY ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]),
ISNULL(t9.Date, t8.Date)
ORDER BY c.Time) AS RowNum
FROM (SELECT [Patient Identifier], Date, 2 AS [Rows]
FROM [First Step]
WHERE [Operator Index] >= 90
GROUP BY [Patient Identifier], Date
HAVING COUNT(*) >= 2) AS t9 FULL JOIN
(SELECT [Patient Identifier], Date, 4 AS [Rows]
FROM [First Step]
WHERE [Operator Index] >= 80
GROUP BY [Patient Identifier], Date
HAVING COUNT(*) >= 4) AS t8 ON t8.[Patient Identifier] = t9.[Patient Identifier] AND t8.Date = t9.Date INNER JOIN
Complete AS c ON c.[Patient Identifier] = ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]) AND c.Date = ISNULL(t9.Date, t8.Date)) AS d
WHERE d .RowNum <= d .[Rows]
Current Input
Patient IDDATE Time Operator Index
5170000318OCT2006 11:48 91
5170000318OCT200611:50 100
5170000417OCT200611:41 89
5170000417OCT200611:50 93
5170000417OCT200611:52 91
5170000417OCT200612:00 93
Current Output
Patient IDDATE Time Operator Index
0517_0000318OCT200611:48 91
0517_0000318OCT200611:50 100
0517_0000417OCT200611:41 89
0517_0000417OCT200611:50 93
It should be
Patient IDDATE Time Operator Index
5170000318OCT2006 11:48 91
5170000318OCT200611:50 100
5170000417OCT200611:50 93
5170000417OCT200611:52 91
The data is organized by patient id, date, time (ascending)
For a given patient id, on a certain data, testing was performed. A value between 80 and 100 is acceptable data. I need either the first 2 tests with a score above 90 or the first 4 tests above 80. (The tests are further sorted by time because the testing is time dependant. On some occassions, there is just too much data. What is wrong with my current query?
View 1 Replies
View Related
Apr 14, 2008
Here is a simple grid view page I made.
http://visualboxscore.com/boxscores/cfb_box_scores.aspx
Ultimately I want to be able to filter every field that is listed. However, for now I am just learning this stuff and would like to just filter the by the name in the "offense" field.
However, it is not binding the selection thus not filtering it out. Do you see any problems in my code?
<%@ Page Language="VB" %>
<html>
<head id="Head1" runat="server">
<title>GridView DetailsView Master-Details (2 Page)</title>
</head>
<body>
<form id="form1" runat="server">
<b>Choose a team:</b>
<asp:DropDownList ID="DropDownList1" DataSourceID="SqlDataSource2" AutoPostBack="true"
DataTextField="offense" Runat="server" />
<asp:SqlDataSource ID="SqlDataSource2" Runat="server" SelectCommand="SELECT DISTINCT [offense] FROM [cfb_boxscores]"
ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>" />
<br />
<br />
<asp:GridView ID="GridView1" AllowSorting="True" AllowPaging="false" Runat="server"
DataSourceID="SqlDataSource1" DataKeyNames="offense"
AutoGenerateColumns="False">
<Columns>
<asp:BoundField HeaderText="Date" DataField="date" DataFormatString="{0:MM/dd/yy}" ItemStyle-HorizontalAlign="Center" SortExpression="date" />
<asp:BoundField HeaderText="Offense" DataField="offense" ItemStyle-HorizontalAlign="Center" SortExpression="offense" />
<asp:BoundField HeaderText="Defense" DataField="defense" ItemStyle-HorizontalAlign="Center" SortExpression="defense" />
<asp:BoundField HeaderText="Rush Yards" DataField="rush_net" ItemStyle-HorizontalAlign="Center" SortExpression="rush_net" />
<asp:BoundField HeaderText="YPC" DataField="yards_per_carry" DataFormatString="{0:#.##}" ItemStyle-HorizontalAlign="Center" SortExpression="yards_per_carry" />
<asp:BoundField HeaderText="Pass Yards" DataField="pass_yards" ItemStyle-HorizontalAlign="Center" SortExpression="pass_yards" />
<asp:BoundField HeaderText="YPA" DataField="yards_per_att" ItemStyle-HorizontalAlign="Center" SortExpression="yards_per_att" />
<asp:BoundField HeaderText="Total Yards" DataField="total_yards" ItemStyle-HorizontalAlign="Center" SortExpression="total_yards" />
<asp:BoundField HeaderText="YPP" DataField="yards_per_play" ItemStyle-HorizontalAlign="Center" SortExpression="yards_per_play" />
<asp:BoundField HeaderText="Points" DataField="points" ItemStyle-HorizontalAlign="Center" SortExpression="points" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" Runat="server" SelectCommand="SELECT [date], [offense], [defense], [rush_net], [yards_per_carry], [pass_yards], [yards_per_att], [total_yards], [yards_per_play], [points] FROM [cfb_boxscores]"
ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>">
<SelectParameters>
<asp:ControlParameter Name="Offense" ControlID="DropDownList1" />
</SelectParameters>
</asp:SqlDataSource>
</form>
</body>
</html>
overall I want to format the table look and and background but for now I just want to see how this stuff functions.
Thanks!!
View 3 Replies
View Related
Apr 28, 2008
Hi
I am using below code to fetch the data hit our webserver from different website or engine.
ex: If I pass the parameter Ref as google and the dates, will fetch the data (hits) from google.
I have created a table (PoList) to have names like google,amazon,aol upto 14 names.
My Question : How do I filter all (hits) the data which the name (Ref) is NOT in the PoList table.
Thanks in advance
CREATE PROCEDURE sp_Portals
@Ref as nvarchar(255),
@SDate as Decimal(18,0),
@EDate as Decimal(18,0)
AS
SELECT COUNT(b.Pcode) AS totclicks,
case when b.Pcode = 'NPF' then b.Pcode + '-- No Products Found'
else a.prodmodel
end as prodmodel,
SUM(c.orderTot) AS [Sales Total],
COUNT(c.cusID) AS NumSales,
b.Pcode as Code
FROM CusDetails c RIGHT OUTER JOIN
View1 b LEFT OUTER JOIN
Products a ON COALESCE (b.ProdID, 0) = a.ID ON c.sessID = b.SessID
WHERE (b.Referer LIKE '%' + @Ref+ '%') AND (b.theDate >= @SDate) AND (b.theDate <= @EDate)
GROUP BY b.Pcode, a.prodmodel
ORDER BY totclicks DESC
GO
View 2 Replies
View Related
Jun 13, 2008
I have a master database at headquarters which will be the publisher. I have 5 databases at client sites that have the same database schemas as the master, which will be the subscribers. Each table in the database contains a Customer_No column, which is set as a default to 1 at client 1, 2 at client 2, 3 at client 3, etc. The master database contains data for all customers.
How do I get Merge Replication to only replicate the data from the publisher based on the customer_no column in each table?
View 1 Replies
View Related
Apr 28, 2015
i want to filter the data on specified dates. (between dates).
SELECT a.UserID, b.Name, b.EmployeeNum,b.Department, MIN(a.TransactionTime) AS TransactionTime,a.TransactionTime as TR_Date,
min(CONVERT(CHAR(20), TransactionTime, 113)) As TransactionDateTime
FROM NitgenAccessManager.dbo.NGAC_AUTHLOG AS a INNER JOIN NitgenAccessManager.dbo.NGAC_USERINFO AS b
ON a.UserID = b.ID
WHERE (a.FunctionKey = ' ')
[code]...
View 3 Replies
View Related
May 22, 2008
Hello!
I need to create a report that shows daily events per hours, like this:
15.5. Example
00:00 8
01:00 9
02:00 34
Is this possible? The data should be filtered by the datetime. If the datetime is for example 15.5.2008 00:45, it goes to the first row of that example table I created.
Edit. I am using Sql server 2005.
Thanks,
Aleksi Rytkönen
View 4 Replies
View Related
Feb 26, 2003
Is there any way to use a web based thin client to access OLAP without installing analysis services on the web server?
Thanks for your help!
View 1 Replies
View Related
May 31, 2005
Is any way to export and import an olap cube whithout his data?
Tks!
Paul
View 1 Replies
View Related
Dec 12, 2005
Hi friends,
I am new to MSAS world. I need help related to this. I want to pull data from MSAS cube programmatically. Only way I know is thru ODBO, but that won't help me in this case, cause I might have to drill down upto all possible intersections stored in MSAS (at least all of the stored members). Doing this thru MDX could be humongous thing, at least manipulating data taken out using ODBO.
I might be missing something out here. Can anybody help. It would also help if somebody can tell me if any other approach is poosible.
Thank you.
Abhijeet
View 3 Replies
View Related
Sep 3, 2007
I've seen posts about merging 2 databases but everyone is talking about 2 SQL based databases. I have a data cube based on MDX query giving me all the Sales data. I have a Excel sheet with the Budgets in it, thanks to the connector i can browse through my Excel file using SQL statements.
Now the problem that i have is i want to merge these 2 data's into one report i want to provide Sales figures from the cube with the budgets next to them which are stored in the Excel file.
So basicly is it possible to merge an MDX based database with an SQL database ? Cuz all the solutions i have found to far is by joining databases which is always done on SQL level.
Is this possible or just impossible ?
Thanks in advance
Joa
View 5 Replies
View Related
Feb 21, 2007
I need to create a package that updates the dimensions and cube data from a data warehouse on daily basis. I was going to create a Data Flow that takes the data from the DW source then put it as input to a Process Dimension destination to update the dimensions and use a Process Partition destination in the same manner to update the cube, but then I came across the Analysis Services Processing Task which seems to do the job as well. I am kinda confused which way to go. Any recommendations?
View 1 Replies
View Related
Feb 14, 2008
Hello,
I want to make a package in SSIS for automatic process of my data cube providing some log informations (two INSERT statements to my log table with actual date and result of operation succesful/unsuccesful). I tried to set data source to analysis services, I found my cube but I don't where I can add my cube to project and how can I desingn it. Can anybody tell me how to??? Thanks
View 3 Replies
View Related
Jun 28, 2007
Hello,
I have a problem with date conversion when the date is coming from a cube.
I mean the function "=cdate(Fields!Signature_Date.Value)" works fine when the date is always fill in, but when my record is equal to nothing, I have got the value "#error" in my report...
Which is the best way to avoid to display this value in my report?
I have used the code "=iif(Fields!Signature_Date.Value = nothing, nothing, cdate(Fields!Signature_Date.Value))" but it doesn't work neither...
Please, if my only way to resolve that is to convert the field in the MDX statement, could you give me a code example...?
Thanks,
Guillaume
View 6 Replies
View Related
Mar 1, 2007
Dear Friends..
I need help if any one can... My need is that...I am devloping my Cube using SSAS. I want to access my cube in multi languages e.g.: English, Japanese or other. At the movement I have two languages description in SQL Database like English and Japanese.
But In SSAS Translations, I can translate Measures Groups, Dimensions, Perspectives, KPIs, Actions, Named Sets & Calculated Members names. But there will be a need to Map Columns as per selected Lenguage so that I can display in proper language data.
Suppose I select Japanese the description should come from Column where Japanese description is stored.
OR any othere method by doing I can see my reports/data in any language.
Please Help...
View 7 Replies
View Related
Nov 21, 2006
I'm defining a mining structure against an OLAP dimension. The continuous value that I'm using both as input and for forecasting represents the time to complete a certain process.
There's something that strikes me as if it could be a problem, but I'm not sure. Our fact table has multiple columns (with multiple correponding measures in the cube). The "time-to-complete" measure is only populated on some of the fact rows - the rows that represent completion information. Other rows represent other information, and the "time-to-complete" value is set to 0. This works fine for cumulative time-to-complete and average time-to-complete, but it seems like it could mess up data mining. Will those 0-value facts skew the mining results? I'm not seeing a way to filter out those entries and only include the non-zero facts in the mining processing.
Or perhaps I'm totally misunderstanding something, which is quite possible. :)
View 14 Replies
View Related
Nov 19, 2006
Hi,
I've created a Cluster data mining model and seems to correctly return data. However I've created a data mining dimension and cube but when I go to query the cube it doesn't return any data if I select any members on the data mining dimension. Any suggestions to where I can look to resolve this?
Thanks
Sanjay
View 4 Replies
View Related
Jan 26, 2006
Howdy,
I have a table that has a group. In this group, I want to filter by 2 different expressions, concatenated with an OR. BUT I can't change the "And/Or" column value for the first entry because it is grayed out. The column will automatically change to an OR value if both my expression column fields are the same (which I don€™t want) but if I put any other value in to the expression field of the second row, the "And/Or" field of the first row automatically changes to an AND.
PLEASE! How do I get the And/Or field "ungrayed" so I can change it to what I want?
The 2 filters I and using check the UserID = to the user, and the other is checking a count to get the Top N 1. (So just showing the current user and the top producer)
View 14 Replies
View Related
Jul 2, 2007
Generally, on any screen, we design filter screen by allowing user to identify range or one value to search.
But sometime in some screen, It will be more convenient for user if user can identify No matter how value to search.
For example
On screen which have information of people in any province.
So user would like to search it by identify no matter how value to search.
There are check box of any province on filter part which enable users choose it.
Hence, if sometime user choose (by clicking on checkbox) 3 provinces : LA, Michigan, WachingtonDC to see description only 3 chosen province.
and sometime user choose (by clicking on checkbox) 2 provinces : LA, Michigan to see description only 2 chosen provinces.
Please give me any idea for create Stored Procedure or any tecnique to complete my idea....
Help me Pleaseeeee
View 1 Replies
View Related
May 11, 2008
I have a GridView that gets data from an SqlDataSource. It works fine, but now when I want to filter the results and add more parameters to my sql query, nothing happens. My initial select statement in the SqlDataSource contains only 2 parameters, so now when a user clicks a Button, onclick I call a function that is supposed to change the select statement of the SqlDataSource, add 2 more parameters taken from 2 controls, and then update the GridView. This is what I use: dataSource.SelectCommand = "new select command here"; dataSource.SelectParameters.Add("State", txtState.Text); dataSource.SelectParameters.Add("City", txtCity.Text); grid.DataBind(); It compiles, but when I click the button to filter the results, the GridView always looks the same as if nothing happened. I do not know if this is the right way to do it, or am I on the wrong track? Help is appreciated. Thank you in advance.
View 2 Replies
View Related
