I'm looking for a way to monitor index usage, over time, on all user tables
in a db, in order to determine which ones are getting hit the most and which
may not be getting used at all.
Any suggestions?
Thanks
--
MGMG-
Check out the article below. I think it will get you moving in the right
direction.
http://www.sqlmag.com/Article/ArticleID/38789/sql_server_38789.html
--
Thomas
"MGeles" wrote:
> I'm looking for a way to monitor index usage, over time, on all user tables
> in a db, in order to determine which ones are getting hit the most and which
> may not be getting used at all.
> Any suggestions?
> Thanks
> --
> MG|||If you are running SQL Server 2005, you can use the new
sys.dm_db_index_operational_stats management view:
http://msdn2.microsoft.com/en-us/library/ms174281.aspx
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"MGeles" <michael.geles@.thomson.com> wrote in message
news:06668ED5-FCC7-4D62-9169-1F70E936D43B@.microsoft.com...
> I'm looking for a way to monitor index usage, over time, on all user
> tables
> in a db, in order to determine which ones are getting hit the most and
> which
> may not be getting used at all.
> Any suggestions?
> Thanks
> --
> MG|||On Thu, 27 Apr 2006 12:28:16 -0700, "Ryan Stonecipher [MSFT]"
<ryanston@.microsoft.com> wrote:
>If you are running SQL Server 2005, you can use the new
>sys.dm_db_index_operational_stats management view:
>http://msdn2.microsoft.com/en-us/library/ms174281.aspx
Ryan, that is super-kewl!!!
Now, if I could just get the shop to upgrade to Server2003, ...
Josh
Showing posts with label tables. Show all posts
Showing posts with label tables. Show all posts
Wednesday, March 21, 2012
Monday, March 19, 2012
Measure group related question
I have a cube with two fact tables, two measure groups. I also created few calculated measures.
On client side I see two measure groups and below the calculated measure.
Is there any way I can show that calculated measure in one of the measure group. It's easy for end user to see number measure and % measure both next to each other.
I hope this can be possible using MDX some how update mdx for measure group or some thing...
Thank you - Ashok
Ashok,
You can easily associate your calculated measure with any existing measure group. To do this, please follow these steps:
- Open your project in SQL Server Business Intelligence Development Studio & double click your cube
- In the Cube editor, go to the Calculations tab and double click your calculated member "Average Sale Price"
- In the toolbar, just right from the "Form View" and "Script View" toolbar buttons, click on the "Calculation Properties" toolbar button
- In the Calculation Properties dialog that comes up, select your calculated member "Average Sale Price" from the dropdown and set the associated measure group for it to be "Royalty Statement"
- Deploy the project and you are ready to go
Hope this helps,
Artur
|||You are the man Thanks. Is there any way to move a measure from one group to another. I know If I change underline Table/View it can be done but is there any way in cube design time I can move measure from one group to another.
-Ashok
|||Ashok, moving a measure from one measure group to another is not currently supported in AS 2005.
--Artur
Measure change updates in Fact tables
Is there an industry standard method to populate Fact table columns with the
amount of change of a measure from one row to the next?
I have a FactSubscriptions (1 row = 1 week), and I wold like to have a
column of the price change from the previous week.
I'm wrestling with using tsql to join the Fact to itself offset by one week.
Progress is slowly being made, but I'm saying to myself "There's gotta be an
easier way!"
I at least need some keywords to do further research.
thanks,
Conrad
This type of query is usually done in the OLAP structures using MDX. Doing
it in t-sql would result in a poor performance.
If you do need to do it in t-sql please post sample of data and structures.
For example, how is date stored in your tables? As a datetime or you have a
week number or something?
MC
"Conrad" <Conrad@.discussions.microsoft.com> wrote in message
news:45C056A5-A015-4517-9CD8-F9D46D868968@.microsoft.com...
> Is there an industry standard method to populate Fact table columns with
> the
> amount of change of a measure from one row to the next?
> I have a FactSubscriptions (1 row = 1 week), and I wold like to have a
> column of the price change from the previous week.
> I'm wrestling with using tsql to join the Fact to itself offset by one
> week.
> Progress is slowly being made, but I'm saying to myself "There's gotta be
> an
> easier way!"
> I at least need some keywords to do further research.
> thanks,
> Conrad
>
|||1. Could you give me some OLAP/MDX keywords I could research further?
2. I have both WeekID and a date, but the "offset join" is done by WeekID.
I don't know OLAP or MDX yet, so I'll need to do this project in tsql. The
data is small, so
performance is not a problem.
Here's a representation of the data and tsql:
Table A
ID WeekID CustID Price PriceChange
1 1 90 100 0
2 2 90 110 10
3 3 90 110 0
4 4 90 110 0
5 1 91 100 0
6 2 91 100 0
7 3 91 120 20
8 4 91 120 0
Insert Table A Into ##TempB [sic]
Select ID, WeekID, CustID
From A
Inner Join ##TempB
On A.WeekID = ##TempB + 1
And A.CustID = ##TempB.CustID
tia,
Conrad
"MC" wrote:
> This type of query is usually done in the OLAP structures using MDX. Doing
> it in t-sql would result in a poor performance.
> If you do need to do it in t-sql please post sample of data and structures.
> For example, how is date stored in your tables? As a datetime or you have a
> week number or something?
>
> MC
>
> "Conrad" <Conrad@.discussions.microsoft.com> wrote in message
> news:45C056A5-A015-4517-9CD8-F9D46D868968@.microsoft.com...
>
>
|||You obviously need price change over the weeks for each custID (customer?).
Does this wokr for you?
select
A.price,
isnull((select A2.price from tableA A2 where A2.weekID = A.WeekID - 1
and A2.CustID = A.CustID), A.price) as PreviousPrice,
A.WeekID,
A.CustID
from
tableA A
Alternatively, you can use join as you did but using the same table.
select
A.price,
isnull(A2.Price,A.Price) as PreviousPrice,
A.WeekID,
A.CustID
from
tableA A
left join TableA A2 on A.WeekID = A2.WeekID - 1 ANDA.CustID = A2.CustID
MC
"Conrad" <Conrad@.discussions.microsoft.com> wrote in message
news:0E83399D-4B5C-40CE-988F-D58F9898F9E5@.microsoft.com...[vbcol=seagreen]
> 1. Could you give me some OLAP/MDX keywords I could research further?
> 2. I have both WeekID and a date, but the "offset join" is done by WeekID.
> I don't know OLAP or MDX yet, so I'll need to do this project in tsql. The
> data is small, so
> performance is not a problem.
> Here's a representation of the data and tsql:
> Table A
> ID WeekID CustID Price PriceChange
> 1 1 90 100 0
> 2 2 90 110 10
> 3 3 90 110 0
> 4 4 90 110 0
> 5 1 91 100 0
> 6 2 91 100 0
> 7 3 91 120 20
> 8 4 91 120 0
> Insert Table A Into ##TempB [sic]
> Select ID, WeekID, CustID
> From A
> Inner Join ##TempB
> On A.WeekID = ##TempB + 1
> And A.CustID = ##TempB.CustID
>
> tia,
> Conrad
>
> "MC" wrote:
amount of change of a measure from one row to the next?
I have a FactSubscriptions (1 row = 1 week), and I wold like to have a
column of the price change from the previous week.
I'm wrestling with using tsql to join the Fact to itself offset by one week.
Progress is slowly being made, but I'm saying to myself "There's gotta be an
easier way!"
I at least need some keywords to do further research.
thanks,
Conrad
This type of query is usually done in the OLAP structures using MDX. Doing
it in t-sql would result in a poor performance.
If you do need to do it in t-sql please post sample of data and structures.
For example, how is date stored in your tables? As a datetime or you have a
week number or something?
MC
"Conrad" <Conrad@.discussions.microsoft.com> wrote in message
news:45C056A5-A015-4517-9CD8-F9D46D868968@.microsoft.com...
> Is there an industry standard method to populate Fact table columns with
> the
> amount of change of a measure from one row to the next?
> I have a FactSubscriptions (1 row = 1 week), and I wold like to have a
> column of the price change from the previous week.
> I'm wrestling with using tsql to join the Fact to itself offset by one
> week.
> Progress is slowly being made, but I'm saying to myself "There's gotta be
> an
> easier way!"
> I at least need some keywords to do further research.
> thanks,
> Conrad
>
|||1. Could you give me some OLAP/MDX keywords I could research further?
2. I have both WeekID and a date, but the "offset join" is done by WeekID.
I don't know OLAP or MDX yet, so I'll need to do this project in tsql. The
data is small, so
performance is not a problem.
Here's a representation of the data and tsql:
Table A
ID WeekID CustID Price PriceChange
1 1 90 100 0
2 2 90 110 10
3 3 90 110 0
4 4 90 110 0
5 1 91 100 0
6 2 91 100 0
7 3 91 120 20
8 4 91 120 0
Insert Table A Into ##TempB [sic]
Select ID, WeekID, CustID
From A
Inner Join ##TempB
On A.WeekID = ##TempB + 1
And A.CustID = ##TempB.CustID
tia,
Conrad
"MC" wrote:
> This type of query is usually done in the OLAP structures using MDX. Doing
> it in t-sql would result in a poor performance.
> If you do need to do it in t-sql please post sample of data and structures.
> For example, how is date stored in your tables? As a datetime or you have a
> week number or something?
>
> MC
>
> "Conrad" <Conrad@.discussions.microsoft.com> wrote in message
> news:45C056A5-A015-4517-9CD8-F9D46D868968@.microsoft.com...
>
>
|||You obviously need price change over the weeks for each custID (customer?).
Does this wokr for you?
select
A.price,
isnull((select A2.price from tableA A2 where A2.weekID = A.WeekID - 1
and A2.CustID = A.CustID), A.price) as PreviousPrice,
A.WeekID,
A.CustID
from
tableA A
Alternatively, you can use join as you did but using the same table.
select
A.price,
isnull(A2.Price,A.Price) as PreviousPrice,
A.WeekID,
A.CustID
from
tableA A
left join TableA A2 on A.WeekID = A2.WeekID - 1 ANDA.CustID = A2.CustID
MC
"Conrad" <Conrad@.discussions.microsoft.com> wrote in message
news:0E83399D-4B5C-40CE-988F-D58F9898F9E5@.microsoft.com...[vbcol=seagreen]
> 1. Could you give me some OLAP/MDX keywords I could research further?
> 2. I have both WeekID and a date, but the "offset join" is done by WeekID.
> I don't know OLAP or MDX yet, so I'll need to do this project in tsql. The
> data is small, so
> performance is not a problem.
> Here's a representation of the data and tsql:
> Table A
> ID WeekID CustID Price PriceChange
> 1 1 90 100 0
> 2 2 90 110 10
> 3 3 90 110 0
> 4 4 90 110 0
> 5 1 91 100 0
> 6 2 91 100 0
> 7 3 91 120 20
> 8 4 91 120 0
> Insert Table A Into ##TempB [sic]
> Select ID, WeekID, CustID
> From A
> Inner Join ##TempB
> On A.WeekID = ##TempB + 1
> And A.CustID = ##TempB.CustID
>
> tia,
> Conrad
>
> "MC" wrote:
Measure change updates in Fact tables
Is there an industry standard method to populate Fact table columns with the
amount of change of a measure from one row to the next?
I have a FactSubscriptions (1 row = 1 week), and I wold like to have a
column of the price change from the previous week.
I'm wrestling with using tsql to join the Fact to itself offset by one week.
Progress is slowly being made, but I'm saying to myself "There's gotta be an
easier way!"
I at least need some keywords to do further research.
thanks,
ConradThis type of query is usually done in the OLAP structures using MDX. Doing
it in t-sql would result in a poor performance.
If you do need to do it in t-sql please post sample of data and structures.
For example, how is date stored in your tables? As a datetime or you have a
week number or something?
MC
"Conrad" <Conrad@.discussions.microsoft.com> wrote in message
news:45C056A5-A015-4517-9CD8-F9D46D868968@.microsoft.com...
> Is there an industry standard method to populate Fact table columns with
> the
> amount of change of a measure from one row to the next?
> I have a FactSubscriptions (1 row = 1 week), and I wold like to have a
> column of the price change from the previous week.
> I'm wrestling with using tsql to join the Fact to itself offset by one
> week.
> Progress is slowly being made, but I'm saying to myself "There's gotta be
> an
> easier way!"
> I at least need some keywords to do further research.
> thanks,
> Conrad
>|||1. Could you give me some OLAP/MDX keywords I could research further?
2. I have both WeekID and a date, but the "offset join" is done by WeekID.
I don't know OLAP or MDX yet, so I'll need to do this project in tsql. The
data is small, so
performance is not a problem.
Here's a representation of the data and tsql:
Table A
ID WeekID CustID Price PriceChange
1 1 90 100 0
2 2 90 110 10
3 3 90 110 0
4 4 90 110 0
5 1 91 100 0
6 2 91 100 0
7 3 91 120 20
8 4 91 120 0
Insert Table A Into ##TempB [sic]
Select ID, WeekID, CustID
From A
Inner Join ##TempB
On A.WeekID = ##TempB + 1
And A.CustID = ##TempB.CustID
tia,
Conrad
"MC" wrote:
> This type of query is usually done in the OLAP structures using MDX. Doing
> it in t-sql would result in a poor performance.
> If you do need to do it in t-sql please post sample of data and structures
.
> For example, how is date stored in your tables? As a datetime or you have
a
> week number or something?
>
> MC
>
> "Conrad" <Conrad@.discussions.microsoft.com> wrote in message
> news:45C056A5-A015-4517-9CD8-F9D46D868968@.microsoft.com...
>
>|||You obviously need price change over the weeks for each custID (customer?).
Does this wokr for you?
select
A.price,
isnull((select A2.price from tableA A2 where A2.weekID = A.WeekID - 1
and A2.CustID = A.CustID), A.price) as PreviousPrice,
A.WeekID,
A.CustID
from
tableA A
Alternatively, you can use join as you did but using the same table.
select
A.price,
isnull(A2.Price,A.Price) as PreviousPrice,
A.WeekID,
A.CustID
from
tableA A
left join TableA A2 on A.WeekID = A2.WeekID - 1 ANDA.CustID = A2.CustID
MC
"Conrad" <Conrad@.discussions.microsoft.com> wrote in message
news:0E83399D-4B5C-40CE-988F-D58F9898F9E5@.microsoft.com...[vbcol=seagreen]
> 1. Could you give me some OLAP/MDX keywords I could research further?
> 2. I have both WeekID and a date, but the "offset join" is done by WeekID.
> I don't know OLAP or MDX yet, so I'll need to do this project in tsql. The
> data is small, so
> performance is not a problem.
> Here's a representation of the data and tsql:
> Table A
> ID WeekID CustID Price PriceChange
> 1 1 90 100 0
> 2 2 90 110 10
> 3 3 90 110 0
> 4 4 90 110 0
> 5 1 91 100 0
> 6 2 91 100 0
> 7 3 91 120 20
> 8 4 91 120 0
> Insert Table A Into ##TempB [sic]
> Select ID, WeekID, CustID
> From A
> Inner Join ##TempB
> On A.WeekID = ##TempB + 1
> And A.CustID = ##TempB.CustID
>
> tia,
> Conrad
>
> "MC" wrote:
>
amount of change of a measure from one row to the next?
I have a FactSubscriptions (1 row = 1 week), and I wold like to have a
column of the price change from the previous week.
I'm wrestling with using tsql to join the Fact to itself offset by one week.
Progress is slowly being made, but I'm saying to myself "There's gotta be an
easier way!"
I at least need some keywords to do further research.
thanks,
ConradThis type of query is usually done in the OLAP structures using MDX. Doing
it in t-sql would result in a poor performance.
If you do need to do it in t-sql please post sample of data and structures.
For example, how is date stored in your tables? As a datetime or you have a
week number or something?
MC
"Conrad" <Conrad@.discussions.microsoft.com> wrote in message
news:45C056A5-A015-4517-9CD8-F9D46D868968@.microsoft.com...
> Is there an industry standard method to populate Fact table columns with
> the
> amount of change of a measure from one row to the next?
> I have a FactSubscriptions (1 row = 1 week), and I wold like to have a
> column of the price change from the previous week.
> I'm wrestling with using tsql to join the Fact to itself offset by one
> week.
> Progress is slowly being made, but I'm saying to myself "There's gotta be
> an
> easier way!"
> I at least need some keywords to do further research.
> thanks,
> Conrad
>|||1. Could you give me some OLAP/MDX keywords I could research further?
2. I have both WeekID and a date, but the "offset join" is done by WeekID.
I don't know OLAP or MDX yet, so I'll need to do this project in tsql. The
data is small, so
performance is not a problem.
Here's a representation of the data and tsql:
Table A
ID WeekID CustID Price PriceChange
1 1 90 100 0
2 2 90 110 10
3 3 90 110 0
4 4 90 110 0
5 1 91 100 0
6 2 91 100 0
7 3 91 120 20
8 4 91 120 0
Insert Table A Into ##TempB [sic]
Select ID, WeekID, CustID
From A
Inner Join ##TempB
On A.WeekID = ##TempB + 1
And A.CustID = ##TempB.CustID
tia,
Conrad
"MC" wrote:
> This type of query is usually done in the OLAP structures using MDX. Doing
> it in t-sql would result in a poor performance.
> If you do need to do it in t-sql please post sample of data and structures
.
> For example, how is date stored in your tables? As a datetime or you have
a
> week number or something?
>
> MC
>
> "Conrad" <Conrad@.discussions.microsoft.com> wrote in message
> news:45C056A5-A015-4517-9CD8-F9D46D868968@.microsoft.com...
>
>|||You obviously need price change over the weeks for each custID (customer?).
Does this wokr for you?
select
A.price,
isnull((select A2.price from tableA A2 where A2.weekID = A.WeekID - 1
and A2.CustID = A.CustID), A.price) as PreviousPrice,
A.WeekID,
A.CustID
from
tableA A
Alternatively, you can use join as you did but using the same table.
select
A.price,
isnull(A2.Price,A.Price) as PreviousPrice,
A.WeekID,
A.CustID
from
tableA A
left join TableA A2 on A.WeekID = A2.WeekID - 1 ANDA.CustID = A2.CustID
MC
"Conrad" <Conrad@.discussions.microsoft.com> wrote in message
news:0E83399D-4B5C-40CE-988F-D58F9898F9E5@.microsoft.com...[vbcol=seagreen]
> 1. Could you give me some OLAP/MDX keywords I could research further?
> 2. I have both WeekID and a date, but the "offset join" is done by WeekID.
> I don't know OLAP or MDX yet, so I'll need to do this project in tsql. The
> data is small, so
> performance is not a problem.
> Here's a representation of the data and tsql:
> Table A
> ID WeekID CustID Price PriceChange
> 1 1 90 100 0
> 2 2 90 110 10
> 3 3 90 110 0
> 4 4 90 110 0
> 5 1 91 100 0
> 6 2 91 100 0
> 7 3 91 120 20
> 8 4 91 120 0
> Insert Table A Into ##TempB [sic]
> Select ID, WeekID, CustID
> From A
> Inner Join ##TempB
> On A.WeekID = ##TempB + 1
> And A.CustID = ##TempB.CustID
>
> tia,
> Conrad
>
> "MC" wrote:
>
Friday, March 9, 2012
mdx question
I have 2 dimension tables :students and classes related through a fact table that contains the keys of these tables in addition to other keys such as academic year and other stuff. the classes dimension has an attribute called class_department.
what is the statement in mdx to get the class_department for a student in students table?
thanks
Christina
I guess you want the MDX query? Since I don't know the naming of your dimensions/members/measures, I will just give it a shot with some similar names. I assume that you have one measure in your fact table - a count measure.
SELECT {[Measures].[Count]} ON 0,
{[Classes].[Class_department].members} ON 1
FROM [YourCube]
WHERE {[Student].[Student Name].[John Doe]}
Wednesday, March 7, 2012
MDX query in excel sheet
I am using Excel 2007 and SSAS 2005. I have an excel report that is done by pivot tables, accesing my SSAS cube. Is it possible to view and edit the MDX query for the report? Can I pass parameters to MDX query? If so how?
You can use vba to extract the MDX query from a pivot table, but I do not believe there is anyway to change it. I have not tried this in 2007, but you could not in Excel 2003 and I have not heard anything to make me believe otherwise.|||Here's a blog post from Marco Russo that provides the code for doing this.
http://sqljunkies.com/WebLog/sqlbi/archive/2007/01/18/26875.aspx
Monday, February 20, 2012
MDX Parameter in Report...
Dear Friends,
I have a doubt, and I need your support...
I have 4 tables that are in one dimension STRUCTURE.
The Structure is:
1. Entidade
2. Carteira
3 Mesa
4. Folder
In my report I have these 4 combobox with data and related...
So the problem is...
I need to show a field in the report based on the selection... For example:
1oWhen
1. Entidade = 'LIS'
2. Carteira = 'ALL'
3 Mesa = 'ALL'
4. Folder = 'ALL'
I want to show in the textbox1.value the CalculatedMember1
2oWhen
1. Entidade = 'LIS'
2. Carteira = 'LIS-CPR'
3 Mesa = 'ALL'
4. Folder = 'ALL'
I want to show in the textbox1.value the CalculatedMember2
3oWhen
1. Entidade = 'LIS'
2. Carteira = 'LIS-CPR'
3 Mesa = 'LIS-CPR-ME1'
4. Folder = 'ALL'
I want to show in the textbox1.value the CalculatedMember3
4oWhen
1. Entidade = 'LIS'
2. Carteira = 'LIS-CPR'
3 Mesa = 'LIS-CPR-ME1'
4. Folder = 'FOLDER1'
I want to show in the textbox1.value the CalculatedMember4
It's Possible?
Someone help me?
Regards!!
I'm assuming that this means that you want the calculation to work differently at the different levels. Why not create a 4th calculation and use scope assignment to return the approriate value at the appropriate level?
I'm assuming that these attributes are in a hierarchy, but you did not mention what the name of that was, so I have just used the text "" as a placeholder.
Code Snippet
CREATE MEMBER currentcube.measures.CalculatedMember5 as (CalculatedMember1)
SCOPE (STRUCTURE.<hierarchy>.Carteira.Members);
(measures.CalculatedMember4) = (measures.CalculatedMember2);
END SCOPE;
SCOPE (STRUCTURE.<hierarchy>.Mesa.Members);
(measures.CalculatedMember4) = (measures.CalculatedMember3);
END SCOPE;
SCOPE (STRUCTURE.<hierarchy>.Folder.Members);
(measures.CalculatedMember4) = (measures.CalculatedMember4);
END SCOPE;
Then in your report you just referece CalculatedMember5
|||
Dear Darren,
I didn't understand your statment...
I need to use CM1 or CM2 or CM3 or CM4 depending on the values selected by the user in the combobox's report.
So I found a solution to get if inside the report, but It would be better If I control it in a CM in spite of textbox report... So I did like this:
Code Snippet
=IIF(Parameters!DimStructureCarteiraID.Value(0)="[DimStructure].[Carteira_ID].[All]"
AND Parameters!DimStructureMesaID.Value(0)="[DimStructure].[Mesa_ID].[All]"
AND Parameters!DimStructureFolderID.Value(0)="[DimStructure].[Folder_ID].[All]"
,Sum(Fields!CM_1.Value, "dSource1")
,IIF(Parameters!DimStructureMesaID.Value(0)="[DimStructure].[Mesa_ID].[All]"
AND Parameters!DimStructureFolderID.Value(0)="[DimStructure].[Folder_ID].[All]"
,Sum(Fields!CM_2.Value, "dSource1")
,IIF(Parameters!DimStructureFolderID.Value(0)="[DimStructure].[Folder_ID].[All]"
,Sum(Fields!CM_3.Value, "dSource1")
,Sum(Fields!CM_4.Value, "dSource1")
)
)
)
But would be better to call for example a CMX in spite of using this formula for each textbox...
I will try to convert this statment to inside my dataset...
Understood?
Regards and thanks!
|||The scope statement will only work inside the MDX script of your cube. You could probably do similar logic to your SSRS expression inside the MDX using a similar IIF() pattern.
Subscribe to:
Posts (Atom)